How to unpivot in Excel

The need to unpivot data is a common requirement. There are a number of reasons as to why. I and my customers are primarily using  Excel on Windows and so the examples below are shown in excel and rely on excel functionality.

I use the Pivot table wizard and integrated drill down. A second technique is available using power query, but this is an addon until Excel 2016.

Using the Pivot Wizard

Here is my test data table.


Use the key combination [Alt] + [D], then [P], this opens the pivot table wizard.


Select the Multiple Consolidation Ranges radio button


and then [Next]. Select the I will create page fields radio button


and then [Next]. Set the data range and then press the [Add] button


and then [Next]. Set the location of the new pivot table.


and then [Finish]. The new pivot table is created.


Double click on the Grand Total/Grand Total field.


A full drill down is created in a new worksheet


This is not dynamic, changes in the original data table will not be reflected in the new TNF table. The drill down will need to be repeated.

I was guided by this page. My example has a value range ( a,b,c, null) in the original matrix cells.


Using Power Query

I used this video as a guide for using Power Query to perform the work.

and this one, was my initial guide, I got lost in the add additional column part of the script, but this is probably important part of the answer.

The huge advantage of this technique is that the results are dynamic; if you change the source, then the output changes to reflect new inputs.

Related Posts

  • Microsoft Office Templates With Office 2010, the location of the "My Templates" folder for Excel is managed using […]
  • Disqus This is page contains complex html i.e. javascript, the page must only be edited as […]
  • Snipsnap and the ‘html’ macro One of the add on features contributed  by the community to the snipsnap project was an […]
  • ERP Obviously ERP has been around a while, in my researches, I came across these two open […]
  • zbench, footer.php When written the zbench theme's footer text was Copyright © 2013 | […]

One thought on “How to unpivot in Excel

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.