Combo Chart With a Secondary Axis in Excel

A secondary axis in Excel gives one data series its own scale on the right side of a chart. It's the fix when two measures don't fit on one scale, like revenue in thousands and margin in percent: the columns show revenue against the left axis, and a line shows margin against the right. This guide shows how to build that combo chart, how to add a secondary axis to a chart you already have, and how to keep it easy to read.

When do you need a secondary axis?#

Use a secondary axis when two series have very different sizes or units. On one shared axis, the smaller series is squashed flat. Typical pairs:

• Revenue and margin %: dollars against percentages.
• Units sold and average price: thousands of units against a price under 100.
• Website visits and conversion rate: large counts against a small percentage.
• Budget and actual spend against variance %: see the budget vs actual guide.

If both series use the same unit and a similar size, such as revenue this year and last year, don't add a second axis. A plain clustered column or bar chart compares them more honestly.

All the steps below use this monthly summary in A1:D7:

Prepare the data: calculate margin first#

Charts plot what's in the cells, so calculate the percentage series before you chart it. Margin is profit divided by revenue. In D2:

=ROUND(C2/B2,3)

For January that's 7,200 / 48,000 = 0.15, shown as 15.0% once you format the column as a percentage (Windows: Ctrl+Shift+%; Mac: Control+Shift+%). Fill it down for the other months. The same idea works for growth: =(B3-B2)/B2 gives each month's percent change in revenue. Feb is 4,000 / 48,000, about 8.3%.

If your data is a long list of transactions rather than a monthly table, summarize it first. A pivot table works, or a formula summary with UNIQUE and SUMIFS that updates by itself, as the pivot-style summary lesson shows.

How to make a combo chart with a secondary axis#

On Windows, Excel can build the classic columns-plus-line chart in one step:

1. Select A1:B7, then hold Ctrl and select D1:D7, so you have Month, Revenue and Margin. Leave out Profit for a cleaner chart.
2. Go to Insert, and in the Charts group click Insert Combo Chart.
3. Choose Clustered Column - Line on Secondary Axis.

Revenue appears as columns measured on the left axis, and Margin appears as a line measured on a new right-hand axis from 0% to 20% or so.

For more control, use Insert > Recommended Charts > All Charts > Combo. At the bottom, each series has a chart type drop-down and a Secondary Axis checkbox. Set Revenue to Clustered Column, Margin to Line, tick Secondary Axis for Margin, and click OK.

On a Mac, select the data the same way (hold ⌘ to add D1:D7), insert a clustered column chart from the Insert tab, then use Chart Design > Change Chart Type > Combo, or follow the next section.

How to add a secondary axis to an existing chart#

If you already have a chart, move one series to the secondary axis:

1. Click the series you want to move, such as the Margin columns or line.
2. Right-click and choose Format Data Series, or press Ctrl+1 (Mac: ⌘1).
3. In the pane, under Series Options, choose Secondary Axis.

The series now has its own scale on the right. If both series are still columns, they will overlap. Fix it by right-clicking the moved series, choosing Change Series Chart Type, and picking Line for it.

Tiny series can be hard to click when they sit flat at the bottom. On Windows, go to the Format tab and pick the series from the drop-down in the Current Selection group, then click Format Selection.

How to add axis titles to both axes#

With two vertical axes, readers need to know which is which. Add a title to each:

• Windows: click the chart, click the + (Chart Elements) button next to it, tick Axis Titles, then click each title to type it, such as "Revenue ($)" and "Margin (%)".
• Mac: go to Chart Design > Add Chart Element > Axis Titles and choose Primary Vertical and Secondary Vertical.

A clear chart title helps too, such as "Revenue and margin, Jan to Jun". The Excel charts basics guide covers titles, data labels and legends.

Format the secondary axis so it doesn't mislead#

Excel picks the scale of each axis on its own, and two automatic scales can tell a misleading story. A margin moving from 15% to 19% can look like a huge climb if the right axis starts at 14%. To set it yourself:

1. Double-click the secondary axis to open Format Axis.
2. Under Bounds, set Minimum to 0 and a sensible Maximum, such as 0.25 for 25%.
3. Under Number, choose Percentage with 0 decimal places.

Starting both axes at zero keeps the columns and the line honest. Also make the line's color match its axis title, or add data labels to the line, so nobody reads margin against the revenue scale. In a dashboard with several charts, keep the same colors and scales across them; the sales dashboard guide shows how.

Common secondary axis problems and fixes#

• The percentage line is flat at the bottom. It's still on the primary axis next to revenue. Move it to the secondary axis.
• The right axis shows 0.15 instead of 15%. Format the axis number as Percentage, or format the source cells as percentages first.
• Columns overlap and hide each other. Both series are columns on different axes. Change one to a line.
• You can't remove the secondary axis. Format the series and set it back to Primary Axis; the axis disappears when nothing uses it.
• The chart type doesn't fit. A pie chart, for example, can't have a secondary axis. Use columns, bars or lines.

Practise it#

A great chart starts with a clean summary and correct percentages, and those are the parts employers test. Practise building a pivot-style summary with formulas, using UNIQUE and SUMIFS to total data by category, then practise percentages and percent change so your secondary-axis series is right before it's plotted. Both lessons check each formula as you type. Start with the margin exercise on this page.