Microsoft Excel: Converting a summary table / crosstab back into data rows
Posted on October 20th, 2014 by Sacha Chua
More posts about: geek Tags: excel, pivot // 15 Comments »
More posts about: geek Tags: excel, pivot // 15 Comments »
I recently wanted to transform a summary table of the form:
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.
- Press Alt-D, P to get to the secret pivot table wizard that’s different from the one you get from Insert > Pivot table.
- Choose Multiple consolidation ranges. Click Next.
- Choose I will create the page fields. Click Next.
- Select the range and add it. Go through the rest of the wizard to create a pivot table.
- Remove the row and column fields.
- Double-click on the total.
You should now see a table with the data from your crosstab.
Neato! Pivot tables are even cooler than I thought.
One Pingback/Trackback
- 12 December 2016 at 9:12am
- Creating data rows from a summary table - Reverse Pivot – By Keith Yap
[…] to the internet, I uncovered this little ...
![[me]](/blog/wp-content/themes/sacha-v3/images/face.png)
I'm 
