Microsoft Excel ® is an ideal tool for data analysis. While my SQL and SPSS friends may look down on a humble spreadsheet application, Excel is able to do a lot of analysis with relative ease compared to more high powered solutions.
The purpose of this article is to provide some tried and true tips to make data analysis in Excel as effective as possible. There are a lot of ways to do things in Excel and some of them make analysis easier while other ways can create problems down the line.
Data for Analysis Should be in Tabular Form
- There should not be empty rows or columns within the data table and every column should have one row with column names at the top of the table. If data is missing, it’s OK to have blank cells here and there but entire rows and columns that are empty cause problems. Many times I have been given a spreadsheet with data that was “prettied up” by putting blank rows and columns in it to make it look nice. Before I can do anything useful, I have to clean up the data to make a normal table.
- As much as possible, all of the data should be in a single table. Splitting the data up by some factor in the data that could be used for analysis limits what you can do when analyzing the data. For example, if you have data from several school, there should be a column identifying the school in the table rather than a separate table for each school.
Each Column Should Be of a Consistent Data Type
- Columns can contain text, dates, numbers but all of the elements in a column should contain the same kind of data.
- Values within columns should consistently identify attributes of the data. In other words, if a column represents gender, values should be consistently represented by either ‘M’ and ‘F’ or ‘Male’ and ‘Female’ but not a combination of the two. Numbers should all express the same unit (e.g. quarts, gallons, percentage points, currency, etc.).
By keeping your data clean and simple in the spreadsheet, you have all of the data analysis options in Excel available to you including Auto Filters, Sorts, Pivot Tables, Conditional Formatting, etc.