Normalize Data for Excel Pivot Table
If your Excel data is in monthly columns, like the worksheet shown below, you’ll have trouble setting up a flexible pivot table.
Instead of multiple columns with sales amounts, rearrange your data into a single column of amounts. Adjacent columns will show the product name and month.
Rearrange the Data With a Pivot Trick
To change the data layout, you can create a Multiple Consolidation Ranges pivot table, as shown in the video below. Usually that type of pivot table is used for combining data on different sheets, but it has the side benefit of changing horizontal data into a vertical layout.
After you create the pivot table, double-click on the Grand Total cell, to extract the source data, with amounts in a single column.
Then, build a new pivot table, from the normalized data.
Or watch on YouTube: Normalize Data for Excel Pivot Table
Download the Sample Workbook
To test the Multiple Consolidation Ranges trick, you can download the Normalize Data for Excel Pivot Table workbook. The file is in Excel 2007 format, and zipped. There are no macros in the workbook.