Reverse Pivot Magic

11 11 2013


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 

Duncan Williamson

3 Responses to “Reverse Pivot Magic”

  1. praveen Says:

    awesome


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: