The PivotTables feature within Microsoft Excel 2013 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 2013 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.
Ramp up Your Excel PivotTables Skills
If you would like to learn more about the intricate features of pivot tables, attend our upcoming Excel 2013: PivotTables class (See our Course Schedule for the most up-t0-date class listings). This half-day class focuses on manipulating data with PivotTables and presenting it using PivotCharts.
Stick around in the afternoon for the Excel 2013: Power Pivot class (again, check our Course Schedule for dates and times) to explore the visualization tools and advanced functionality of Power Pivot.
Get Tips in Your Inbox: Subscribe to Training News!
This tutorial for preparing Excel data for PivotTables builds on a tip originally featured in our Training News newsletter. To get future tips in your inbox, subscribe using the form on this page. You’ll also get monthly productivity tips, training helps and notices of upcoming classes and offers!