What if the months are in rows and products in columns?

The same idea applies with the roles swapped: keep the month column and unpivot the product columns, giving one row per month per product.

Does unpivoting make the file much bigger?

It has more rows but the same number of values, so size grows only modestly. Tools such as Power Query, pandas and DuckDB handle long tables easily.

Should I unpivot in the source file?

No. Keep the original report untouched and unpivot in Power Query or a script, so the step repeats automatically when a new version arrives.

What's the difference between Unpivot Columns and Unpivot Other Columns?

Unpivot Columns lists the columns to turn into rows, so new month columns are ignored. Unpivot Other Columns lists the columns to keep, so new months are picked up automatically.

Can I unpivot with a formula in Excel?

In Microsoft 365, combinations of TOCOL, CHOOSECOLS and HSTACK can do it, but Power Query is easier to maintain for anything beyond a small table.

Do blank cells survive unpivoting?

Power Query's unpivot drops null values. If a blank means zero, replace nulls with 0 first.

How do I handle a two-row header (year above month)?

Combine the two header rows into one (for example, '2026-Jan') before unpivoting, then split the attribute column back into year and month.

Why and how to unpivot wide reports before merging

By the Analistable team · Updated 2026-10-10 · 5 min read

A wide report has one column per month (Jan, Feb, Mar…). Two such reports rarely line up because their columns differ, so stacking them misaligns months. Unpivot each one to long format, with one row per item per month and columns like product, month, value, then append or join on product and month. In Power Query, select the ID columns and choose Unpivot Other Columns.

Part of our guide: How to combine Excel sheets into one

Wide vs long

Wide: one column per month
product	Jan	Feb	Mar
Mugs	120	95	140
Plates	80	110	90

Long: one row per product per month
product	month	units
Mugs	Jan	120
Mugs	Feb	95
Mugs	Mar	140
Plates	Jan	80
Plates	Feb	110
Plates	Mar	90

Both hold the same six numbers (total 635). The long version has fixed columns no matter how many months there are, which is what makes it safe to stack and join.

What goes wrong if you don't

Stacking: this year's file has Jan–Mar, last year's has Jan–Dec. Appending them creates a jagged table with nine empty month columns on one half.
Different headers: one file says "Jan-26", another "January 2026". Append treats them as different columns.
Joining: to compare budget and actual by month you'd need a lookup for every month column. In long format it's a single join on product and month.
Totals: a total column or total row in a wide report gets treated as another month or product and double-counts.

Unpivot in Power Query

Select the report and choose Data > From Table/Range (or get it from a file).
Remove any Total column and filter out Total rows.
Select the identifier columns (product, region), right-click > Unpivot Other Columns.
Rename Attribute to month and Value to units.
Convert month to a real date: add a column with Date.FromText("1 " & [month] & " 2026") or parse the header format you have, then set the type to Date.
Repeat for the other reports (or turn the steps into a function), then use Append Queries.

Choose Unpivot Other Columns rather than Unpivot Columns: when next month's column appears, it's included automatically because the step names the columns to keep, not the ones to unpivot. After unpivoting, Append Queries stacks the reports cleanly.

let
    Source    = Excel.CurrentWorkbook(){[Name="Sales2026"]}[Content],
    NoTotal   = Table.RemoveColumns(Source, {"Total"}),
    Unpivoted = Table.UnpivotOtherColumns(NoTotal, {"product"}, "month", "units"),
    WithYear  = Table.AddColumn(Unpivoted, "year", each 2026, Int64.Type)
in
    WithYear

Unpivot in pandas and SQL

import pandas as pd

wide = pd.read_excel("sales_2026.xlsx")
long = wide.melt(id_vars=["product"], var_name="month", value_name="units")
long["month"] = pd.to_datetime(long["month"] + " 2026", format="%b %Y")

DuckDB has an UNPIVOT statement that selects month columns by exclusion, much like Power Query:

UNPIVOT sales_2026
ON COLUMNS(* EXCLUDE (product))
INTO NAME month VALUE units;

Other databases differ: SQL Server and Snowflake have UNPIVOT with an explicit column list, and elsewhere you can UNION ALL one SELECT per month column.

Then merge

With budget and actuals both in long format, comparing them is a straightforward join on product and month. Budget for Mugs in Feb is 100 and actual is 95, so variance is −5; Plates in Feb has budget 100 and actual 110, a variance of +10.

Budget vs actual after unpivoting both reports (February)
product	month	budget	actual	variance
Mugs	Feb 2026	100	95	-5
Plates	Feb 2026	100	110	10
Total		200	205	5

If you need the wide layout for presentation, pivot it back at the end with a PivotTable. Keep the long table as your source. For a broader view of stacking sheets see the combine Excel sheets guide and combining files with different columns.

Analistable reads wide sheets as they are, and its AI can unpivot them in the SQL it writes (DuckDB supports UNPIVOT), so you can ask "which products missed budget in February?" across two wide reports. Long-format sources still give the most predictable answers.

Clean the report before unpivoting

Wide reports built for people usually carry extras that a database can't use. Remove title rows above the header, blank spacer rows, subtotal rows within groups and notes at the bottom. Fill down group labels if the report shows a region only on its first row, otherwise the unpivoted rows will have blank regions. Make sure every month header is unique; two columns both called 'Q1' will become indistinguishable once they're values in a single column.

Check numbers stored as text too. After unpivoting, set the value column's type to a number; any cells that fail to convert (such as '-' or 'n/a') show up as errors you can replace with null or zero deliberately.

Check the totals match

Before and after unpivoting, the sum of all values must be the same. Sum the month columns in the wide report (excluding any total column) and compare with the sum of the value column in the long table. In the example above both are 635. If the long table is higher, a total column or total row slipped through; if lower, blanks were dropped that should have been zeros.

Frequently asked questions

What if the months are in rows and products in columns?: The same idea applies with the roles swapped: keep the month column and unpivot the product columns, giving one row per month per product.
Does unpivoting make the file much bigger?: It has more rows but the same number of values, so size grows only modestly. Tools such as Power Query, pandas and DuckDB handle long tables easily.
Should I unpivot in the source file?: No. Keep the original report untouched and unpivot in Power Query or a script, so the step repeats automatically when a new version arrives.
What's the difference between Unpivot Columns and Unpivot Other Columns?: Unpivot Columns lists the columns to turn into rows, so new month columns are ignored. Unpivot Other Columns lists the columns to keep, so new months are picked up automatically.
Can I unpivot with a formula in Excel?: In Microsoft 365, combinations of TOCOL, CHOOSECOLS and HSTACK can do it, but Power Query is easier to maintain for anything beyond a small table.
Do blank cells survive unpivoting?: Power Query's unpivot drops null values. If a blank means zero, replace nulls with 0 first.
How do I handle a two-row header (year above month)?: Combine the two header rows into one (for example, '2026-Jan') before unpivoting, then split the attribute column back into year and month.