How to Use Excel Power Query to Unpivot Data for PivotTables

One of the most common data analysis failures in business occurs when a user tries to build a PivotTable, only to realize the source data is formatted incorrectly. Humans love reading “wide” data—where months or years are spread horizontally across multiple columns (e.g., Column B is Jan, Column C is Feb, Column D is Mar). However, database systems and PivotTables require “tall” (tabular) data, where there is only one column for “Month” and one column for “Sales Amount.” If you try to build a PivotTable from wide data, it is impossible to group or filter by month. Instead of manually copying and pasting thousands of rows to transpose the data, you can instantly transform it using Excel’s most powerful data tool: Power Query.

The Problem with Wide Data

Imagine a spreadsheet that tracks regional sales:

• Column A: Region (North, South, East, West)
• Column B: Q1 Revenue
• Column C: Q2 Revenue
• Column D: Q3 Revenue
• Column E: Q4 Revenue

To analyze this in a PivotTable properly, you need the data to be three columns: Region, Quarter, and Revenue. This process of converting columns into rows is called Unpivoting.

Step 1: Load Data into Power Query

Power Query is a data transformation engine built directly into modern versions of Excel.

1. Click anywhere inside your wide data table.
2. Go to the Data tab on the ribbon.
3. In the “Get & Transform Data” group, click From Table/Range.
4. If Excel asks to confirm the range of your data, verify it is correct and ensure “My table has headers” is checked. Click OK.

A new, separate window will open. This is the Power Query Editor. It is a completely safe sandbox; nothing you do in this window alters your original spreadsheet.

Step 2: Unpivot the Columns

This is where the magic happens.

1. In the Power Query Editor, locate the column that you do not want to unpivot. In our example, this is the Region column, because it is already formatted correctly as a category.
2. Click the header of the Region column so it is highlighted.
3. Right-click the Region header.
4. From the dropdown menu, select Unpivot Other Columns.

Instantly, Power Query deletes the wide Q1, Q2, Q3, and Q4 columns. In their place, it creates two brand new columns. It collapses all the headers (the quarter names) into a single column called “Attribute,” and it stacks all the numerical data into a single column called “Value.”

Step 3: Rename and Clean Up

Before sending the data back to Excel, you should clean it up so the PivotTable reads cleanly.

1. Double-click the header of the new Attribute column. Rename it to Quarter.
2. Double-click the header of the new Value column. Rename it to Revenue.
3. Ensure the data types are correct by clicking the small icon in the top left of each column header. (Set Quarter to Text, and set Revenue to Currency or Decimal Number).

Step 4: Load the Tabular Data Back to Excel

Now that the data is perfectly formatted (tall and tabular), it is ready for analysis.

1. In the top-left corner of the Power Query Editor ribbon, click the Close & Load button.

Power Query will close and generate a brand-new worksheet in your Excel workbook containing the beautiful, unpivoted data. You can now insert a PivotTable based on this new green table, allowing you to easily drag “Quarter” into the rows, columns, or filters area.

The Best Part: It is Automated

The true power of Power Query is that it records the steps you took, not just the static result. Next year, when someone adds a “Q5” or “Next Year Q1” column to the original messy data sheet, you do not have to unpivot it again. You simply go to your clean table, right-click, and select Refresh. Power Query will automatically grab the new wide columns, unpivot them, and update your PivotTable in one second.