{"id":2988,"date":"2016-11-03T19:27:50","date_gmt":"2016-11-03T19:27:50","guid":{"rendered":"http:\/\/wiki.davelevy.info\/?p=2988"},"modified":"2016-11-04T11:35:20","modified_gmt":"2016-11-04T11:35:20","slug":"how-to-unpivot-in-excel","status":"publish","type":"post","link":"https:\/\/davelevy.info\/wiki\/how-to-unpivot-in-excel\/","title":{"rendered":"How to unpivot in Excel"},"content":{"rendered":"

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\u00a0 Excel on Windows and so the examples below are shown in excel and rely on excel functionality.<\/p>\n

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.<\/p>\n

Using the Pivot Wizard<\/h3>\n

Here is my test data table.<\/p>\n

\"unp-data-1-w450\"<\/p>\n

Use the key combination [Alt] + [D]<\/code>, then [P]<\/code>, this opens the pivot table wizard.<\/p>\n

\"unp-wiz-1-w450\"<\/p>\n

Select the Multiple Consolidation Ranges radio button<\/p>\n

\"unp-wiz-2-w450\"<\/p>\n

and then [Next]<\/code>. Select the I will create page fields radio button<\/p>\n

\"unp-wiz-3-w450\"<\/p>\n

and then [Next]<\/code>. Set the data range and then press the [Add]<\/code> button<\/p>\n

\"unp-wiz-4-w600\"<\/p>\n

and then [Next]<\/code>. Set the location of the new pivot table.<\/p>\n

\"unp-wiz-5-w450\"<\/p>\n

and then [Finish]<\/code>. The new pivot table is created.<\/p>\n

\"unp-data-2-w600\"<\/p>\n

Double click on the Grand Total\/Grand Total field.<\/p>\n

\"unp-data-3-w600\"<\/p>\n

A full drill down is created in a new worksheet<\/p>\n

\"unp-data-4-w600\"<\/p>\n

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.<\/p>\n

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

ooOOOoo<\/p>\n

Using Power Query<\/h3>\n

I used this video as a guide for using Power Query<\/a> to perform the work.<\/p>\n