Basics Updated November 2026

Unpivot data

Select the data, go to Data, From Table/Range to open Power Query, select the ID columns you want to keep, then Transform, Unpivot Other Columns. Every month column becomes an Attribute and Value pair on its own row, and Close & Load returns a list a pivot table can summarize.

Syntax

Data > From Table/Range > select key columns > Transform > Unpivot Other Columns > Close & Load

Part	What it is
`Unpivot Columns`	Unpivots only the selected columns
`Unpivot Other Columns`	Keeps the selected columns and unpivots the rest; safest when new months get added
`Attribute`	The new column holding the old header text (Jan, Feb, ...)
`Value`	The new column holding the cell values

Examples

Wide: Product | Jan | Feb | Mar
Select Product > Unpivot Other Columns

Result: Long: Product | Attribute | Value, three rows per product (Jan, Feb, Mar) instead of one.

Rename Attribute to Month, Value to Revenue > Close & Load > Insert > PivotTable

Result: A pivot with Rows: Product, Columns: Month, Values: Sum of Revenue reproduces the original layout, now with totals, filters and grouping available.

Google Sheets: =ARRAYFORMULA(SPLIT(FLATTEN(A2:A4&"|"&B1:D1&"|"&B2:D4),"|"))

Result: A three-column list (product, month, value) built from a 3 by 3 block with headers in B1:D1 and products in A2:A4.

Common mistakes

Blank cells vanish after unpivoting: Power Query drops nulls on unpivot, so replace blanks with 0 first if you need them.
New month columns are missing: you used Unpivot Columns on a fixed selection; use Unpivot Other Columns so new columns are included on refresh.
The Value column is text: the wide columns had mixed types, so set the Value column type to Decimal Number after unpivoting.

Practice Unpivot data

These drills check your answer on live data, free, no account needed.

Revenue by monthShow total Revenue for each month of 2025.

Questions

Why do I need to unpivot before making a pivot table?

A pivot needs one value per row with a category column. If months are headers, Excel treats each month as a separate field and you cannot group, filter or total across them.

Can I unpivot without Power Query?

Yes, with the classic wizard (Alt, D, P, Multiple Consolidation Ranges) and double-clicking the grand total, or with formulas, but Power Query is simpler and refreshable.

Create a pivot tableCreate a pivot table in Excel from a range or table, pick a new worksheet, then drag fields into Rows and Values. Steps, shortcut, data rules and fixes.
Pivot from multiple sheetsBuild one pivot table from several worksheets by appending them in Power Query or relating them in the Data Model. Steps for both and the old wizard limits.
Pivot table in Google SheetsCreate a pivot table in Google Sheets with Insert, Pivot table, use the editor to add Rows, Columns, Values and Filters, change SUM to COUNTA, group dates.