Normalize Data for Excel Pivot Table

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 leaving the data like this, see how to normalize data for Excel pivot table setup.

Data in Monthly Columns

normalizedata01

Switch to a Vertical Layout

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. This process will “normalize data” for Excel pivot table setup, and makes it easier to work with.

The steps below explain how to do this quickly — you don’t have to manually rearrange the data!

Normalize Data for Excel Pivot Table

Rearrange the Data With a Pivot Trick

Because the original data, with 12 columns for months, looks almost like a completed pivot table, we want to “unpivot” the data, or “normalize” it.

[Update] If you have Excel 2010 or later, use Get & Transform, or Power Query, to quickly “unpivot” the data. If those tools aren’t available in your version of Excel, follow the instructions below.

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 from multiple consolidation ranges, double-click on the Grand Total cell.
  • That will extract the source data onto a new worksheet, with all the monthly amounts in a single column.
  • You can change the column headings in the extracted data — Product, Month and Amount are the new headings in this example.
  • Then, build a new pivot table, from the normalized data. Instead of having 12 value fields (one for each month), you will have one value field — Amount.

Video: Normalize Data for Excel Pivot Table

This Normalize Data for Excel Pivot Table video shows the steps for changing the data layout to create a flexible pivot table.

Normalize Data for Excel Pivot Table Workbook

To test the Multiple Consolidation Ranges pivot table technique, you can download the sample file from my Contextures website. Go to the Fix Pivot Table Source Data Layout page, and go to the Download section.

The file is in xlsx format, and zipped. There are no macros in the workbook.
_____________________

18 thoughts on “Normalize Data for Excel Pivot Table”

  1. Really good explanation, Debra. I hadn’t thought about the concatenate trick for multiple categories. Now I’m going to try to automate this. (Ya, and I’ll try the Tableau approach too).

    1. The Tableau Add-in is fantastic. It is easy, intuitive, quick and functional. I’ve used VBA unpivots, and they’re very slow and clunky.

  2. The Tableau Add-In is great on small datasets. However, in using the Add-In on a large dataset that was 24k rows x 27 columns reshaped into 648k rows x 1 column, it took 1 hour on a 32 bit machine and 30 minutes with a 64 bit machine – both using Office 32 bit. Via the process shown in this video, it took less than a minute on the same dataset.

  3. Debra, I’ve been researching data normalization in Excel and while I found all kinds of suggestions and comments, yours is not only the easiest solution but provided the clearest explanation. Thanks so much — you just saved me a bunch of time.

  4. I tried this in Office 2013 and realized that Excel 2013 changed the UX for Pivot Table and i can’t figure out how to do this in Excel 2013.
    Has anyone tried this with Excel 2013 ?

  5. Thanks for the awesome tip.
    I have one more question
    How can the data be refreshed in this table? I am trying to automate the workbook so if every month i paste the raw data and just refresh the table to reflect the new data without actually going through all the steps again.

    1. Tan – I’ve got some code that can achieve this for you, based on the code at the link in the comment above yours. Flick me an email to [email protected] and I can send you the routine. Note that you will need some familiarity with VBA (macros) to implement it. ALso note that I’m going on vacation today for two weeks, so won’t be around for a while to help you if you need help.

  6. Pingback: Applying dates to normalised data in Excel | Adam Dimech's Coding Blog
  7. Hello I’m new to this I’m trying to consolidate data into 1 pivot table.
    I’ve managed to get the data there but in my columns I have some text but it’s showing as zero is there a way to make this show the text as well as the numbers there as well.

Leave a Reply to Anonymous Cancel reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.