To use a Pivot Table we usually need transactional data or a flat data table containing rows and columns of data, that is not summarised already. In my example below, the data already looks summarized as it contains months across our data set which is always a giveaway!
So what can we do about it, can we make this data ‘pivotable’???. so we can carry out some analysis on it??? Sure we can, a neat trick is available….to take formatted summary data and transform it into transactional data…
- Firstly go to create a Pivot Table
- Hit ALT+D then P to bring up the Pivot Table Wizard (Excel 2010-)
- In Step 1 choose select Option 3 -Multiple Consolidation Ranges
- Hit Next
- Allow Excel to create a single page in Step 2
- Hit Next
- Click Add then Next
- Click Finish on step 3 of the Pivot Table Wizard
- One Pivot Table is created that does not look great
This is the clever part though, just hit the total cell at the very bottom right,(the Grand Total for Rows and Grand Total for Columns) and as you know by double clicking on any cell in a Pivot table you get a new sheet with all of the data that makes up that cell.
So all we do is double click and a whole new page of transactional data appears. Magic.
We have a new data set that shows a new record for every intersection of column and row in our original data now we can GO Pivot!!!
Give this a go next time you are stuck with ‘Unpivotable Data’
Break Your Pivot Tables Into Multiple Columns
Sort Pivot Tables The Way You Want To
Refresh All Of Your Pivot Tables With One Click
GETPIVOTDATA when You Don’t Want To?
Michael Teng says
Hi Barbara
You are genius and thank you so much for sharing this invaluable solution to the mystery that I always have in pivot tables.
Best regards
TC
Mads Jensen says
Hi
If you have Excel 2016 Professional or Power BI Desktop you can use the New Query function to import data and then “unpivot” them in the query editor before importing them.
This also works if there are 2 or more columns in front that doesn’t contain numbers.
As I see, the method you suggest only works if there is 1 column without numbers.