How to Work with Advanced Chart Types in Excel (Combo, Waterfall, etc.)

Introduction

Charts help people understand numbers quickly. Most users know column and line charts, but Excel includes many advanced chart types that can explain change, distribution, contribution and comparisons better. In this guide, you’ll learn when to use Combo, Waterfall, Pareto, Histogram, Box & Whisker, Funnel, Treemap, Sunburst, Radar, and Stock charts. You’ll also get simple steps to build them, plus formatting tips so your message is clear.

Everything here works in modern versions of Excel (Microsoft 365 and recent perpetual versions). We’ll keep the explanations plain and practical.

A quick framework: pick the right chart for the job

• Change over time → Line, Column, Combo, Stock
• Contributions to a total → Stacked Column/Bar, Waterfall, Treemap/Sunburst
• Distribution/variation → Histogram, Box & Whisker
• Ranking & cumulative impact → Pareto (sorted bars + running total line)
• Stages in a process → Funnel
• Circular relationships or categories with many sub-items → Sunburst
• Profile across several categories → Radar

Keep the audience and the single key message in mind. If a chart needs two sentences to explain, try a simpler type.

1) Combo charts (two stories, one visual)

Best for: Showing two related measures together (e.g., Revenue as columns and Margin % as a line), or different scales (units vs price).

Build it

1. Select your data (e.g., Date, Revenue, Margin %).
2. Insert > Insert Combo Chart (or insert a column chart, then Change Chart Type > Combo).
3. Set one series to Column and the other to Line.
4. Tick Secondary Axis for the percentage if the scales are very different.

Make it clear

• Add axis titles (“Revenue (£)”, “Margin (%)”).
• Keep to two series if possible.
• Avoid dual axes unless units differ. If you must use two axes, label them clearly and choose contrasting colours or a dashed line for the secondary series.

Common use: Actual vs Target (columns for Actual, thin line for Target).

2) Waterfall charts (bridge from start to finish)

Best for: Explaining how a value moves from Start to End through increases and decreases. Great for profit bridges, budget reconciliations, and variance analysis.

Build it

1. Prepare a table with Start, positive/negative steps, and End.
2. Insert → Waterfall or Stock → Waterfall.
3. In the chart, right-click the Start and End columns → Set as Total so they anchor the bridge.

Make it clear

• Colour code: green (increase), red (decrease), grey (totals) — or use your brand colours with the same logic.
• Sort steps logically (e.g., revenue components, cost buckets).
• Show data labels for steps and the final total.

Tip: If you track monthly changes, consider a waterfall per month or a single waterfall with grouped steps (Revenue, COGS, Opex).

3) Pareto chart (80/20 view)

A Pareto combines sorted bars (descending) with a cumulative % line to show the few categories that create most of the effect (e.g., top defect types causing 80% of returns).

Build it

1. Make a table of Category and Count.
2. Sort Count descending.
3. Add a Cumulative % column: cumulative sum / grand total.
4. Insert a Combo: Bars for Count, Line (on Secondary Axis) for Cumulative %.
5. Set the secondary axis to 0–100% and format as percent.

Make it clear

• Add a dashed line or marker at 80% to show the “vital few”.
• Label the top bars and the point where the cumulative line crosses 80%.

4) Histogram (distribution)

Best for: Seeing how values spread across bins (ranges). Ideal for order values, lead times, or exam scores.

Build it

1. Put your numbers in a single column.
2. Select them → Insert > Statistical Charts > Histogram.
3. Adjust Bin width or Number of bins (right-click the horizontal axis → Format Axis).

Make it clear

• Choose bins that users recognise (e.g., 0–5 days, 6–10 days).
• Show a vertical line for a target using a secondary axis or simply a shape.
• Keep labels short; users mostly care about the shape of the distribution.

5) Box & Whisker (variation at a glance)

Best for: Comparing spread across groups—shows median, quartiles, and outliers. Great for quality metrics, response times, or service level comparisons.

Build it

1. Arrange data with categories in columns or rows.
2. Select the range → Insert > Statistical Charts > Box & Whisker.
3. Excel creates a box for each group.

Make it clear

• Add data labels for median if comparisons matter.
• Remove fill patterns; use simple colour with a clear border.
• If outliers distract, consider toggling Show outlier options—or explain what they mean.

6) Funnel (stage drop-off)

Best for: Showing how numbers fall through stages (leads → qualified → proposals → wins), or process steps (submitted → approved → completed).

Build it

1. Arrange Stage (top to bottom) with Value.
2. Insert > Funnel.
3. Excel sorts from largest to smallest by default. If you need your own order (Lead, Qualify…), sort the source data first.

Make it clear

• Display percent of previous stage as a separate column in your table, then show it with labels or a companion chart.
• Keep colours neutral; the shape already suggests flow.
• Limit to 5–7 stages.

7) Treemap and Sunburst (parts of a whole with hierarchy)

Treemap shows rectangles sized by value; Sunburst shows rings for each level of hierarchy.

Best for: When you have categories and sub-categories and want to show contribution quickly (e.g., product family → product).

Build it

1. Prepare hierarchical data: Level1, Level2, Value.
2. Insert > Treemap or Insert > Sunburst.
3. For treemap, large rectangles are top contributors; for sunburst, outer rings represent deeper levels.

Make it clear

• Use labels on the largest blocks; avoid tiny labels.
• Use one palette with shade variations within a family.
• If you need exact comparisons, consider a bar chart instead; treemap is for scanning, not precision.

8) Radar (profiles across categories)

Best for: Showing a profile across several categories (e.g., skills, satisfaction dimensions). The shape makes “strengths vs gaps” easy to spot.

Build it

1. Put categories in the first column and series across the top row.
2. Insert > Other Charts > Radar (with or without markers).
3. Keep to 2–3 series max to avoid clutter.

Make it clear

• Start axis at 0 and keep scales consistent.
• Use markers sparingly; choose distinct line styles/colours.
• If people need exact values, a simple table may be better.

9) Stock charts (open-high-low-close, volume)

Best for: Financial OHLC data over time. Also useful to show ranges (e.g., min/median/max temperature).

Build it

1. Arrange columns as Date, Open, High, Low, Close (and Volume if needed).
2. Insert > Waterfall or Stock > Open-High-Low-Close (or Volume-OHLC).
3. Ensure dates are recognised as dates.

Make it clear

• Use candlestick colours consistently (e.g., up = green, down = red).
• Add moving average as a separate line if you want trend context.

10) Build a Pareto the quick way (step-by-step mini-tutorial)

Say you have complaint types and counts.

Data:

TypeCountDelivery54Quality31Billing15Website7Other3

Steps:

1. Sort Count largest to smallest.
2. Add a Cumulative column: running total of Count.
3. Add Cumulative %: Cumulative / SUM(Count).
4. Insert a Combo: Clustered Column for Count, Line for Cumulative % on Secondary Axis (0–100%).
5. Add a constant line at 80% (use a shape line on the chart area if you prefer).

Result: You instantly see the few categories driving most complaints.

11) Formatting that makes advanced charts easy to read

• Titles: State the insight, not just the data.
  Weak: “Revenue by Region” → Strong: “Revenue by Region – North leads by 22%”.
• Labels: Use data labels only where they help; format as K/M; 0–1 decimal places.
• Colours: Default to brand + greys. Use accent for the key series; keep others muted.
• Gridlines: Light or off; rely on labels and white space.
• Legends: Top or right; remove if you label directly.
• Sort order: For ranked charts (Pareto, bar), sort descending.
• Zero baseline: For bars/columns, keep baseline at 0; for rate of change, line charts are safer.
• Secondary axis: Only when units differ; label clearly.

12) Dynamic charts: simple, reliable techniques

• Named ranges with INDEX to auto-expand.
• Tables (Insert > Table) so charts grow as you add rows.
• Slicers with PivotCharts to interactively filter.
• For monthly updates, keep the same structure and paste new rows—your charts update automatically.

13) Common problems (and quick fixes)

14) Practical examples you can reuse

• Revenue vs Margin % (Combo): Columns for Revenue, line for Margin %, secondary axis for %.
• Budget Bridge (Waterfall): Start with Prior Year, add deltas (Price, Volume, Mix), end at Current Year.
• Top Defects (Pareto): Counts sorted + cumulative line; focus on the first few bars.
• Lead Time (Histogram): Bins 0–5, 6–10, 11–15…; mark median with a line.
• Service Levels (Box & Whisker): One box per site/team; label medians.
• Sales Pipeline (Funnel): Lead → Qualify → Proposal → Win; display % drop at each step.
• Category Contribution (Treemap): Product family rectangles, product tiles inside.

15) Best-practice checklist (copy/paste)

• ✅ Pick the chart type that matches your message.
• ✅ Keep to 1–2 colours + greys; use accent for the key series.
• ✅ Titles tell the point; labels show units; gridlines are light.
• ✅ Sort where it helps (Pareto, bars).
• ✅ Use secondary axis only for different units.
• ✅ Use Tables/Named ranges for dynamic charts.
• ✅ Test at 125% zoom and with colour-blind palettes.
• ✅ If it takes too long to explain, simplify.

Conclusion

Advanced chart types help you tell better data stories. Combo charts show two related measures clearly. Waterfall charts explain how you move from start to finish. Pareto charts reveal the vital few categories. Histogram and Box & Whisker expose distribution and variation. Funnel shows stage drop-off, while Treemap/Sunburst help people scan contributions in a hierarchy. With a few formatting rules and the right chart for the job, your reports will be easier to read and much more persuasive.

If you’d like hands-on practice with expert coaching, join the relevant courses:

• Excel Introduction – Chart basics, clean formatting, and quick wins.
• Excel Intermediate – Combo, dual axes, dynamic ranges, and PivotCharts.
• Excel Advanced – Waterfall, Pareto, Histogram, Box & Whisker; advanced chart formatting.
• Excel Power User – Mastering Complex Functions and Data Models – Model-driven visuals, automation, and dashboard design patterns.

‍