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?