8.9 Treemap and Sunburst
Excel 2016+. Both show hierarchies (Category › Product, City › Area) with size = value.
- Treemap – rectangles; best to compare sizes within a hierarchy.
- Sunburst – rings; best to show levels of a hierarchy.
Steps in Excel
- Arrange data with hierarchy columns first: City | Area | Sales (City repeated on each row).
- Select it › Insert › Charts › Insert Hierarchy Chart › Treemap (or Sunburst).
- Ctrl + 1 › Series Options › Label Options › Banner for parent labels (treemap).
Worked example. Treemap of sales by City › Area: Pune's block (Kothrud, Hinjewadi, Baner, Hadapsar, Wakad) is the largest; inside Nagpur, Dharampeth is bigger than Sitabuldi.
Ravindra Bagale's Tip
For a treemap, many students give a summary table (city in one row, areas beside it) – and the chart isn't created. Hierarchy charts need long format: every row has City, Area, Sales. And if there are more than 30 small boxes, the labels don't show – take the top items.
Ravindra Bagale's Tip – मराठी
Treemap साठी बरेच students summary table (एकाच row मध्ये city, बाजूला areas) देतात – chart बनतच नाही. Hierarchy charts ला long format लागतो: प्रत्येक row ला City, Area, Sales. आणि 30 पेक्षा जास्त लहान boxes असतील तर labels दिसत नाहीत – Top items घ्या.
Ravindra Bagale's Tip – हिंदी
Treemap के लिए बहुत से students summary table (एक row में city, बगल में areas) दे देते हैं – chart बनता ही नहीं. Hierarchy charts को long format चाहिए: हर row में City, Area, Sales. और 30 से ज़्यादा छोटे boxes हों तो labels नहीं दिखते – Top items लो.
Worked example — treemap and sunburst
Why
Vikram Rao in Pune and Liam Murphy abroad both need a clear, repeatable way to use treemap and sunburst on a BrightLedger Finance sheet without breaking totals.
What
Treemap And Sunburst 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 treemap and sunburst.
- Enter a small made-up value for Vikram Rao using $ where money appears.
- Add a second value for Liam Murphy 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 treemap and sunburst 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 treemap and sunburst on a blank sheet for NovaCraft Tools.
Hint: Use only the steps on this page. Keep numbers numeric.
2. Change one input for Aarti Kulkarni 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 Oliver Bennett.
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 treemap and sunburst to a 5-row made-up NovaCraft Tools 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 treemap and sunburst.
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 NovaCraftTo_practice.xlsx.
Hint: Use a clear file name for class.
Loading practice tasks...
- Total units sold (Easy): Add up Units sold in the "Units sold" column.
- Average price (Easy): Average of the Price (₹) column, rounded to 2 decimals.
- Rows in Snacks (Easy): How many rows have Category/group "Snacks"? (the "Category" column)
- Largest units sold (Easy): The largest value in the "Units sold" column.
- Units sold for Snacks (Medium): Total of the Units sold column where Category is "Snacks".
- Snacks in Delhi (Medium): Total of the Units sold column where Category is "Snacks" and City is "Delhi".
- Best in Snacks (Medium): Largest value in the "Price (₹)" column where "Category" is "Snacks".
- Value of row 2 (Medium): Multiply the first-row Units sold by the first-row Price (₹).
- Look up Vikram Singh (Medium): The Units sold for "Vikram Singh" (use a lookup on the "Name" column).
- Total value (Hard): Sum of "Units sold" times "Price (₹)" for all rows.
- Share of Snacks (Hard): Share of the "Units sold" column that is "Snacks", rounded to 3 decimals.
- Total "Units sold" for Snacks (Hard): Add up the "Units sold" column for the rows where "Category" is "Snacks".
- Above average? (Hard): "Above" if the "Price (₹)" value in the first row is above the average of the "Price (₹)" column, else "Below".
Quick revision cards
Three standalone cards. Each one explains the idea with its own example. Scan the QR to open this lesson.
