If a pivot table's source data is set up incorrectly, it will be difficult to build a flexible pivot table. Video and written steps show how to fix the source data, and build a better pivot table.
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.
NOTE: This technique uses the old Multiple Consolidation Ranges feature, which has a 256 character limit per cell. The macro temporarily combines cell text, which could result in text strings that are over that limit. Some text could be cut off.
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.
Don't miss my latest Excel tips and videos! Click OK, to get my weekly newsletter with Excel tips, and links to other Excel news and resources.
Last updated: November 26, 2020 1:53 PM