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.


Comment ( 1 )

  1. Dave
    Next to document Power Query

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

By continuing to use the site, you agree to the use of cookies. more information

The cookie settings on this website are set to "allow cookies" to give you the best browsing experience possible. If you continue to use this website without changing your cookie settings or you click "Accept" below then you are consenting to this.

Close