How to convert wide tables to long format in Excel
If your Excel data has multiple columns for months, years, or categories, it’s likely in a wide format. While this is easy to read, it’s not the best shape for analysis.
In this tutorial, you’ll learn how to convert wide data to long format in Excel using Power Query, plus a quick manual option for small datasets.
📊 Example: Wide vs. Long Data
Wide Format
| Region | January | February | March |
|---|---|---|---|
| East | 120 | 135 | 150 |
Long Format
| Region | Month | Sales |
|---|---|---|
| East | January | 120 |
| East | February | 135 |
🧰 Method 1: Use Power Query (Recommended)
Power Query is built into modern versions of Excel (Excel 2016 and later) and provides an easy one-click way to reshape your data.
Step-by-Step:
Select your table or range.
Go to Data → Get & Transform → From Table/Range.
In the Power Query Editor:
Select the first column (e.g., Region).
Right-click and choose Unpivot Other Columns.
Rename the new columns to Month and Sales.
Click Close & Load to bring the reshaped data back into Excel.
✅ You now have long-format data ready for pivot tables or charts.
🪜 Method 2: Manual Copy and Stack (For Small Datasets)
If you don’t have Power Query or you’re working with a short table:
Copy the Region column several times.
Stack each month’s values beneath one another.
Create a new column labeled Month and fill in the corresponding month names.
This manual method isn’t scalable, but it’s useful for learning how the structure changes.
💡 Why Use Long Format in Excel?
Long-format data makes it easier to:
Build pivot tables that summarize across multiple periods.
Create line charts that auto-update when new months are added.
Export data for Power BI or other analytics tools.
✅Summary
| Method | Difficulty | Best For |
|---|---|---|
| Power Query Unpivot | Easy | All dataset sizes |
| Manual Copy & Stack | Moderate | Small tables |
