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:
Even when you have a clean data table, the data structure may not be precisely right for the kind of analysis you want to do. You may need to:
Some people call this kind of data arrangement tidy data. This arrangement is useful for many tools beyond just PivotTables -- Tableau, ggplot2, pandas, etc. all work well with tidy data.
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:
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.
This Guide is based on the Excel Guide created by Angela Zoss, Ph.D., Duke University Libraries. It is modified with permission from Dr. Zoss. The original is located: https://guides.library.duke.edu/excel/intro