Microsoft Excel Charts Advanced Chart Types Combo Waterfall Funnel Treemap Sunburst

What This Is and Why It Matters

Advanced chart types in data visualization are powerful tools for communicating complex information. These charts help professionals and analysts to effectively present data, identify trends, and make informed decisions. On the exam, combo, waterfall, funnel, treemap, and sunburst charts are crucial for demonstrating data analysis and visualization skills. If you struggle with these charts, you may miss critical insights, leading to poor decision-making and potentially costly mistakes.

Core Knowledge (What You Must Internalize)

• Combo Chart: A combination of two or more chart types, used to display different data series.
  • Why this matters: Combo charts help to present multiple data points in a single visualization, making it easier to compare and contrast different data series.
• Waterfall Chart: A chart that shows how an initial value is affected by a series of positive or negative values.
  • Why this matters: Waterfall charts help to visualize the cumulative effect of positive or negative values, making it easier to understand changes in data over time.
• Funnel Chart: A chart that shows the stages of a process, with the initial value at the top and the final value at the bottom.
  • Why this matters: Funnel charts help to visualize the flow of data through a process, making it easier to identify bottlenecks and areas for improvement.
• Treemap: A chart that shows hierarchical data as a set of nested rectangles.
  • Why this matters: Treemaps help to visualize complex hierarchical data, making it easier to understand relationships between different data points.
• Sunburst Chart: A chart that shows hierarchical data as a set of concentric circles.
  • Why this matters: Sunburst charts help to visualize complex hierarchical data, making it easier to understand relationships between different data points.

Step-by-Step Deep Dive

Combo Chart

1. Create a combo chart: Select the data series you want to display and choose the chart type (e.g., line, column, or area).
2. Add multiple data series: Select the additional data series you want to display and add them to the chart.
3. Customize the chart: Adjust the colors, labels, and other settings to make the chart easy to read and understand.
   • ⚠️: Avoid using too many data series, as this can make the chart cluttered and difficult to read.

Waterfall Chart

1. Create a waterfall chart: Select the initial value and the series of positive or negative values that affect it.
2. Add the values: Enter the values for each step in the process, making sure to include both positive and negative values.
3. Customize the chart: Adjust the colors, labels, and other settings to make the chart easy to read and understand.
   • ⚠️: Avoid using too many steps, as this can make the chart cluttered and difficult to read.

Funnel Chart

1. Create a funnel chart: Select the stages of the process you want to display.
2. Add the values: Enter the values for each stage, making sure to include both positive and negative values.
3. Customize the chart: Adjust the colors, labels, and other settings to make the chart easy to read and understand.
   • ⚠️: Avoid using too many stages, as this can make the chart cluttered and difficult to read.

Treemap

1. Create a treemap: Select the hierarchical data you want to display.
2. Add the values: Enter the values for each level of the hierarchy, making sure to include both positive and negative values.
3. Customize the chart: Adjust the colors, labels, and other settings to make the chart easy to read and understand.
   • ⚠️: Avoid using too many levels, as this can make the chart cluttered and difficult to read.

Sunburst Chart

1. Create a sunburst chart: Select the hierarchical data you want to display.
2. Add the values: Enter the values for each level of the hierarchy, making sure to include both positive and negative values.
3. Customize the chart: Adjust the colors, labels, and other settings to make the chart easy to read and understand.
   • ⚠️: Avoid using too many levels, as this can make the chart cluttered and difficult to read.

How Experts Think About This Topic

Experts think of advanced chart types as a way to tell a story with data. They consider the audience, the message they want to convey, and the type of data they are working with when choosing a chart type. Instead of memorizing formulas and settings, experts focus on understanding the underlying principles of each chart type and how to apply them effectively.

Common Mistakes (Even Smart People Make)

• The mistake: Using too many data series in a combo chart.
  • Why it's wrong: This can make the chart cluttered and difficult to read.
  • How to avoid: Use a maximum of 3-4 data series and use different colors and labels to distinguish between them.
  • Exam trap: Test writers may use a combo chart with too many data series to see if you can identify the key insights.
• The mistake: Using a waterfall chart with too many steps.
  • Why it's wrong: This can make the chart cluttered and difficult to read.
  • How to avoid: Use a maximum of 3-4 steps and use different colors and labels to distinguish between them.
  • Exam trap: Test writers may use a waterfall chart with too many steps to see if you can identify the key insights.
• The mistake: Using a funnel chart with too many stages.
  • Why it's wrong: This can make the chart cluttered and difficult to read.
  • How to avoid: Use a maximum of 3-4 stages and use different colors and labels to distinguish between them.
  • Exam trap: Test writers may use a funnel chart with too many stages to see if you can identify the key insights.
• The mistake: Using a treemap or sunburst chart with too many levels.
  • Why it's wrong: This can make the chart cluttered and difficult to read.
  • How to avoid: Use a maximum of 3-4 levels and use different colors and labels to distinguish between them.
  • Exam trap: Test writers may use a treemap or sunburst chart with too many levels to see if you can identify the key insights.

Practice with Real Scenarios

Scenario 1

You are a marketing analyst and you want to display the sales data for a new product. You have three different data series: sales by region, sales by product category, and sales by month. How would you create a combo chart to display this data?

Question: What type of combo chart would you create and how would you customize it?

Solution:

1. Create a combo chart with three data series: sales by region, sales by product category, and sales by month.
2. Use different colors and labels to distinguish between each data series.
3. Adjust the chart settings to make it easy to read and understand.

Answer: A combo chart with three data series, using different colors and labels to distinguish between each series.

Why it works: This type of combo chart allows you to display multiple data series in a single visualization, making it easier to compare and contrast different data points.

Scenario 2

You are a financial analyst and you want to display the changes in a company's revenue over time. You have a series of positive and negative values that affect the initial value. How would you create a waterfall chart to display this data?

Question: What type of waterfall chart would you create and how would you customize it?

Solution:

1. Create a waterfall chart with the initial value and the series of positive and negative values.
2. Use different colors and labels to distinguish between each step in the process.
3. Adjust the chart settings to make it easy to read and understand.

Answer: A waterfall chart with the initial value and the series of positive and negative values.

Why it works: This type of waterfall chart allows you to visualize the cumulative effect of positive and negative values, making it easier to understand changes in data over time.

Quick Reference Card

• Core rule: Use advanced chart types to tell a story with data and make it easy to read and understand.
• Key formula: None
• Three most critical facts:
  • Combo charts are used to display multiple data series.
  • Waterfall charts are used to visualize the cumulative effect of positive and negative values.
  • Funnel charts are used to visualize the flow of data through a process.
• One dangerous pitfall: Avoid using too many data series, steps, or levels in a chart, as this can make it cluttered and difficult to read.
• One mnemonic: "Combo charts are like a recipe, with multiple ingredients and a dash of creativity."

If You're Stuck (Exam or Real Life)

• What to check first: Make sure you understand the type of data you are working with and the message you want to convey.
• How to reason from first principles: Consider the underlying principles of each chart type and how to apply them effectively.
• When to use estimation: Use estimation when you are unsure of the exact values, but still want to make an educated guess.
• Where to find the answer (without cheating): Use online resources, such as tutorials and forums, to find the answer and learn from others.

Related Topics

• Scatter plots: Scatter plots are used to visualize the relationship between two variables.
• Bar charts: Bar charts are used to compare categorical data.
• Pie charts: Pie charts are used to display proportional data.

These topics are related to advanced chart types because they all involve visualizing data in different ways. By understanding these topics, you can become a more effective data analyst and communicator.