5.17 Unpivot: Wide to Long (Concept)
Reports often come wide (one column per month). Analysis tools (PivotTables, Power BI) need long data: one row per city per month.
Before (wide)
| City | Aug | Sep | Oct |
|---|---|---|---|
| Pune | 4,12,500 | 5,86,200 | 4,95,300 |
| Nashik | 1,48,900 | 1,96,400 | 1,62,700 |
About this data: Teaching sample for a fictional shop “Orbit Logistics”. Figures and names (e.g. Vikram Rao) are made up for this lesson on unpivot wide to long concept.
After (long)
| City | Month | Sales |
|---|---|---|
| Pune | Aug | 4,12,500 |
| Pune | Sep | 5,86,200 |
| Pune | Oct | 4,95,300 |
| Nashik | Aug | 1,48,900 |
| Nashik | Sep | 1,96,400 |
| Nashik | Oct | 1,62,700 |
About this data: Demo table only — not real customer or company data. Built to practise unpivot wide to long concept.
Steps in Excel (Power Query – full detail in Module 10.6)
- Click inside the wide table › Data › Get & Transform Data › From Table/Range.
- In Power Query, select the City column › Transform › Unpivot Columns ▾ › Unpivot Other Columns.
- Rename Attribute →
Month, Value →Sales› Home › Close & Load.
Why "Unpivot Other Columns"? When November is added next month, it is unpivoted automatically.
Ravindra Bagale's Tip
Many students try to build a PivotTable on wide data and drag each month in as a separate field – and then a "total by month" chart can't be made at all. The rule: one column = one kind of data (Month is one column, Sales is one column). When you see wide data, unpivot it first, then pivot.
Ravindra Bagale's Tip – मराठी
बरेच students wide data वर PivotTable बनवायचा प्रयत्न करतात आणि प्रत्येक month वेगळं field म्हणून ओढतात – मग "total by month" chart बनतच नाही. नियम: एक column = एक प्रकारचा data (Month हा एक column, Sales हा एक column). Wide data दिसला की आधी unpivot करा, मग pivot.
Ravindra Bagale's Tip – हिंदी
बहुत से students wide data पर PivotTable बनाने की कोशिश करते हैं और हर month को अलग field के रूप में खींचते हैं – फिर "total by month" chart बनता ही नहीं. नियम: एक column = एक तरह का data (Month एक column, Sales एक column). Wide data दिखे तो पहले unpivot करो, फिर pivot.
Worked example — unpivot wide to long concept
Why
Aamir Khan in Pune and Emma Clarke abroad both need a clear, repeatable way to use unpivot wide to long concept on a CityBasket Retail sheet without breaking totals.
What
Unpivot Wide To Long Concept is the Excel idea this page teaches. We will walk one tiny example end to end.
How
- Open a blank sheet. In A1 type a short label for unpivot wide to long concept.
- Enter a small made-up value for Aamir Khan using ₹ where money appears.
- Add a second value for Emma Clarke using ₹ so you see mixed currencies stay numeric.
- Apply the method from this lesson step by step (menu or formula as shown above).
- Check the result still works inside SUM or a simple check cell.
Formula / action
Follow the formula or ribbon steps shown in this lesson (keep values numeric).Meaning: Once unpivot wide to long concept is correct on one row, copy the same pattern down the column.
Practice tasks (10+)
Try these on a blank sheet. Open a hint only if you are stuck. No quiz — just practice.
1. Rebuild the smallest example of unpivot wide to long concept on a blank sheet for Orbit Logistics.
Hint: Use only the steps on this page. Keep numbers numeric.
2. Change one input for Meera Joshi and confirm the result updates.
Hint: If the result is stuck, check absolute vs relative references.
3. Repeat the same steps with a £ or € value for Sophie Martin.
Hint: Formatting must not break SUM.
4. Write the result meaning in one plain sentence next to the cell.
Hint: Imagine explaining it to a new joiners.
5. Add a second row and fill the method down.
Hint: Double-click the fill handle if the pattern is consistent.
6. Break the example on purpose, then fix it using this lesson.
Hint: Common break: text instead of a number.
7. Apply unpivot wide to long concept to a 5-row made-up Orbit Logistics table.
Hint: Keep column headers clear.
8. Check your answer with a simple total or COUNT.
Hint: A wrong total usually means a format or key mismatch.
9. Swap ₹ and $ on one column and re-check totals.
Hint: Stored values stay the same; only the display changes.
10. List two mistakes beginners make with unpivot wide to long concept.
Hint: Look back at the tip box on this page.
11. Teach the steps out loud in under one minute.
Hint: Why → What → How.
12. Save the sheet as OrbitLogist_practice.xlsx.
Hint: Use a clear file name for class.
Quick revision cards
Three standalone cards. Each one explains the idea with its own example. Scan the QR to open this lesson.
