How to Add a Secondary Axis in Excel Charts (Easy Guide)

Excel charts allow you to do a lot of customizations that help in representing the data in the best possible way.

And one such example of customization is the ease with which you can add a secondary axis to Excel charts.

But before I get into the mechanics of adding a secondary axis, let me take a step back and explain why it’s needed.

Why Add a Secondary Axis in Excel Charts?

Let me try and explain this by using an example.

Suppose you have the following data set of Sales and Profit margin of a company for six years.

Below is the chart that I have created using this data. The blue bars represent the sales value and the red ones (the little bars next to the blue sales bars) represents the profit margin.

Do you see the problem?

What if I ask you to tell me how much change happened in profit margin in 2019 (compared with 2018).

I am sure you can’t tell by looking at the chart.

But if you look at the numbers, the profit soared by 125% in 2019.

And that’s the issue with this Excel chart.

When you plot all the data on the same axis, you lose the ability to compare data of different scales. While Sales numbers are likely to be high, profit margins are usually very low values.

And these two can’t be plotted on the same axis.

Solution – adding a secondary axis to plot the profit margin numbers.

So, we add a secondary axis to the mix and make the chart better (as shown below).

A secondary axis has been added to the right which has different scales. The lowest value is 0% and the highest is 4% (which is determined by the profit margin percentage values in your dataset).

This combination chart now allows you to see the variation in both series of data – Sales and profit margin values.

Adding Secondary Axis to Excel Charts

Adding a secondary axis is very simple in all the versions of Excel (more so in the latest ones).

In this section, I will show you the steps to add a secondary axis in different versions.

Using Recommended Charts

In Excel 2013 and higher versions (Excel 2016, 2019 and Office 365), there is a quick way to create charts using the recommended charts feature.

This feature quickly analyzes your data and show you a few options.

If you have a simple dataset (like the one we are using in this example), it’s likely that recommended charts will show you an option that already includes a second axis as a part of the chart.

Below are the steps to add a secondary axis to a chart:

1. Select the dataset.
2. Click the Insert tab.
3. In the Charts group, click the Recommended Charts option. This will open the Insert Chart dialog box.
4. Scan the charts in the left pane and select the one that has a secondary axis.
5. Click OK.

Note: You also get other chart options that you can use. Excel tries to be helpful but may not always be on point. If you don’t find the chart that you want to use, you can create it manually (covered next).

Adding the Secondary Axis Manually (2013 and above versions)

In case the ‘Recommended Charts’ feature does not work for you, there is always the option to do it manually (only takes a few clicks).

Below are the steps to add a secondary axis to the chart manually:

1. Select the data set
2. Click the Insert tab.
3. In the Charts group, click on the Insert Columns or Bar chart option.
4. Click the Clustered Column option.
5. In the resulting chart, select the profit margin bars. If these are too small to select, select any of the blue bars and hit the tab key.
6. With the Profit margin bars selected, right-click and click on ‘Format Data Series’
7. In the right-pane that opens, select the Secondary Axis option. This will add a secondary axis and give you two bars.
8. Right-click on the Profit margin bar and select ‘Change Series Chart Type’.
9. In the Change Chart Type dialog box, change the Profit Margin chart type to ‘Line with Markers’

That’s it!

This will give you a chart that has the secondary axis and the chart type of data on a secondary axis is a line chart.

You can also format the line by right-clicking and selecting the Format Data Series option. Simple things such as making the line and columns in contrasting colors can make your Excel charts professional and easy to understand.

Note that in the above example, we are adding a vertical secondary axis.

Adding the Secondary Axis Manually (Excel 2010)

In case you’re using Excel 2010, you can follow the below steps to add a secondary axis:

1. Select the data and insert the chart
2. Click the chart. This will also make visible the Chart Tools tab. This is a contextual tab and appears only when you select a chart.
3. Click the Format tab
4. In the current selection group, select the series for which you want to add a secondary axis
5. After selecting the column, click on Format selection. This will open the Format Data Series dialog box.
6. In the dialog box, select Series Options in the left pane
7. Select the Secondary Axis option
8. Close the dialog box

Removing the Secondary Axis

Removing the secondary axis is as simple as hitting the Delete key (literally).

To delete a secondary axis, click on it.

Hit the Delete Key (or right-click and click on Delete).

Note: In most cases, you’ll need to add a vertical secondary axis only. But in cases, you want a horizontal secondary axis you can use the same steps to do add it.

You May Also Like the Following Excel Charting Tutorials:  

• 10 Advanced Excel Charts
• How to Add Axis Titles in Charts in Excel?
• Excel Histogram Chart
• Pareto Chart in Excel
• Creating Step Chart in Excel
• Actual Vs Target Charts
• Bullet Chart
• Creating a Pie Chart in Excel