How to Unpivot Data in Excel (Step by Step)
By Joe Lee — Data Analyst • Last updated: 2026-06-05
This tutorial shows two ways to unpivot your data: Excel Power Query (for repeatable workflows) and a fast online tool (for quick, one-off tasks). You will learn when to use each method, walk through the exact steps, and validate your results.
Sample Data (Before → After)
Below is a practical example using regional sales data. The wide format has each quarter as a separate column:
Before (wide format)
| Region | Q1 | Q2 | Q3 | Q4 |
|---|---|---|---|---|
| East | 12000 | 14500 | 13200 | 15800 |
| West | 9800 | 10200 | 11500 | 12100 |
After unpivoting, each quarter becomes a row value — making it easy to filter by quarter, calculate growth rates, or build charts:
After (long format)
| Region | Quarter | Revenue |
|---|---|---|
| East | Q1 | 12000 |
| East | Q2 | 14500 |
| East | Q3 | 13200 |
| East | Q4 | 15800 |
| West | Q1 | 9800 |
| West | Q2 | 10200 |
| West | Q3 | 11500 |
| West | Q4 | 12100 |
Download sample (before) · Download sample (after)
Method A — Excel Power Query (Recommended for Recurring Tasks)
Power Query is Excel's built-in data transformation tool. It records your steps as a repeatable query, so you can refresh the transformation whenever your source data changes.
Step 1: Convert Your Data to an Excel Table
Select your entire data range (including headers), then go to Insert → Table. Make sure the "My table has headers" checkbox is checked. This step is important because Power Query works with official Excel tables — raw cell ranges will not appear in the Power Query options.
Step 2: Open Power Query Editor
Click anywhere inside the table, then go to the Data tab on the ribbon and select From Table/Range. This launches the Power Query Editor, a separate window where all transformations happen. Your data should appear as a table with column headers visible.
Step 3: Select Identifier Columns and Unpivot
First, select the columns you want to keep as identifiers (e.g., the "Region" column). Hold Ctrl to select multiple columns. Then go to Home → Unpivot Columns → Unpivot Other Columns. This keeps your selected columns unchanged and stacks everything else into two new columns: "Attribute" (the former header) and "Value" (the cell content).
Common pitfall: If you select "Unpivot Columns" instead of "Unpivot Other Columns", Power Query will unpivot only the columns you selected — the opposite of what you want. Always think about which columns should stay as-is, then choose "Unpivot Other Columns".
Step 4: Rename Columns and Close
Double-click the "Attribute" column header and rename it to something meaningful (e.g., "Quarter" or "Month"). Do the same for "Value" (e.g., "Revenue" or "Sales"). Then click Close & Load in the top-left corner. Power Query creates a new worksheet with your unpivoted data and a query that you can refresh anytime.
Tips for Large Datasets
- Check data types after unpivoting — the Value column should be a number type, not text. Click the column header icon to set it.
- Remove blank rows before unpivoting to avoid null values in your results.
- If you have hundreds of columns, consider selecting a subset first instead of unpivoting all at once.
- Right-click the query in the Queries & Connections pane to see the applied steps — you can delete or reorder them if needed.
Method B — Online Unpivot Tool (Fastest for Ad-Hoc Tasks)
If you do not have Excel or need a quick result without setup, the online tool handles everything in your browser with no data leaving your device.
- Open the tool: Go to Unpivot Tool.
- Paste or upload: Copy your data from Excel and paste it into the input area, or upload a .xlsx or .csv file.
- Select columns: The tool detects your headers automatically. Choose which columns are identifiers (to keep) and which are values (to unpivot) using the column selection interface.
- Download or copy: Preview the unpivoted result, rename the Variable/Value columns if needed, then download as Excel (.xlsx) or CSV, or copy to clipboard.
Privacy: All processing happens locally in your browser. No data is uploaded to any server. This makes it safe for sensitive financial, HR, or proprietary business data.
Validation Checklist
After unpivoting, verify these four checks to ensure correctness:
- Row count equals original rows × number of value columns (e.g., 2 rows × 4 quarters = 8 output rows).
- No mixed data types in the Value column — numbers should stay as numbers, not convert to text.
- Consistent names for dates/categories — "Q1" and "q1" are treated as different values.
- No blank or duplicate headers after renaming.
Troubleshooting
- Merged cells or multi-level headers: Flatten headers first. If your Excel file has merged cells in the header row, use the Unmerge & Fill tool to expand them, then re-run the unpivot.
- Unexpected nulls: Remove empty columns/rows and trim spaces before unpivoting.
- Wrong IDs: Reselect true identifiers and repeat the unpivot operation.
FAQs
Do I need Power Query?
+No. It is recommended for repeatability and recurring transformations, but online tools work perfectly for quick one-off tasks.
What if my headers are merged or multi-level?
+Merged headers must be expanded before unpivoting. In Excel, select the merged area and click "Unmerge Cells" under the Home tab. Alternatively, use the Unmerge & Fill tool to handle this automatically — it detects merged cells, expands them, and fills in the missing values so your data is ready for unpivot.
Can I undo an unpivot operation?
+Yes. In Power Query, use Ctrl+Z to step back, or delete the Unpivot step from the Applied Steps list on the right panel. In the online tool, simply re-upload your original file. If you already saved the unpivoted result, you can also re-pivot it by creating a pivot table from the long-format output.
What is the difference between "Unpivot Columns" and "Unpivot Other Columns"?
+"Unpivot Columns" unpivots only the columns you have selected. "Unpivot Other Columns" does the opposite — it keeps your selected columns as identifiers and unpivots everything else. For most use cases, "Unpivot Other Columns" is the right choice because it is easier to select the one or two ID columns you want to keep rather than selecting dozens of value columns.
How do I handle blank cells during unpivot?
+Blank cells in the value columns become null (empty) values in the unpivoted output. This is usually fine — you can filter them out afterward in Power Query by clicking the filter arrow on the Value column and unchecking "(null)". However, if blanks appear because columns have different lengths, fix the source data first to avoid skewed results.
Where is the official Excel method?
+See Microsoft's Power Query Unpivot docs for the native Excel method.
Methodology: Who, How, Why
Who: Written by Joe Lee (Data Analyst).
How: Steps compiled from standard Excel operations and ad-hoc workflows.
Why: Help you finish the task faster and with fewer errors.