Unpivot Data in Excel With Power Query

To unpivot data in Excel, load the table into Power Query, select the columns you want to keep, and choose Unpivot Other Columns. Every month (or product, or year) column turns into rows, so you get one long list that pivot tables and SUMIFS can read. This guide is for anyone who receives reports with a column for every month and needs to analyse them properly.

What does unpivot mean?#

Unpivoting turns a wide table into a long one. A wide table has one column per month. A long table has one row per region and month, with the month name in its own column.

Here is a wide table, the kind you often get from a budget or a report:

And here is the same data unpivoted:

Nothing was added or lost: six numbers in, six numbers out. Unpivoting is not the same as transposing. Transpose just flips rows and columns, so the table is still wide, only sideways.

Why unpivot data before you analyse it?#

Most Excel tools expect long data, with one value per row and one column per kind of information. With the long table:

• A pivot table can put Month in Rows or Columns, filter to one month, or total by region with no extra work.
• SUMIFS needs one column to check. "Total for Feb" is =SUMIFS(C2:C7,B2:B7,"Feb"), which returns 245 (135 + 110).
• Adding April means adding rows, not a new column that every formula must learn about.

With the wide table, each month is a different column, so every month needs its own formula, and a pivot table treats Jan, Feb and Mar as three separate fields. The beginner's guide to pivot tables explains why pivots want one column per field.

How to unpivot data in Excel with Power Query#

Power Query is Excel's built-in tool for importing and reshaping data in recorded steps. It's on the Data tab in Excel 2016 and later and Microsoft 365. The Power Query guide for beginners gives a full tour. To unpivot:

1. Click any cell in the wide table.
2. Go to Data > From Table/Range. If Excel asks to create a table, check that My table has headers is ticked and click OK.
3. The Power Query Editor opens. Click the Region column header. To keep more than one label column, hold Ctrl (Mac: ⌘) and click each one.
4. Go to Transform > Unpivot Columns (click the small arrow) and choose Unpivot Other Columns. You can also right-click the selected header and pick it there.
5. Power Query creates two new columns, Attribute (the old headers) and Value (the numbers). Double-click each header and rename them Month and Sales.
6. Click Home > Close & Load. The long table lands on a new sheet.

If the old headers were dates, check the Month column's data type in the editor (the small icon on the header) so Excel treats them as dates, not text.

Unpivot Columns vs Unpivot Other Columns#

Both commands give the same result today. The difference shows up next month.

• Unpivot Columns unpivots only the columns you selected (Jan, Feb, Mar). When April arrives, it is left as a separate column.
• Unpivot Other Columns unpivots everything except the columns you selected (Region). When April arrives, it is unpivoted too.

So select the label columns and use Unpivot Other Columns. It's the safer habit for any report that grows.

Refresh the unpivoted table when data changes#

The query remembers every step. When the wide table gets new rows or a new month column, go to Data > Refresh All (Windows: Ctrl+Alt+F5) and the long table rebuilds itself. You never repeat the clicks.

This repeatability is the main reason to use Power Query over a manual copy and paste. The article on Power Query vs formulas compares the two approaches in more depth. The same tool can also combine sheets into one table before you unpivot them.

How to unpivot data with formulas#

If Power Query isn't available, for example in an older version of Excel for Mac, formulas can rebuild the long table. Put the numbers 1 to 6 in a helper column, say F2:F7 (two regions × three months). Then, in row 2:

Region: =INDEX($A$2:$A$3,INT((F2-1)/3)+1)
Month:  =INDEX($B$1:$D$1,MOD(F2-1,3)+1)
Sales:  =INDEX($B$2:$D$3,INT((F2-1)/3)+1,MOD(F2-1,3)+1)

Fill the three formulas down. The 3 is the number of month columns. INT((n−1)/3)+1 moves to the next region every three rows, and MOD(n−1,3)+1 cycles through months 1, 2, 3. For n = 5 the formulas return South, Feb and 110.

This works in every version, but you must update the ranges and the 3 by hand when months are added. Microsoft 365 also has a TOCOL function that stacks a block of values into one column, which shortens the job.

Practise it#

Once your data is long, summarising it is the easy part. Build your first pivot table in a live spreadsheet: you drag the fields and each step is checked instantly. Then try the pivot-style summary with formulas lesson, which builds a summary from a long table with UNIQUE and SUMIFS. Start with the SUMIFS exercise on this page.