Reverse Pivot Magic

UPDATE: 13th September 2019 I can imagine some of my visitors will still find this page of use if they are using a very old version of Excel. However, if you are using Excel 2016 or 2013 or even 2010, you should be able to use a much newer and more powerful technique for unpivoting data. Of course, I already have a page for that and here it is: Power Query Unpivoting

UPDATE: 16th January 2014: I have been using this technique since I last reported and I have found some limitations AND solutions to this reverse pivot technique. I will prepare a page and video to demonstrate what I have found.

2nd December 2013

Watch the video and then imagine that you have downloaded a five year set of financial statements for a company. Nothing wrong with the data or the database they came from but imagine you want to set up a pivot table from them. Probably not possible unless you are lucky enough to have a data provider who has designed their output with this in mind.

Let's cut a long story short: open this file amazon_case_reverse_pivot and try to apply the reverse pivot technique in my video to the data you will see there. Good luck and it will be worth any effort you put into it! PLEASE READ THE NOTES in this work book re copyright ...

I didn't invent this technique but I learned it a couple of weeks ago and used it in earnest just now ... watch the video!

I had a table that had months across the top, years down the left hand side and data in between. What I wanted was the data in a list in three columns:

year month value

transposing, rotating, copying and pasting can give you what you want but it will take a long time. My original table covered all twelve months and five years ...

Reversing a Pivot table is what it's called and this is how to do it:

1. Press Alt + D then P to call up the pivot table wizard and select Create a "Multiple Consolidation Ranges PivotTable."


2. Select "I will create my own page fields".


3. Select your data range and choose 0 page fields


4. When you see your pivot table, double click on the intersection of Row Grand and Column Grand, in the bottom right hand corner of your pivot table


5. You will be presented with your new table in the form of a list ... that is, from pivot table style to list style ... it's magic!

The video [wpvideo tGsBeRej]

Duncan Williamson