Pivot The UnPivotable In Excel

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

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.

Your email address will not be published. Required fields are marked *

Comment

Name *

Email *

Website

Follow How To Excel At Excel

Need Answers To Excel Questions Like These?

*How Do I Create A Timestamp In Excel?
*I Want To Change The Width Of The bars On My Excel Chart
*How Can I Find Out The Length Of My Text in Excel?
Click the link below to receive more Excel tips' and my Free Ebook

Sign Up For My FREE Excel Tips Newsletter and receive your own E-book of my Top 50 Excel Tips.