How to insert secondary axis in Excel graph

Chart created using two different types of data are very useful. They can be using in finding 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 insert a secondary vertical axis in your MS Excel Chart. Excel has an inbuilt option available for it.

Insert secondary axis in Excel graph

  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.
    graph1

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

image

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.

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.

MS Excel 2007 has been used here. But same steps applies in other versions of Excel with some modification.