If a pivot table's source data is set up incorrectly, it will be difficult to build a flexible and efficient pivot table. A short video shows how to change the source data layout, and build a better pivot table. Written instructions list the steps. For a macro to unpivot the source data, go to the Unpivot Excel Data Macro page.
In some workbooks, the data is set up like the example shown below, with a column for each month. In this table, the sales amounts are entered horizontally, over those 12 monthly columns.
This is a good format for a worksheet report, but the horizontal structure is a poor layout to use as source data for an Excel pivot table.
If a pivot table is created from this source data, there are 12 value fields - one for each month. To see an annual total, a calculated field would be required, to sum all 12 months..
Instead of a using horizontal structure, with a separate column for each month, the pivot table source data should have just one column for amounts, where all of the sales amounts are entered. In another column, enter the month name for each amount. This creates a vertical layout, and is part of normalizing the data, which will allow a flexible pivot table to be built.
In the screen shot below, the orignal data has been rearranged, into a normalized structure, with columns for Product, Month and Amount. The easy steps for changing the layout are described below. With this 3-column Excel table, it will be easy to create a flexible pivot table, and focus of specific items or months.
Read more about Database Normalization on Wikipedia. There are other guidelines to follow too, when setting up the source data for an Excel pivot table. Find more information here: Data layout guidelines.
To quickly rearrange pivot table source data that is in a horizontal layout, such as the example above, use a pivot table trick to change the 13-column data into a normalized 3-column table, in a vertical structure. This video shows the steps for this easy trick, and also shows the steps for this technique, if there are two or more columns with labels. Written instructions are below the video
Follow these steps, to quickly rearrange the data into a normalized table:
After the source data is normalized, in a 3-column table instead of 13 columns, the next step is to create a pivot table, based on the rearranged data.
To create a pivot table:
In the screen shot below, Month was checked first, then Product, and then Amount. The pivot table shows the total amount for each month, and total sales for each product each month.
With normalized data source layout, the amounts are all in a single columns, and that makes this pivot table powerful and flexible.
To test the multiple consolidation ranges technique, you can download the Normalize Data for Excel Pivot Table sample workbook. The file is in xlsx format, and is zipped. There are no macros in the file.
For regular Excel news, tips and videos, please sign up for the Contextures Excel newsletter. Your email address will never be shared with anyone else.
Search Contextures Sites
Last updated: June 14, 2017 11:29 PM