Unpivot an Excel table
If your report has one month per column, no pivot table will be able to analyse it. This tool converts it into the shape that works.
The problem it solves
Almost every report that travels by email is a cross-tab:
| Salesperson | Jan | Feb | Mar |
|---|---|---|---|
| Ana | 100 | 120 | 90 |
| Luis | 80 | 70 | 60 |
It reads beautifully, and it is useless for analysis. A pivot table cannot group by month, because the months are not data: they are column names. You cannot filter "first quarter only" either, or add April without rebuilding everything.
What you need is the tabular shape, where each row is one observation:
| Salesperson | Attribute | Value |
|---|---|---|
| Ana | Jan | 100 |
| Ana | Feb | 120 |
| Ana | Mar | 90 |
| Luis | Jan | 80 |
On that shape, pivot tables work, Power BI works, and every analysis tool works.
How to use it
All you have to decide is which columns identify each row. In the example
that is Salesperson; in a sales report it might be Region and Product
together. List them separated by commas under "Columns to keep".
Everything you do not mark as a key column is unpivoted automatically. There is no need to list twelve months: leave "Columns to unpivot" empty and all the rest are used.
The titles of the new columns
Two columns are generated: one holding the name of the source column, one
holding its value. They default to Attribute and Value, but it is almost
always worth renaming them to something meaningful — Month and Amount, say —
because those are the names you will see in the pivot table.
Empty cells
"Skip empty cells" is enabled by default. In a sparse cross-tab — normal in product-by-month reports — turning it off can multiply the row count tenfold without adding a single data point.
Leave it off only when you need every combination to exist, blanks included.
Frequently asked questions
What is the difference between unpivoting and transposing?
Transposing swaps rows for columns: the table rotates, but it is still a cross-tab. Unpivoting restructures the data so that each row is one observation. They are different operations and only the second one makes the data analysable.
Can I have several key columns?
Yes. List them separated by commas, for example Region,Salesperson. They are
repeated on every result row, which is exactly what a pivot table needs.
Are numbers kept as numbers?
Yes. An amount is still a number in the downloaded file, so you can sum it directly. If it arrived as text, a pivot table could not compute anything with it.
How many rows does it produce?
Original rows multiplied by unpivoted columns, minus the empty cells if you left that option on. A 100-row table across 12 months produces up to 1,200 rows.
Is this the same as Power Query's "Unpivot Columns"?
Yes, it is the same operation. The difference is that here you do not need to open Excel, load Power Query or learn its interface: you upload the file and download the result.