Treemap and sunburst in Excel - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to insert a treemap chart from data in Excel.

Excel

ActiveSheet.Shapes.AddChart2(251, [1], 100, 100, 400, 300).Select

Drag options to blanks, or click blank then click option'

AxlTreemap

BxlPie

CxlLine

DxlBarClustered

Attempts:

3 left

💡 Hint

Common Mistakes

Using a chart type like xlPie or xlLine instead of xlTreemap.

Not specifying the chart type correctly.

The code uses xlTreemap to create a treemap chart type in Excel VBA.

2fill in blank

medium

Complete the code to insert a sunburst chart from data in Excel.

Excel

ActiveSheet.Shapes.AddChart2(251, [1], 50, 50, 500, 350).Select

Drag options to blanks, or click blank then click option'

AxlScatter

BxlColumnClustered

CxlAreaStacked

DxlSunburst

Attempts:

3 left

💡 Hint

Common Mistakes

Using a chart type like xlColumnClustered or xlScatter instead of xlSunburst.

Incorrect chart type constant causes errors.

The xlSunburst constant creates a sunburst chart in Excel VBA.

3fill in blank

hard

Fix the error in the VBA code to set the chart title for a treemap chart.

Excel

With ActiveChart
  .ChartTitle.Text = [1]
End With

Drag options to blanks, or click blank then click option'

ATrue

B"Sales Data"

CSales Data

D12345

Attempts:

3 left

💡 Hint

Common Mistakes

Not using quotes around the title text.

Using a number or boolean instead of a string.

The chart title text must be a string enclosed in quotes, like "Sales Data".

4fill in blank

hard

Fill both blanks to set the chart type and add data source for a sunburst chart.

Excel

With ActiveSheet.Shapes.AddChart2(251, [1], 10, 10, 600, 400).Chart
  .SetSourceData Source:=Sheets("Data").Range([2])
End With

Drag options to blanks, or click blank then click option'

AxlSunburst

BxlPie

C"A1:C10"

D"B2:D15"

Attempts:

3 left

💡 Hint

Common Mistakes

Using wrong chart type constant.

Not quoting the range string properly.

Use xlSunburst for the chart type and specify the data range as a string like "B2:D15".

5fill in blank

hard

Fill all three blanks to create a treemap chart, set its title, and specify data range.

Excel

With ActiveSheet.Shapes.AddChart2(251, [1], 20, 20, 450, 350).Chart
  .SetSourceData Source:=Sheets("Sheet1").Range([2])
  .ChartTitle.Text = [3]
End With

Drag options to blanks, or click blank then click option'

AxlTreemap

B"A1:B20"

C"Quarterly Sales"

DxlLine

Attempts:

3 left

💡 Hint

Common Mistakes

Using wrong chart type.

Not quoting strings for range or title.

Mixing up chart types and data ranges.

Use xlTreemap for the chart type, specify the data range as a string, and set the chart title as a quoted string.

Practice

1. What is the main purpose of a treemap or sunburst chart in Excel?

easy

A. To show parts of a whole with nested groups

B. To create a simple line graph

C. To calculate sums automatically

D. To sort data alphabetically

Solution

1. Step 1: Understand chart types

   Treemap and sunburst charts visualize hierarchical data by showing parts of a whole with nested groups.

2. Step 2: Compare with other chart types

   Line graphs show trends over time, sums are calculated by formulas, and sorting is a data operation, not chart purpose.

3. Final Answer:

   To show parts of a whole with nested groups -> Option A

4. Quick Check:

   Treemap/sunburst = parts of whole [OK]

Hint: Remember: treemap shows nested parts visually [OK]

Common Mistakes:

• Confusing treemap with line or bar charts
• Thinking treemap calculates data
• Assuming treemap sorts data

2. Which Excel menu path do you use to insert a treemap or sunburst chart?

easy

A. Formulas > Function Library > Math & Trig

B. Insert > Charts > Hierarchy Chart

C. Data > Sort & Filter > Filter

D. Review > Comments > New Comment

Solution

1. Step 1: Locate chart insertion

   Treemap and sunburst charts are found under Insert tab, then Charts group, specifically Hierarchy Chart.

2. Step 2: Eliminate unrelated menus

   Data menu is for sorting/filtering, Formulas for functions, Review for comments, none for charts.

3. Final Answer:

   Insert > Charts > Hierarchy Chart -> Option B

4. Quick Check:

   Insert > Charts > Hierarchy = correct menu [OK]

Hint: Charts are always under Insert tab in Excel [OK]

Common Mistakes:

• Looking under Data or Formulas tabs
• Confusing chart types location
• Not finding Hierarchy Chart group

3. Given this data in Excel:

Category | Subcategory | Sales
Fruit    | Apple       | 100
Fruit    | Banana      | 150
Veggie   | Carrot      | 80
Veggie   | Broccoli    | 120

If you create a sunburst chart from this data, what will the inner ring represent?

medium

A. The sales for only the first row

B. Individual sales numbers for each Subcategory

C. The sum of all sales combined

D. The total sales for each Category (Fruit, Veggie)

Solution

1. Step 1: Understand sunburst chart rings

   The inner ring shows top-level groups (Categories), outer rings show subgroups (Subcategories).

2. Step 2: Apply to given data

   Categories are Fruit and Veggie, so inner ring slices represent total sales per Category.

3. Final Answer:

   The total sales for each Category (Fruit, Veggie) -> Option D

4. Quick Check:

   Sunburst inner ring = top-level groups [OK]

Hint: Inner ring = main groups, outer rings = subgroups [OK]

Common Mistakes:

• Thinking inner ring shows individual sales
• Confusing total sum with category totals
• Assuming only first row is shown

4. You tried to create a treemap chart but Excel shows an error. Which of these is the most likely cause?

medium

A. You used a formula instead of typing numbers

B. You selected the Insert tab instead of the Data tab

C. Your data has missing or blank cells in the hierarchy columns

D. You applied a filter to the data before inserting the chart

Solution

1. Step 1: Check data requirements for treemap

   Treemap requires complete hierarchy data without blanks in category columns.

2. Step 2: Analyze error causes

   Missing or blank cells break the hierarchy, causing Excel to error. Other options don't cause chart errors.

3. Final Answer:

   Your data has missing or blank cells in the hierarchy columns -> Option C

4. Quick Check:

   Complete hierarchy data needed [OK]

Hint: No blanks allowed in hierarchy columns for treemap [OK]

Common Mistakes:

• Thinking Insert tab is wrong
• Believing formulas cause chart errors
• Assuming filters block chart creation

5. You have sales data for regions and products:

Region | Product | Sales
North  | A       | 200
North  | B       | 300
South  | A       | 150
South  | B       | 350

You want to create a treemap chart that shows sales by region and product. Which steps should you follow to prepare your data and insert the chart correctly?

hard

A. Arrange data with Region and Product columns, no blanks; select all data; go to Insert > Charts > Hierarchy Chart > Treemap

B. Sort data by Sales descending; select only Sales column; Insert > Charts > Pie Chart

C. Create a pivot table first; then insert a line chart from pivot data

D. Filter data by Region; select filtered data; Insert > Charts > Bar Chart

Solution

1. Step 1: Prepare data with complete hierarchy

   Ensure Region and Product columns have no blanks and all sales data is included.

2. Step 2: Insert treemap chart

   Select all data, then use Insert > Charts > Hierarchy Chart > Treemap to visualize sales by region and product.

3. Final Answer:

   Arrange data with Region and Product columns, no blanks; select all data; go to Insert > Charts > Hierarchy Chart > Treemap -> Option A

4. Quick Check:

   Complete data + Insert > Hierarchy > Treemap = correct [OK]

Hint: Select full hierarchy data before inserting treemap [OK]

Common Mistakes:

• Selecting only sales column
• Using wrong chart type like pie or line
• Filtering data before chart insertion