How to Unpivot Data in Excel (Step by Step)

By Joe Lee — Data Analyst • Last updated: 2025-08-13

This tutorial shows two ways to unpivot: Excel Power Query and a fast online tool. You’ll detect when to unpivot, do the steps, and validate results.

Sample Data (Before → After)

Before (wide): Name | Jan | Feb | Mar
After (long): Name | Month | Value

Download sample (before) · Download sample (after)

Method A — Excel (Power Query)

1. Select your data and turn it into a table (Insert → Table, ensure headers).
2. Data → From Table/Range to open Power Query.
3. Select ID columns (e.g., Name). Then Home → Unpivot Columns → Unpivot Other Columns.
4. Rename columns (e.g., Month, Value) and Close & Load.

Method B — Online Unpivot (No Excel Required)

1. Open the tool: Unpivot Tool.
2. Paste or upload .xlsx/.csv.
3. Select ID columns (keep) and value columns (to unpivot).
4. Rename Variable/Value as needed, then download or copy.

Validation Checklist

• Row count equals original rows × number of value columns.
• No mixed data types in the Value column.
• Consistent names for dates/categories.
• No blank or duplicate headers after renaming.

Troubleshooting

• Merged cells or multi-level headers: flatten headers first.
• Unexpected nulls: remove empty columns/rows and trim spaces.
• Wrong IDs: reselect true identifiers and repeat unpivot.

FAQs

Do I need Power Query? No. It’s recommended for repeatability, but online tools work for quick tasks.

Where is the official Excel method? See Microsoft’s Power Query unpivot docs (external).

External reference: Microsoft Docs — Power Query Unpivot (link provided on request)

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.