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.

unp-data-1-w450

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

unp-wiz-1-w450

Select the Multiple Consolidation Ranges radio button

unp-wiz-2-w450

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

unp-wiz-3-w450

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

unp-wiz-4-w600

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

unp-wiz-5-w450

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

unp-data-2-w600

Double click on the Grand Total/Grand Total field.

unp-data-3-w600

A full drill down is created in a new worksheet

unp-data-4-w600

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.

ooOOOoo

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 […]
  • Why I gave up on Snipsnap It can be summed up as a lack of future, cost to host and missing functionality. The […]

One thought on “How to unpivot in Excel

Leave a Reply

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