What it does
It unpivots your data – restructuring it from several columns to stacked rows – basically the opposite of building a PivotTable off of source data.

When to use it
When you want take data with several columns (often referred to as a pivoted format) and stack those columns into rows in order to more easily set up formulas, filter, or other analysis.

Why to use it
It’s a very quick and easy way to unpivot your data containing several similar columns into a stacked row-based format.

Default shortcut
None

Other Details

• Category: Data / Pivot
• Difficulty: 4/5
• Usage/frequency: 2/5
• Automation factor: 5/5 (estimated 300 seconds saved each time used)
• Type: Bulk
• Date added: 6/1/2014
• Tags: Data, pivot, unpivot, table, columns

Related Macros
Select To Bottom
Select To Right
Filter Picker

Other Articles
None

Unpivot Table – Example File

Prerequisites
Select the data set you want to unpivot – including any header rows above the data or label columns to the left of the data.  See the example file referenced above for what these header rows and label columns refer to.

Instructions
With your data set selected, run the Unpivot Table macro.  You will be prompted to enter the number of header rows above your data – enter that and click OK.  Then you will be prompted to enter the number of label columns to the left of your data – enter that and click OK.  A new sheet called Data_Output will be created with the data restructured into a stacked row vertical structure rather than a flat column horizontal structure.  For large data sets, you can track the progress of the macro with the status bar at the lower-left corner of your Excel window.