Treemap and sunburst in Excel - Deep Dive

Overview - Treemap and sunburst

What is it?

Treemap and sunburst are types of charts in Excel that help you visualize hierarchical data. A treemap shows data as nested rectangles, where the size and color represent values. A sunburst chart displays the same data as concentric rings, with each ring representing a level in the hierarchy. Both make it easier to see parts of a whole and relationships within complex data.

Why it matters

Without treemap and sunburst charts, understanding large hierarchical data sets can be confusing and slow. These charts turn complex data into clear visuals, helping you spot patterns, compare categories, and make decisions faster. They save time and reduce errors when analyzing data with many layers.

Where it fits

Before learning treemap and sunburst charts, you should understand basic Excel charts and how to organize data in tables. After mastering these charts, you can explore advanced data visualization tools like PivotCharts and Power BI for deeper analysis.

Mental Model

Core Idea

Treemap and sunburst charts turn hierarchical data into visual shapes that show size and structure at a glance.

Think of it like...

Imagine a family tree drawn as a set of nested boxes (treemap) or as rings of a tree trunk (sunburst), where each box or ring slice shows how big or important each family branch is.

Hierarchy Data
  ├─ Treemap: Nested rectangles sized by value
  │    ┌─────────────┐
  │    │  Category 1 │
  │    │ ┌───────┐   │
  │    │ │Item A │   │
  │    │ └───────┘   │
  │    └─────────────┘
  └─ Sunburst: Concentric rings
       ┌─────────────┐
       │   Center    │
       │  Category   │
       └─────────────┘
        ◯ Outer rings show subcategories

Build-Up - 7 Steps

1

Foundation

Understanding Hierarchical Data

Concept: Learn what hierarchical data means and how it is structured.

Hierarchical data is organized like a family tree or folders on your computer. It has levels: a top level with broad categories, and lower levels with more detailed subcategories. For example, a company might have departments, teams, and employees arranged in a hierarchy.

Result

You can identify levels and relationships in your data, which is necessary before making treemap or sunburst charts.

Understanding the structure of your data is key to choosing the right chart and organizing it properly.

2

Foundation

Preparing Data for Charts

Concept: How to arrange hierarchical data in Excel for treemap and sunburst charts.

Excel needs your data in a table with columns representing each level of the hierarchy and a value column. For example, columns might be Region, Country, City, and Sales. Each row shows one path down the hierarchy with its value.

Result

Your data is ready for Excel to read and create the charts correctly.

Proper data layout ensures Excel understands the hierarchy and displays it accurately.

3

Intermediate

Creating a Treemap Chart

🤔Before reading on: do you think a treemap shows data as circles or rectangles? Commit to your answer.

Concept: Learn how to insert a treemap chart and what its parts represent.

Select your hierarchical data including the value column. Go to Insert > Hierarchy Chart > Treemap. Excel draws nested rectangles sized by value. Larger rectangles mean bigger values. Colors group related categories.

Result

You get a colorful chart showing your data as nested boxes, making it easy to compare sizes and see structure.

Knowing how size and color work together helps you interpret the treemap quickly.

4

Intermediate

Creating a Sunburst Chart

🤔Before reading on: do you think a sunburst chart uses rings or bars to show hierarchy? Commit to your answer.

Concept: Learn how to insert a sunburst chart and understand its ring structure.

Select your hierarchical data including values. Go to Insert > Hierarchy Chart > Sunburst. Excel draws concentric rings. The center is the top level, outer rings are deeper levels. Each slice size shows the value.

Result

You see your data as rings, making it easy to follow paths from top to bottom and compare parts.

Understanding rings as levels helps you trace categories and subcategories visually.

5

Intermediate

Customizing Chart Appearance

Concept: Learn how to change colors, labels, and styles to improve readability.

Click on the chart and use Chart Tools to change colors, add data labels, or adjust fonts. You can highlight important categories or make the chart match your report style. Labels can show names, values, or percentages.

Result

Your chart becomes clearer and more attractive, making it easier to share insights.

Customizing visuals helps communicate your data story effectively.

6

Advanced

Interpreting Complex Hierarchies

🤔Before reading on: do you think treemap and sunburst charts handle many hierarchy levels equally well? Commit to your answer.

Concept: Understand the strengths and limits of these charts with deep hierarchies.

Treemaps can become cluttered with many small rectangles, making details hard to see. Sunburst charts can show many levels but slices get thinner and harder to read. Use filters or group small items to keep charts clear.

Result

You can decide when to simplify data or choose a different visualization for clarity.

Knowing chart limits prevents confusing visuals and helps maintain clear communication.

7

Expert

Advanced Data Preparation and Dynamic Charts

🤔Before reading on: do you think Excel charts update automatically when data changes? Commit to your answer.

Concept: Learn how to prepare data for dynamic updates and use named ranges or tables for live charts.

Use Excel Tables or named ranges for your data source. When you add or change data, the treemap or sunburst chart updates automatically. You can also combine with slicers or filters to interactively explore data.

Result

Your charts stay current without manual updates, saving time and reducing errors.

Understanding dynamic data sources makes your reports more powerful and user-friendly.

Under the Hood

Excel reads hierarchical data as paths from top to bottom levels. For treemaps, it calculates rectangle sizes proportional to values and arranges them to fill space efficiently. For sunbursts, it divides a circle into slices per category, stacking rings for each hierarchy level. The chart engine uses algorithms to balance size, color, and layout for clarity.

Why designed this way?

These charts were designed to visualize complex hierarchies compactly and intuitively. Treemaps use space-filling rectangles to maximize area usage, while sunbursts use rings to show levels clearly. Alternatives like nested pie charts or lists were less space-efficient or harder to read.

Data Table
  ┌───────────────┐
  │ Level 1       │
  │ Level 2       │
  │ Level 3       │
  │ Value         │
  └───────────────┘
       │
       ▼
  ┌───────────────┐       ┌───────────────┐
  │ Treemap Chart │       │ Sunburst Chart│
  │ Nested Rects  │       │ Concentric    │
  │ Size by Value │       │ Rings by Level│
  └───────────────┘       └───────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Do treemap charts always show data sorted by size? Commit to yes or no.

Common Belief: Treemap charts automatically sort rectangles from largest to smallest.

Reality: Excel treemaps do not always sort by size; they arrange rectangles to fill space efficiently, which may not be size order.

Why it matters: Expecting sorted rectangles can cause confusion when interpreting the chart, leading to wrong conclusions about category importance.

Quick: Can sunburst charts show non-hierarchical data effectively? Commit to yes or no.

Common Belief: Sunburst charts work well for any data, even if it has no hierarchy.

Reality: Sunburst charts require hierarchical data; without it, the chart loses meaning and becomes confusing.

Why it matters: Using sunburst charts on flat data wastes space and misleads viewers about relationships.

Quick: Do treemap and sunburst charts always update instantly when data changes? Commit to yes or no.

Common Belief: Once created, these charts automatically update with any data change without setup.

Reality: Charts update only if data is in a dynamic range or table; otherwise, manual refresh or re-creation is needed.

Why it matters: Assuming automatic updates can cause outdated charts and wrong decisions.

Quick: Are treemap and sunburst charts equally good for very deep hierarchies? Commit to yes or no.

Common Belief: Both charts handle deep hierarchies equally well without losing clarity.

Reality: Sunburst charts handle deeper levels better visually; treemaps can become cluttered and hard to read with many levels.

Why it matters: Choosing the wrong chart for deep data can hide important details and confuse users.

Expert Zone

1

Treemap color gradients can encode a second variable, adding depth to the visualization beyond size.

2

Sunburst charts can be combined with interactive slicers in Excel to explore different hierarchy branches dynamically.

3

Excel's chart engine uses a squarified treemap algorithm to optimize rectangle shapes for better readability.

When NOT to use

Avoid treemap and sunburst charts when data is flat or has no clear hierarchy. Use bar charts or pie charts instead. For very large datasets with many small categories, consider PivotTables or Power BI visuals for better performance and clarity.

Production Patterns

Professionals use treemap and sunburst charts in dashboards to summarize sales by region and product lines. They often combine these charts with filters and slicers for interactive reports. In presentations, they customize colors and labels to highlight key insights and trends.

Connections

File System Hierarchy

Both represent nested folder structures visually.

Understanding how files and folders nest helps grasp how treemap and sunburst charts display hierarchical data.

Sunburst Galaxy Structure (Astronomy)

Sunburst charts mimic the layered rings seen in spiral galaxies.

Recognizing natural ring patterns in galaxies can deepen intuition about how sunburst charts show levels.

Nested Russian Dolls (Matryoshka)

Both show objects nested inside larger ones, revealing layers step-by-step.

This physical nesting helps visualize how treemap rectangles or sunburst rings contain subcategories.

Common Pitfalls

#1 Using flat data without hierarchy for treemap or sunburst charts.

Wrong approach:

Data:
Category | Value
A | 100
B | 200
C | 150
Insert > Hierarchy Chart > Treemap

Correct approach:

Data:
Level 1 | Level 2 | Value
A | Sub1 | 100
A | Sub2 | 50
B | Sub1 | 200
Insert > Hierarchy Chart > Treemap

Root cause:

Misunderstanding that these charts require hierarchical data paths, not just categories.

#2 Not using Excel Tables or named ranges for dynamic data, causing charts not to update.

Wrong approach:

Select fixed cell range for chart data and add new rows later without updating range.

Correct approach:

Convert data to Excel Table (Insert > Table) so chart range expands automatically with new data.

Root cause:

Lack of knowledge about dynamic ranges and how Excel links charts to data.

#3 Overloading treemap with too many small categories, making rectangles unreadable.

Wrong approach:

Include hundreds of tiny categories without grouping or filtering.

Correct approach:

Group small categories into 'Other' or filter to show only top items before charting.

Root cause:

Not considering visual clarity and chart limitations with many small data points.

Key Takeaways

Treemap and sunburst charts visualize hierarchical data by showing size and structure clearly.

Proper data layout with hierarchy levels and values is essential for these charts to work.

Treemaps use nested rectangles; sunbursts use concentric rings to represent data layers.

Customizing colors and labels improves chart readability and communication.

Knowing chart limits and dynamic data setup prevents common mistakes and keeps visuals accurate.