How To Add A Second Axis To Charts In Excel

3.png

When you're creating charts in Excel, it can be useful to show two metrics side by side which are on different scales. For example, if you wanted to show charts for sales over time and profit margin over time, you'd normally need to create them on separate charts. But it is possible to show them all on the same chart using a little configuration.

 
Showing metrics in different units side by side on the same chart can produce useful insight.

Showing metrics in different units side by side on the same chart can produce useful insight.

 

Working Example

For example, take this data set

 
Example Revenue and Profit Margin data

Example Revenue and Profit Margin data

 

If we were to create two charts, they'd look like this

Screenshot 2019-08-13 at 14.01.32.png

But if we selected both metrics into the same chart we'd end up with the following, which tries to plot a % and $ value on the same chart - it just doesn't work well at all.

 
 

Luckily we can amend this chart so that one of the metrics is plotted on a second vertical axis on the right of the chart. Let's say we want to do this to the profit margin metric.

First, click on the line on the chart and the chart properties menu will pop up on the right hand side.

 
The Format Data Series Menu (on Excel for Mac)

The Format Data Series Menu (on Excel for Mac)

 

In the ‘Series Options’ Section select ‘Secondary Axis’. The chart will now change to something like this:

 
 

It's now possible to compare these metrics side by side on the same chart.

Turning one of the data series into a bar chart can add an extra dimension to the chart, this type of mixed chart is often used in publications like The Economist.

To change the chart type, simply select the metric (Revenue in this example) and right click, go to the change chart type option on the ‘Chart Options’ toolbar. Here you can select which chart to change and choose a chart type.

Screenshot 2019-08-13 at 14.01.32.png

Here's the finished chart:

 
 

Get More Exclusive Content

Sent Directly to your inbox with Excel 3 Tip Friday. A weekly dose of Functions shortcuts and tips for getting more out of Excel.

We won't send you spam. Unsubscribe at any time. Powered by ConvertKit