The PivotTables feature within Microsoft Excel can be a powerful ally in analyzing large amounts of data. However, to reduce the chance of errors, be sure to prepare Excel data for PivotTables analysis by following these 8 data clean up tasks first.
Fail-Proof Tips to Get Excel Data in Shape for PivotTables Analysis
You already know Excel is popular program for analyzing data, and PivotTables is one of the many powerful tools you can use. It enables you to quickly analyze large amounts of data and present it in meaningful ways.
While any large set of data can be turned into a pivot table, it’s important to prepare your Excel data for PivotTables analysis in advance. Otherwise, you may run into errors or inaccuracies that misrepresent your data. Follow these instructions to ensure your pivot table will produce the results you are looking for.
8 Steps to Prepare Excel Data for PivotTables
- Give each column in your dataset a unique heading. For example, specify “First Name” and “Last Name” rather than using “Name” for both.
- Assign the category for each column such as currency or date. To do this, highlight the entire column by clicking the letter above the column. Then assign the appropriate category using the drop-down menu in the Numbers group on the Home tab. You may also format your data by right-clicking the column and choosing Format Cells, then selecting the Number tab. You’ll be able to choose your category and specify how you want the data displayed.
- Do not use any totals, averages, subtotals, etc. as a part of the data. Pivot tables will do a much better job with creating calculations.
- Remove all blank cells from the data. If you have any blank cells in the source data, it will show an error messages in the results. Use “not available” or “n/a” to remove blank cells. An easy way to find blank cells is Shift + Down Arrow. It will automatically move you to the next blank cell in the column.
- Remove duplicated data. Repeating data will produce incorrect results. While some data will repeat (for example, products in a sales report), you do not want duplicate entries for a single event.
- Remove all filters from the data. Filters can be created within the pivot table. You can adjust filters using the Sort & Filter command in the Editing group on the Home tab.
- Ungroup any grouped cells. Wait until after you’ve created your pivot table to group data. Remove grouped cells with the Ungroup command in the Outline group on the Data tab.
- Your last step before creating your pivot table should be to format your data as a table. Highlight all of your data, and then choose Format as Table in the Styles group on the Home tab.
The key to getting reliable insights with Excel PivotTables tool is properly cleaning and formatting your data. Follow the steps above to prepare Excel data for PivotTables and ensure your results are accurate and error-free.
Bonus Tip: Absolute References vs. Relative References
Microassist’s lead instructor, Andy Weaver, talks about absolute and relative references in this virtual class.
Subscribe to Monthly Training Updates
Receive monthly productivity and training insights, software tips, and notices of upcoming classes!