Advanced Charts: Dual-Axis, Combo, Histogram, Box & Whisker, Funnel, Sunburst, Waterfall

The MO-211 exam requires you to create specialized charts and perform specific technical modifications (such as adjusting bins, mapping series to a secondary axis, or defining totals) within seconds.

Part 1: Combo Charts and Dual-Axis (Secondary Axis)

In data science and business intelligence, you often need to visualize the relationship between two variables that don't share the same unit of measure—for example, Total Revenue (measured in millions of dollars) vs. Conversion Rate (measured as a percentage). If you plot these on a standard chart, the percentage line will collapse to the bottom of the grid. Just as you might use a secondary Y-axis in matplotlib or ggplot2, Excel Expert users use Combo charts with a secondary axis to bring two disparate scales into a single, readable view.

When to Use a Secondary Axis

A dual-axis chart is the right tool when your data contains:

1. Different units: Dollars vs. percentages, or temperature vs. precipitation.
2. Different magnitudes: One series is in the thousands, the other in the millions.

By adding a secondary axis, you create a second Y-axis on the right side of the chart, allowing both data series to be scaled independently while sharing the same X-axis (usually time).

Creating a Combo Chart from Scratch

The most efficient way to build a dual-axis chart on the exam is via the Insert Chart dialog.

1. Select your data range.
2. Go to Insert > Recommended Charts > All Charts tab.
3. Select Combo at the bottom of the list.
4. For each data series, choose the Chart Type (e.g., Clustered Column for Sales, Line for Margin).
5. Check the Secondary Axis box for the series with the smaller scale or different unit.

Converting an Existing Series to the Secondary Axis

If you already have a chart and want to push one series onto a secondary axis:

1. Right-click the data series you want to move.
2. Select Format Data Series (or press Ctrl + 1).
3. In Series Options, select Secondary Axis.

Modifying and Fine-Tuning

Once the axis is created, an Expert ensures the chart isn't misleading.

A common exam task pattern: "Create a Clustered Column - Line on Secondary Axis combo chart. Set the 'Target' series as a Line on the secondary axis." Use the All Charts > Combo dialog and tick Secondary Axis for Target.

Part 2: Statistical Charts (Histogram, Pareto & Box and Whisker)

Histogram

• Create: Select data > Insert > Insert Statistic Chart > Histogram.
• Modify (Bins): Right-click the Horizontal (Category) Axis > Format Axis. Under Axis Options, set the Bin Width (the range per bar) or the Number of Bins as specified in the task.

Histogram Bin Options (Format Axis pane)

The Format Axis pane is where almost every Histogram exam tweak happens. Select the horizontal axis and open Axis Options:

Pareto

A Pareto chart is a Histogram variant designed for 80/20 analysis: it sorts the bars in descending order and overlays a cumulative percentage line on a secondary axis so you can see which few categories drive most of the total.

• Create: Select data (categories + values) > Insert > Insert Statistic Chart > Pareto (right-most icon under the Histogram group).
• Difference vs. Histogram: Pareto = pre-sorted descending bars + cumulative line. A regular Histogram keeps the bins in numeric order with no cumulative overlay.
• When to use: Defect analysis, complaint categorization, sales-by-product ranking — anytime you need to identify the "vital few" categories.

Box and Whisker

• Create: Select data > Insert > Insert Statistic Chart > Box and Whisker.
• Modify (Quartiles): Right-click the series > Format Data Series. Under Series Options, choose between Inclusive Median or Exclusive Median.

Part 3: Process & Financial Charts (Waterfall & Funnel)

Waterfall

A Waterfall chart visualizes how an initial value is affected by a series of positive and negative changes, ending in a final total. It is the standard chart for profit-and-loss bridges, inventory reconciliations, and headcount changes between two periods.

• Create: Select data (one column of labels, one column of signed values) > Insert > Waterfall.
• Set as Total: Final or subtotal bars should sit on the axis baseline rather than float as an "increase." To mark a bar as a total:
  1. Click the bar once to select the whole series.
  2. Click it again to select that single data point.
  3. Right-click > Set as Total (or double-click the point and toggle Set as total in the Format Data Point pane).
• Connector lines: The thin lines linking the tops of bars are toggled in Format Data Series > Series Options > Show connector lines. Turn them off for a cleaner look or on to emphasize the running balance.

Funnel

A Funnel chart visualizes values that progressively shrink across stages — sales pipelines, recruitment funnels, conversion stages.

• Required source layout: Funnel charts expect a single value column with category labels in the adjacent column. This is not a typical multi-series chart layout.

  Recommended structure:

  | Stage | Count | | --------------- | ----- | | Leads | 1000 | | Qualified | 720 | | Proposal Sent | 410 | | Negotiation | 180 | | Closed-Won | 95 |

• Create: Select the two-column range > Insert > Insert Waterfall, Funnel, Stock, Surface, or Radar Chart > Funnel.

• Modify: If the chart looks inverted or disjointed, the error is almost always in the source data sorting or in providing more than one value column.

Part 4: Hierarchy Charts (Sunburst)

Sunburst

• Create: Select data (must have at least two levels of categories/hierarchy) > Insert > Hierarchy Chart > Sunburst.
• Modify: Use Chart Elements (+) to toggle Data Labels or the Legend to ensure clarity in the nested rings.

Technical Modification Checklist (Exam Speed)

Official Resources:

• Available chart types in Office
• Create a Waterfall chart
• Create a Histogram
• Create a Pareto chart
• Create a Box and Whisker chart
• Create a Funnel chart
• Create a Sunburst chart
• Create a combination chart with a secondary axis