Unpivot Excel

What Unpivoting Does to the Grid

A crosstab spends one column per period or category. Unpivoting collapses all of those into two: one holding the column's name, one holding the cell's value. The columns you tick as ID columns are the ones that survive as columns and get repeated down the output; every column you do not tick is melted. So one input row becomes as many output rows as there were melted columns.

With Region as the only ID column and Skip blank values on, that becomes:

West's empty March cell produced no row. Output is written a record at a time - every attribute of the first row, then the second - so the result stays grouped by ID in source order.

The Four Settings

ID columns is the only structural choice: tick everything that identifies the record, which often means more than one column - Region and Product together, say. Attribute column name and Value column name just head the two new columns, and naming them Month and Revenue rather than leaving the defaults makes the result immediately usable in a pivot or chart. Skip blank values drops output rows whose value cell is empty; leave it on for a sparse matrix and turn it off when the absence of a number is itself data you want to keep.

Edge Cases

The output always has a header row, even with First row is a header unticked - in that case the melted columns are labelled Column 1, Column 2 and so on, because there are no headings to name them after. Untick every ID column and the result is a bare attribute-and-value list with no way back to the source record. Columns are identified by position against the widest row, so a ragged sheet melts safely, with short rows contributing blanks. Only the first sheet is read, the result is written as values in a sheet called Unpivoted, and a date used as an ID appears as the serial number Excel stores beneath it. Watch the row count above the preview: the multiplication is what makes this operation expensive on a large sheet.

Why Bother

A crosstab is designed to be read; a long list is designed to be analyzed. Pivot tables, charts, GROUPBY, and every database want one row per observation, which is exactly what unpivoting produces. It is also the fix for the perennial problem of a report that gains a new column every month: once the data is long, adding a period adds rows, not columns, and nothing downstream has to be rebuilt.

When a Different Tool Fits Better

To go the other way and summarize a long list back into a grid, use the Pivot Table Generator. If you only want rows and columns swapped with the shape intact, that is Transpose Excel, not unpivoting. When the record needs a compound key before melting - Region plus Product as one field - Combine Columns builds it first, and once the data is long, Excel Chart Maker and Filter Excel Rows both work naturally on it.