Skip to Main Content

Excel Basics

A guide on using Excel for data entry, analysis and visualization.

Data Cleanup

To prepare data for later analysis, it is important to have a clean data table.  Depending on the origin of the data, you may need to do some of the following steps to ensure that the data are as complete and consistent as possible:

Data Structure

Even if you have a clean data table, the data structure may not be exactly right for the kind of analysis you want to do.  You may need to:

Excel has many functions for extracting and combining data from columns, calculating new columns based on old columns, and even using conditional statements to tailor the output of functions.  What's more important than knowing every function up front is deciding how specific your data need to be.  Here are some questions you can ask yourself:

  • Will I ever need to analyze the data based on a piece of information that is currently combined with other information in a single cell?
  • Are there any other categories I could create to group my rows in a meaningful way?
  • Are the values within each column inconsistent?  For example, do my numerical columns also have text in them that might cause errors when I try to perform a calculation?

When you identify something that might need to change, you can browse or search for an Excel function that will help.

Converting data from a wide format to a long format, on the other hand, is trickier to do in Excel.  You may want to try another tool, like OpenRefine