How to add secondary axis in Excel chart – Easy Guide 2021

Chart created using two different types (example percentage and number or Very Large number vs small number) of data are used in several occasions. They can be used in finding out trend between those two data set. Usually whenever you create a chart in excel, all data will come on single axis. But using two different data set on single axis will result in a excel chart which looks very odd.
In such a situation out of requirement you might want to add secondary axis in Excel graph your MS Excel Chart. Excel has an inbuilt option available for it.

WHY IS SECONDARY AXIS NEEDED IN EXCEL

As already mentioned when two different types of data sets are being compared in Excel, it may be number vs number or number vs percentage. When value of these data are far apart in terms of value when being compared. Below are two examples –

1) Compare Sales vs Profit Percentage

add secondary axis in Excel

2) No. of days a product was sold in year vs percentage of demand.

add secondary axis in Excel

In Both of Above Case Second data set of Percentage is not Visible. What if you want to get trend from this chart? You wont be able to conclude anything.

Now both Chart if had used Secondary Axis will look like

In Both of Above charts you can easily see both data sets as well se trends clearly.

ADD SECONDARY AXIS IN EXCEL CHART – EXCEL 2013, 2019, OFFICE 365

From office 2013 onwards Excel Charts have option of recommended charts, We will make use of this option for newer version of Excel. Here below manual method of inserting secondary chart is also given for older versions of Excel.

Steps to Add Secondary Axis in Excel chart

1. Create two rows for data you want to use for making chart. Our series are marks obtained by three students vs percentage.

2. Select range of your data. Click on insert and from charts option select Recommended Chart option. You will presented a Chart which is already using Secondary Axis.

3. Now Click on OK and Your chart is ready.

MANUAL METHOD TO ADD SECONDARY AXIS IN EXCEL CHART – VERSIONS OLDER THAN OFFICE 2013

In case more you have more than one series for transfer to secondary axis, follow same steps for each series individually.

1. Create two rows for data you want to use for making chart. Our series are marks obtained by three students vs percentage.

2. Select range of your data. Click on insert and from charts option select bar or line chart option. We have selected bar.

3. Now on your bar chart select series you want to move to secondary axis. Right click on it and select Format Data Series. In this window select Secondary axis under Series Options.

Format Data Series

After moving bar to secondary axis, you will find bars are overlapped. Now if you don’t want your bars to overlap, simply go to step one. Now insert a blank row without data between two and select data for bar chart again with blank rows. Your bars will move a bit. Although they are not perfectly separated but they are not overlapping.

Follow our video guide to learn about how to get rid of this overlapping issue. – https://youtu.be/Jz9Ny_akY3k

Another way out is select one of the bars from chart and change it line chart (refer this link for detailed instruction for changing chart type). This way there will be no overlapping and you don’t need to insert any blank row in your data.