Unpivot Data

About unpivoting

Reports arrive wide because that is how people read them: one row per product, one column per month, totals along the bottom. It is the right shape for a printed page and the wrong shape for almost everything else.

Every tool that wants to chart, pivot, filter or import that data wants it long instead: one row per product per month, with the month as a value in a column rather than a heading above one.

• Wide — Product | Jan | Feb | Mar
• Long — Product | Month | Sales, three times as many rows

Excel can do this through Power Query, which is several dialogs, a load step and a refresh. Here it is two checkboxes and a name.

How to use it

• Paste your rows or upload a spreadsheet
• Tick the columns to keep as they are — the ones that identify the row, usually the first one or two
• Everything you did not tick becomes a row. Name the two new columns while you are there
• Download as CSV or Excel, or copy it back

The first column is ticked for you, because in almost every wide report it is the identifier.

Empty cells are dropped by default

A wide grid is usually sparse. Not every product sold in every month, so a good share of the cells are blank — and carrying those through produces a long table mostly made of nothing.

So blanks are skipped unless you ask for them. Keep empty cells as rows is there for the case where a blank is meaningful rather than missing: a survey where "no answer" is a real answer, or a schedule where an empty slot has to be visible.

When the report has two header rows

Printed reports often carry a title row above the real headings — a merged cell that says Sales by month sitting over Jan, Feb, Mar. Read as data, that top row becomes the header, the real headings become the first row of values, and the result is nonsense with a column called Sales by month and another called blank.

Unpivoting cannot guess which row is which, because both are text and both sit above the numbers. The fix is to delete the decorative row before you paste — one click in the spreadsheet, and everything downstream is right.

The same goes for a totals row along the bottom. It is not an observation, so it does not belong in a long table; leave it out of the selection, or delete it from the result. Carrying it through quietly doubles every figure the moment someone sums the value column.

What people use it for

• Making a wide report usable by a pivot table, which needs one row per observation
• Getting monthly or quarterly columns into a shape a chart can plot over time
• Preparing a spreadsheet for import into a database, which wants one value per row
• Turning a budget grid into a list of line items
• Reshaping survey results where each question became its own column
• Producing tidy data for R, pandas or any analysis tool that expects it

Good to know

• Works on Windows, macOS, Linux, ChromeOS, and on phones and tablets — it takes pasted text or a file, not a folder
• Nothing is uploaded. Reading and reshaping both happen in the page
• The first row is always the header, because the column headings become the values in the new attribute column — there is no way to unpivot a table without them
• Ticking every column would leave nothing to unpivot, so the tool falls back to keeping just the first
• Row order follows the original: all of the first row's values, then all of the second's
• The preview shows the first 200 rows; the download always contains every one

Related tools

• Group and Sum — the opposite direction, collapsing rows into a summary
• Transpose Rows and Columns — flip the whole table instead
• Split & Combine Columns — reshape the columns themselves
• Excel to JSON — take the long table somewhere else