Treemap and sunburst in Excel - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

Calculate total sales for a category in a Treemap data set

You have a table with product categories and their sales amounts. Which formula correctly sums sales for the category "Electronics" to prepare data for a Treemap chart?

Excel

Category | Sales
Electronics | 1200
Furniture | 800
Electronics | 1500
Clothing | 600
Furniture | 700

A =SUMIF(A2:A6, "Electronics", B2:B6)

B =SUM(A2:A6="Electronics", B2:B6)

C =COUNTIF(A2:A6, "Electronics")

D =SUMIFS(B2:B6, A2:A6, "Furniture")

💡 Hint

Use a function that sums values based on a condition matching the category name.

SUMIF sums values in the Sales column where the Category matches "Electronics". Option A uses SUMIF correctly. Option A is invalid syntax. Option A counts cells, not sums. Option A sums Furniture, not Electronics.

❓ Function Choice

intermediate

Choose the best function to prepare data for a Sunburst chart

You want to create a Sunburst chart showing sales by Region and then by Country. Which function helps you summarize sales by both Region and Country?

Excel

Region | Country | Sales
North America | USA | 2000
North America | Canada | 1500
Europe | Germany | 1800
Europe | France | 1600

A VLOOKUP

B SUMIFS

C COUNTIF

D LEFT

💡 Hint

You need to sum sales based on multiple criteria (Region and Country).

SUMIFS sums values based on multiple conditions, perfect for grouping sales by Region and Country. VLOOKUP retrieves data but does not sum. COUNTIF counts occurrences. LEFT extracts text.

🎯 Scenario

advanced

Identify the cause of incorrect Sunburst chart levels

You created a Sunburst chart with data showing sales by Department, Category, and Subcategory. The chart shows missing segments for some Subcategories. What is the most likely reason?

A The data table has duplicate Department names

B The Sales column contains text instead of numbers

C Some Subcategory cells are blank or contain errors

D The chart type was set to Treemap instead of Sunburst

💡 Hint

Sunburst charts require complete hierarchical data without blanks in levels.

Blank or error cells in Subcategory break the hierarchy, causing missing segments. Text in Sales affects values but not hierarchy. Chart type mismatch changes appearance but not missing segments. Duplicate Department names are allowed.

❓ data_analysis

advanced

Determine the number of segments in a Treemap chart

Given this data for a Treemap chart showing sales by Region and Product, how many segments will the Treemap display?

Excel

Region | Product | Sales
East | Chair | 500
East | Table | 700
West | Chair | 600
West | Desk | 400
West | Table | 300

A 4 segments

B 3 segments

C 2 segments

D 5 segments

💡 Hint

Count unique combinations of Region and Product for segments.

Each unique Region-Product pair creates a segment. There are 5 unique pairs: East-Chair, East-Table, West-Chair, West-Desk, West-Table.

🧠 Conceptual

expert

Understand the difference between Treemap and Sunburst charts

Which statement best describes the key difference between Treemap and Sunburst charts in Excel?

A Treemap uses nested rectangles to show hierarchy; Sunburst uses concentric rings

B Treemap requires numeric data; Sunburst only works with text data

C Treemap shows data over time; Sunburst shows data by location

D Treemap is for flat data; Sunburst cannot show hierarchical data

💡 Hint

Think about how each chart visually represents hierarchical data.

Treemap displays hierarchy with nested rectangles sized by value. Sunburst shows hierarchy with rings inside rings. Both show hierarchical data but differ in visual style.