Unpivoting Columns to Rows in Excel Using Power Query: A Complete Guide

You've inherited a spreadsheet where months, regions, or product names are spread across dozens of columns. Every analysis tool you reach for — pivot tables, VLOOKUP, charts — expects the data going down rows, not across columns. Reformatting it by hand would take hours and break the moment new data arrives.

Power Query's Unpivot feature solves this in about two minutes, and once you've set it up, it refreshes automatically whenever your source data changes.

What Does Unpivoting Actually Mean?

A pivoted (wide) table looks like this: each unique value in a category gets its own column. A sales spreadsheet might have columns for Jan, Feb, Mar, Apr — one column per month, one row per salesperson.

An unpivoted (long) table collapses those columns into two: one column for the category name (Month) and one for the value (Sales). Each salesperson now has multiple rows, one per month. This long format is what databases, pivot tables, and most charting tools expect.

The operation is conceptually identical to the melt() function in pandas or the UNPIVOT clause in SQL — just wrapped in a point-and-click interface.

What You'll Learn

• How to load tabular Excel data into Power Query.
• Three different ways to unpivot columns (all columns, selected columns, and "other columns").
• How to rename the generated Attribute and Value columns.
• How to load the transformed data back into your workbook.
• The most common mistakes that break the unpivot step.

Prerequisites

• Excel version: Power Query is built into Excel 2016 and later (Windows). On Excel 2010/2013, install the free Power Query add-in from Microsoft. Excel for Mac gained Power Query in 2023; the steps are identical.
• Data format: Your source data should be in an Excel Table (Ctrl+T) or a plain range. Tables are strongly preferred because they expand automatically.
• No VBA or formula knowledge required.

Loading Your Data into Power Query

Before you can unpivot anything, you need to get the data into the Power Query editor. Click any cell inside your data range or Table, then go to Data → Get & Transform Data → From Table/Range. Excel opens the Power Query editor in a new window.

If your data lives in a separate file or another sheet, use Data → Get Data → From File → From Workbook instead, then navigate to the correct table. Either way, you end up in the same editor with the same unpivot options available.

Take a moment to verify the column headers in the editor match what you expect. Power Query reads the first row as headers by default; if yours are wrong, click Home → Use First Row as Headers to fix it before going further.

How to Unpivot All Columns at Once

This option makes sense only when every column in your dataset is a value column — meaning there are no identifier columns like Name, ID, or Region that you want to keep intact. It's rare but it does happen with simple lookup tables.

In the Power Query editor, select all columns by pressing Ctrl+A or clicking the first column header and then Shift-clicking the last. Then right-click any selected header and choose Unpivot Columns. Power Query collapses everything into two columns named Attribute and Value.

How to Unpivot Only Selected Columns

This is the most explicit approach and the easiest to audit later. You click exactly the columns you want to collapse and unpivot just those.

In the editor, hold Ctrl and click each column header you want to collapse (for example, Jan, Feb, Mar). Then right-click any of the highlighted headers and choose Unpivot Columns. The selected columns turn into Attribute/Value rows while every other column stays exactly where it is, repeating its value for each new row.

This method is predictable but brittle: if your source data grows a new column next month (say, a new quarter), Power Query won't unpivot it automatically because it's not in the saved selection. You'd have to edit the query to add the new column. That's why the next option usually wins.

How to Unpivot Other Columns (Keeping Identifier Columns Intact)

This is the option you'll reach for most often. Instead of selecting the columns you want to unpivot, you select the columns you want to keep as identifiers, and Power Query unpivots everything else.

Hold Ctrl and click the identifier columns — the ones that describe who or what the row is about (for example, Salesperson and Region). Then right-click and choose Unpivot Other Columns.

The result is the same Attribute/Value structure, but now Salesperson and Region repeat for every new row. More importantly, any new columns added to your source data in the future will be unpivoted automatically on the next refresh, because Power Query infers "everything that isn't an identifier" at refresh time rather than at design time.

Rule of thumb: Use Unpivot Other Columns when your identifier columns are stable but your value columns grow over time. This is the case for most month-by-month or region-by-region reports.

Renaming the Output Columns

After any unpivot operation, Power Query produces two generated columns named Attribute and Value. These names are functional but meaningless to anyone else opening the workbook.

Double-click the Attribute column header and type a descriptive name — something like Month, Region, or Category depending on what was in those former column headers. Do the same for the Value column: Sales, Units, Revenue, etc.

Each rename appears as a separate step in the Applied Steps pane on the right. That's intentional. Power Query records every transformation so you can delete or reorder steps if something goes wrong — a much safer workflow than editing formulas scattered across a sheet. If you work with Excel data manipulation regularly, the column-skipping techniques covered in our Excel methods guide are worth keeping in your toolkit alongside Power Query.

Loading the Result Back into Excel

When the query looks right, click Home → Close & Load in the Power Query editor. By default, Excel creates a new sheet and drops a formatted Table containing your unpivoted data.

If you want more control — say, loading the result into an existing sheet at a specific cell, or loading it only to the Data Model without creating a visible table — click the dropdown arrow next to Close & Load and choose Close & Load To…. You'll get a dialog with options for Table, PivotTable, PivotChart, and Data Model.

From that point on, anyone with the file can press Data → Refresh All (or right-click the output table and choose Refresh) to re-run the entire transformation against the latest source data. No manual copy-pasting, no broken formulas.

Common Pitfalls and Gotchas

Mixed data types in value columns

If some of your value columns contain numbers and others contain text (or blanks), the resulting Value column will be typed as Any. Downstream pivot tables and charts may then refuse to sum the column. Fix this by adding a Change Type step after the unpivot: select the Value column, right-click, and choose Change Type → Decimal Number (or Whole Number, depending on your data).

Column headers that are numbers

Years like 2021, 2022, 2023 stored as column headers often get promoted as integers, which causes Power Query to rename them to something like Column1, Column2. Before unpivoting, select those columns and use Transform → Data Type → Text to convert the headers to strings. This keeps the year values legible in the Attribute column.

Source table grows new identifier columns

If someone adds a new descriptive column to the source (say, a "Department" column appears next month), Power Query using Unpivot Other Columns will treat it as a value column and unpivot it too. You'd need to re-open the query, find the identifier selection step, and add the new column to the selection. This is easy to fix but worth knowing about before it surprises you in production.

Data that was already partially formatted

Merged cells, blank header rows, and subtotal rows in the source range will corrupt the unpivot. Strip all of those before loading into Power Query. If the source is a report generated by another system, add a Remove Top Rows or Filter Rows step at the top of your query to clean it first. Dirty source data is also the root cause behind issues like the broken VLOOKUP results caused by trailing spaces — Power Query can actually handle that cleanup for you too with a Trim step.

Forgetting to refresh after source changes

Power Query does not auto-refresh when you open the file unless you configure it to do so. Go to Data → Queries & Connections, right-click your query, choose Properties, and enable Refresh data when opening the file if you want hands-free updates.

Duplicate rows appearing in output

If you see more rows than expected, check whether your source table has duplicate or near-duplicate rows. You can also check whether two steps in the Applied Steps pane are doing conflicting things. The guide on finding duplicate values in Excel walks through detection techniques that transfer directly to this kind of cleanup.

Next Steps

You now have a working, refreshable unpivot query. Here's where to go from here:

1. Build a pivot table on the output. Select any cell in the unpivoted table, then go to Insert → PivotTable. The long format lets you drag Month into Rows and Sales into Values immediately — no restructuring needed.
2. Add a data type step. Confirm that the Value column is typed correctly (number, date, or text) so aggregations work without surprises.
3. Enable refresh on open. In Query Properties, turn on Refresh data when opening the file so stakeholders always see current data.
4. Combine with other Power Query steps. Unpivot is often just one step in a longer cleaning pipeline. Consider adding Remove Duplicates, Filter Rows, and Merge Queries steps to fully automate your ETL inside Excel.
5. Document your query. Right-click the query name in the Queries & Connections pane and add a description. Future-you (and your colleagues) will thank you when troubleshooting six months from now.