On the "Sales Summary" worksheet, create one chart that displays each representative's "Total Sales" as an Area chart and the "Grand Total Percentage" as a secondary axis Line chart.

Excel Combo Chart: Area + Secondary Axis Line Overall Goal The task asks you to create a combo chart - a single chart that overlays two different chart types using two different vertical axes. This is the correct approach when your two data series have different scales or units (

Manage advanced charts and tables

Question

On the "Sales Summary" worksheet, create one chart that displays each representative's "Total Sales" as an Area chart and the "Grand Total Percentage" as a secondary axis Line chart.

Explanation

Excel Combo Chart: Area + Secondary Axis Line

Overall Goal

The task asks you to create a combo chart - a single chart that overlays two different chart types using two different vertical axes. This is the correct approach when your two data series have different scales or units (e.g., dollar totals vs. percentages). Putting them on the same axis would make one series visually meaningless.

Step-by-Step Breakdown

1. Select the data on the "Sales Summary" worksheet

Select the representative names, their Total Sales values, and the Grand Total Percentage values together. You need all three columns so Excel knows what to plot and how to label it.

If skipped: Excel won't know which data to chart, or it may guess wrong.

2. Insert a chart → Choose "Combo" chart type

Go to Insert → Charts → Insert Combo Chart (the icon showing a bar and line). This is the only built-in chart type that lets you assign different chart types to different series in one step.

If you insert a regular Area or Line chart first: You can still convert it, but it's extra work and error-prone.

3. Set "Total Sales" series → Chart Type: Area

In the combo chart dialog, find the "Total Sales" series and set its chart type to Area. This fills the region under the line, emphasizing cumulative volume over time or across reps.

If skipped: Both series default to the same type, which fails the requirement.

4. Set "Grand Total Percentage" series → Chart Type: Line + check "Secondary Axis"

Set this series to Line and check the Secondary Axis checkbox. This plots the percentage on a right-side Y-axis (0%–100%), independent of the dollar-scale left axis.

If you forget Secondary Axis: The percentage values (e.g., 0.15, 0.22) will be dwarfed by the Total Sales values (e.g., $50,000) and will appear as a flat line at the bottom - effectively invisible.

5. Confirm and place the chart

Click OK/Insert. The chart is embedded on the Sales Summary sheet as required.

What Goes Wrong Without Each Step

Skipped Step	Consequence
Not selecting both series	Chart missing data
Not using Combo type	Can't assign different types per series
Wrong chart type for Total Sales	Fails exam requirement
Forgetting Secondary Axis	Percentage line is invisible/misleading

Memory Tip

Think "Two scales = Two axes, Two types":

Area fills → good for volume (Total Sales in dollars)
Line on secondary → good for ratios (percentage, 0–100%)

Whenever two series have incompatible scales, always ask: "Does this need a secondary axis?" - if one is a percentage and the other is a raw dollar amount, the answer is almost always yes.

Topics

#Chart creation#Combo charts#Secondary axis#Chart types

Community Discussion

No community discussion yet for this question.

Full MO-201 Practice