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.
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.
Next to document Power Query