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.

One Comment

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.