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.

normalizedata01

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.

normalizedata02

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.

_____________________

You may also like...

17 Responses

  1. Shawn Shafer says:

    I can recommend using the add-in from Tableau to do normalize, or as they call it, reshape, the data with just one click from the ribbon. It also includes two other useful commands for data manipulation and preparation.

    See their website for install instructions:
    http://www.tableausoftware.com/support/knowledge-base/addin-reshaping-data-excel

  2. AlexJ says:

    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).

  3. Thanks Alex, and if you try the Tableau add-in, I’d be interested to hear about it.

    • colourlessgreen says:

      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.

  4. John Henry says:

    Great Lesson, thanks for sharing!

  5. John Linden says:

    Thanks Debra! Great explanation!

  6. Andrew S says:

    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.

  7. K Adam says:

    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.

  8. Gan says:

    Debra,
    This is the simplest way I have come across to normalize a table in Excel
    Thank for the tips.

  9. Gan says:

    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 ?

  10. Gan says:

    My bad, the keyboard shortcut is the same for 2013. I managed to normalized the data using Excel 2013. Thanks

  11. Tan says:

    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.

    • Jeff Weir says:

      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 weir.jeff@gmail.com 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.

  12. Ryan says:

    Debra, you are a wizard!! This is great. I am curious if there is a way to do this in Access?

  1. September 22, 2015

    […] recently linearised (normalised) a large data set, as per the example below. Unfortunately the script that linearised the data […]

Leave a Reply

Your email address will not be published. Required fields are marked *