Microsoft Excel: Converting a summary table / crosstab back into data rows

I recently wanted to transform a summary table of the form:

2014-10-15 20_36_58-Microsoft Excel - Book2

into a table with rows of (activity, date, value) so that I could add columns for year and month and then analyze the data using a pivot table.

It turns out that you can do this with another pivot table, yay! I followed this tutorial to convert my summary columns into data rows using Microsoft Excel 2010.

  1. Press Alt-D, P to get to the secret pivot table wizard that’s different from the one you get from Insert > Pivot table.
  2. Choose Multiple consolidation ranges. Click Next.
  3. Choose I will create the page fields. Click Next.
  4. Select the range and add it. Go through the rest of the wizard to create a pivot table.
  5. Remove the row and column fields.
  6. Double-click on the total.

You should now see a table with the data from your crosstab.

2014-10-15 20_43_25-Microsoft Excel - Book2

Neato! Pivot tables are even cooler than I thought.

One Pingback/Trackback

  • Douglas G Pratt

    This is fantastic!

  • Sue O’Mullan

    I don’t understand a word… however – you are brilliant indeed my friend! Not only are you beautiful on the inside – you are on the outside and your brain is an intricate web of awesomeness!!!!!

  • Rajesh

    You’re genius and this method is fantastic.

    • Credit belongs to the original tutorial writer! :) This is still, like, black magic for me.

  • darnfinanceguy

    OMG. Saved me hundreds of hours of converting from essbase crosstab to database. Thanks!

  • excel learner

    Amazing! This is awesome!!!!

  • RVM3


  • Dawn Carter-Little

    Girl, You Rock! Like the Flintsones!!!!
    So Helpful, save me tons of time

  • Pingback: Creating data rows from a summary table - Reverse Pivot – By Keith Yap()

  • Adam

    This is an incredibly fantastic tip, thank you for taking the time to post it, like other, you saved me hours of work!

  • Sean O’Connell

    Thank you. Nice trick – very useful. In the past I have written VBA to denormalise such data.

  • Ashusen Tamang

    Thank you . This is awesome and fruitful tricks in Excel. It saves tremendous times in excel .

  • Thanks Sasha, great summary that helped me solve a problem quickly. I’ve written a bit more about how your method helped (complete with animated gif!)