A report with a column for each month or product is easy to read but hard to analyse. Unpivoting turns those columns into rows, one value per row, so you can filter, sort and build PivotTables. Power Query does it in one click and refreshes.
Why unpivot?
In a report, the same kind of value is spread across many columns. Normalized data keeps each kind of value in one column.
Method 1: Power Query
- Click in the report → Data → From Table/Range → OK.
- Select the first column (the one to keep), right-click its header → Unpivot Other Columns.
- Rename Attribute and Value (for example Month and Sales).
- Home → Close & Load.
Use Unpivot Other Columns, not Unpivot Columns. It names the column to keep, so a new month added to the report is unpivoted automatically on refresh.
Method 2: PivotTable and PivotChart Wizard
This works in every Excel version, but doesn’t refresh.
- Press Alt, D, P to open the wizard (or add it to the Quick Access Toolbar from All Commands).
- Choose Multiple consolidation ranges → Next → I will create the page fields → Next.
- Select the report range, click Add, then Finish.
- In the new PivotTable, double-click the Grand Total value. Excel creates a sheet with every value as its own row: that’s your normalized data.
The wizard uses only the first column as the row label. For reports with two label columns, use Power Query.
Excel 365 formula
For a report in A1:E4 (names in A2:A4, months in B1:E1):
=HSTACK(TOCOL(IF(SEQUENCE(1,COLUMNS(B1:E1)),A2:A4)),TOCOL(IF(SEQUENCE(ROWS(A2:A4)),B1:E1)),TOCOL(B2:E4))IF with SEQUENCE repeats the names across and the months down, and TOCOL turns each block into one column.