How to Unpivot Data in Excel Power Query

Dragging data into a pivot table only to find it’s completely unusable is a common headache for anyone working in Excel. The problem often stems from your data being in a "wide" format - structured like a report for humans to read, not for a computer to analyze. This tutorial will walk you through transforming that data using Power Query's Unpivot Columns feature, turning messy reports into clean, analysis-ready tables.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

We’ll cover what “unpivoting” means, provide a step-by-step guide to get it done, and show you how to clean up the results for perfect pivot tables and charts every time.

What Does It Mean to "Unpivot" Data?

Unpivoting is the process of taking data organized horizontally across multiple columns and restructuring it vertically into fewer columns. Think of it as changing data from a "wide" format to a "long" or "tall" format.

This is one of the most fundamental data-shaping tasks you’ll perform. Almost every analytics tool - from Excel PivotTables to Power BI and Tableau - works best with data in a long, tabular format.

An Example: From "Wide" to "Long" Data

Imagine you have a sales report that looks like this. It’s easy to read at a glance, but trying to analyze it is a nightmare. For example, how would you calculate the total sales for Q2 across all regions? Or find the top-performing region for the year? It would require complex formulas.

This is a "Wide" Format:

• Each time period (Q1, Q2, Q3, Q4) gets its own column.
• New data (like a new quarter) requires adding a new column, breaking any existing formulas or charts.

After unpivoting, the same data would look like this:

This is a "Long" Format:

• All similar values (sales numbers) live in a single column.
• Descriptive attributes (the quarters) live in another single column.
• New data can be added simply by adding new rows at the bottom, which is much more scalable.

This long format is perfect for analysis. You can now easily drop it into a PivotTable, filter by quarter, chart sales trends over time, and summarize data without writing a single formula.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Loading Your Data into Power Query

Before you can unpivot, you need to bring your data into the Power Query Editor. The best way to do this is by first formatting your data range as an official Excel Table.

Step 1: Format as an Excel Table

Click anywhere inside your data range and press Ctrl + T (or go to Home > Format as Table on the ribbon). A dialog box will appear. Make sure the range is correct and check the box for "My table has headers." Click OK.

Giving your table a proper format makes it a dynamic data source that Power Query can easily reference.

Step 2: Load the Table into Power Query

With your new Excel Table selected, go to the Data tab on the ribbon. In the "Get & Transform Data" section, click From Table/Range.

This will automatically open the Power Query Editor with your data loaded and ready for transformation.

How to Unpivot Columns in Power Query

Once you’re in the Power Query Editor, the unpivot process is refreshingly simple. There are two primary methods you'll use depending on your data structure.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Method 1: Unpivot Selected Columns (The Classic Approach)

This method is ideal when you want to choose exactly which columns get transformed into rows.

Let's use our regional sales data as an example. We want to keep the "Region" column as is, and unpivot the four quarter columns.

1. Select the columns to unpivot: In the Power Query Editor, hold down the Ctrl key and click on the header for each column you want to unpivot. In our case, click Q1 Sales, Q2 Sales, Q3 Sales, and Q4 Sales. The selected columns will be highlighted.
2. Choose the Unpivot option: Go to the Transform tab in the ribbon. In the "Any Column" group, click the dropdown for Unpivot Columns and select Unpivot Columns. (Alternatively, you can right-click one of the selected headers and choose "Unpivot Columns".)

Instantly, Power Query will transform your selected columns. Your four quarter columns will disappear and be replaced by two new columns, typically named "Attribute" and "Value."

Method 2: Unpivot Other Columns (The Time-Saver)

This method is useful when you have many columns to unpivot and only a few you want to keep static. Instead of selecting 50 columns to unpivot, you can just select the 2 columns you want to keep.

Imagine your sales data included columns for Region, Sales Reps, and Reps ID before the monthly sales columns began. You want to keep those first three columns fixed and unpivot everything else.

1. Select the columns to keep: Hold down the Ctrl key and click on the headers for Region, Sales Reps, and Reps ID. These are the columns that will not be unpivoted.
2. Choose "Unpivot Other Columns": Go to the Transform tab, click the Unpivot Columns dropdown, and select Unpivot Other Columns. (Or right-click a selected header and choose it from the context menu).

Power Query will leave your selected columns alone and unpivot every other column. This is much faster and less error-prone than manually selecting dozens of columns.

Final Steps: Cleaning and Loading Your Data

After unpivoting, you're almost done. The final steps involve a bit of cleanup to make your data presentable and loading it back into your worksheet.

Step 1: Rename the New Columns

Power Query's default column names, "Attribute" and "Value," are not very descriptive. You should rename them:

1. Double-click the header of the "Attribute" column and type a new name, like "Quarter" or "Month." Press Enter.
2. Double-click the header of the "Value" column and rename it to something meaningful, like "Sales Amount" or "Revenue." Press Enter.

Applying clear names is crucial for creating reports that are easy for others (and your future self) to understand.

Step 2: Check and Set Data Types

Power Query usually guesses data types well, but it's good to verify:

• Click the icon to the left of each column header (e.g., "ABC," "123," a calendar icon).
• Ensure your "Sales Amount" column is set to a number type like Currency or Decimal Number.
• Ensure your "Quarter" or descriptive columns are set to Text.

Correct data types ensure accurate calculations and sorting.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Step 3: Close & Load the Transformed Data

Once satisfied, load your data back into Excel:

1. Go to the Home tab in the Power Query Editor.
2. Click the top half of the Close & Load button. This will load the data into a new worksheet as an Excel Table.

If you want more control, click the bottom half of the button and select Close & Load To.... In the dialog box, you can choose to load as:

• A Table: The default, most common.
• A PivotTable Report: Directly into a PivotTable.
• A PivotChart: Creates both a pivot table and a chart.
• Only Create Connection: Loads the query but doesn’t output data, useful for data models.

Your transformed data is now on your worksheet. It remains connected to the original. If the original data changes, right-click the output table and select Refresh. Power Query will re-run all steps automatically.

Final Thoughts

Mastering the unpivot feature in Power Query fundamentally changes what's possible with your data in Excel. It empowers you to take disorganized, human-readable reports and instantly reshape them into a structured format perfect for deep, flexible analysis in PivotTables and beyond.

While Power Query is a powerful tool for restructuring a spreadsheet, this manual cleanup is often just the first step in a bigger reporting process. For connecting and blending data from multiple sources—like combining Excel files with data from Google Analytics, Salesforce, or ad platforms—you might hit a wall. At Graphed, we’ve built a tool that automates this entire pipeline. You can connect your data sources in a few clicks, and instead of navigating menus to unpivot and transform, you can simply ask for the chart or report you need in plain English. Our AI handles the transformations, building live dashboards that update automatically.