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

Posted: - Modified: | geek

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.

You can view 17 comments or e-mail me at sacha@sachachua.com.

17 comments

Douglas G Pratt

2014-10-21T00:12:42Z

This is fantastic!

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!!!!!

You're genius and this method is fantastic.

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

darnfinanceguy

2015-06-29T23:36:14Z

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

excel learner

2015-12-16T04:18:04Z

Amazing! This is awesome!!!!

Dawn Carter-Little

2016-08-12T15:56:34Z

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

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

Ashley Nicholas (AshSandwich)

2017-05-08T12:25:41Z

Incredible. The manual effort I've been putting into this task over the past 2 years was crazy. I don't know if I'm happy to have a solution, or mad that I didn't research it before! Thank you very much.

Sean O'Connell

2017-05-21T12:57:47Z

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

Ashusen Tamang

2017-07-26T11:49:34Z

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

https://medium.com/keith-ya...

Nice Trick, you don't even need to remove the row and columns, just double click the grand total to drill down to the details.

Mark Eckman

2019-06-14T15:41:17Z

OMG...You are awesome! That just saved me so much time. Thanks
v/r Mark Eckman
Senior Analyst Navy Medicine

Budi Setiawan

2020-01-28T17:04:25Z

Thanks a lot... Very usefull..

Just came back to this again. Absolute saviour!