Excel Basics

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:

• Remove empty, non-data rows
• Complete incomplete rows and headers (for example, by repeating labels that appear only in the first row of a group)
• Remove sub-totals and totals

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:

• Split one column into multiple
• Calculate new columns
• Reshape a table from wide format to long

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.