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.


17 comments to Normalize Data for Excel Pivot Table

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>