Treemap and sunburst in Excel - Real Business Scenario

Scenario Mode

👤 Your Role: You are a sales analyst at a retail company.

📋 Request: Your manager wants a visual report to understand sales distribution by product category and subcategory.

📊 Data: You have sales data with columns: Region, Category, Subcategory, and Sales Amount.

🎯 Deliverable: Create a Treemap and a Sunburst chart in Excel to show sales by Category and Subcategory.

Sample Data

Region	Category	Subcategory	Sales Amount
North	Electronics	Phones	1200
North	Electronics	Computers	1500
South	Furniture	Chairs	800
South	Furniture	Tables	700
East	Clothing	Men	900
East	Clothing	Women	1100
West	Electronics	Phones	1300
West	Furniture	Chairs	600
North	Clothing	Men	700
South	Electronics	Computers	900

1

Step 1: Organize your data by creating a summary table that shows total sales for each Category and Subcategory.

Use a PivotTable: Rows = Category, then Subcategory; Values = Sum of Sales Amount.

Expected Result

PivotTable showing total sales per Category and Subcategory, e.g., Electronics > Phones = 2500.

2

Step 2: Select the summary table created by the PivotTable.

Click inside the PivotTable and select the entire table with categories, subcategories, and sales sums.

Expected Result

The summary data is selected for chart creation.

3

Step 3: Insert a Treemap chart to visualize sales distribution.

Go to Insert tab > Hierarchy Charts > Treemap.

Expected Result

A Treemap chart appears showing sales by Category and Subcategory with size representing sales amount.

4

Step 4: Insert a Sunburst chart to visualize the same data differently.

With the summary table selected, go to Insert tab > Hierarchy Charts > Sunburst.

Expected Result

A Sunburst chart appears showing sales by Category and Subcategory in concentric rings.

5

Step 5: Add chart titles and labels for clarity.

Click on each chart title and type: 'Sales Treemap by Category' and 'Sales Sunburst by Category'.

Expected Result

Charts have clear titles explaining what they show.

Final Result

Large block

↓

Medium block

↓

Medium block

↓

Medium block

↓

Small block

↓

Small block

↓

Medium block

↓

Small block

↓

Small block

↓

Center: Electronics

↓

Ring 1: Phones, Computers

↓

Next ring: Furniture

↓

Ring 1: Chairs, Tables

↓

Outer ring: Clothing

↓

Ring 1: Men, Women

✓Electronics category has the highest total sales, especially Phones and Computers.

✓Furniture sales are moderate, with Chairs selling slightly more than Tables.

✓Clothing sales are balanced between Men and Women subcategories.

✓Treemap and Sunburst charts help quickly see which categories and subcategories contribute most to sales.

Bonus Challenge

Create a dynamic dashboard where selecting a Region filters the Treemap and Sunburst charts to show sales only for that Region.

Show Hint

Use Slicers connected to the PivotTable and charts to filter data interactively.