How to Add Secondary Axis in Excel Charts?

Charts are an excellent way of visualizing data.

Whether you want to show a trend or a comparison between multiple values, charts are one of the best ways to represent data visually. 

Microsoft Excel makes it quite easy to create charts from a given dataset. There are also many options available for customizing the chart to your liking.

In some cases, we may require to plot multiple data series on a single chart. This helps to display full information in a single place. 

Why You May Need to Add a Secondary Axis to Your Charts?

Take a look at the following dataset, which shows the statistics for products sold by different company representatives. 

data set for which the chart needs to be made

The first column shows the name of the representative. The second column shows the no. of units sold by each representative. The third column shows the total sale in dollars made by each representative.

It may be helpful to display both the Units Sold and the Total Sales on the same chart so that more information may be gathered at a glance. 

However, displaying both data on a single axis may not correctly display the Units Sold. 

This is because the total sale is in the thousands whereas the units sold are less than a hundred. 

To correctly display the trend for the units sold, it needs to be shown on a secondary axis with a different scale.

So in this tutorial, I will show you how to create a secondary axis in an excel chart using different methods.

Method 1: Add Secondary Axis by Using the Recommended Charts Option

In this method, we will look at the Recommended Charts option available in excel. 

In this option, Excel gives a set of recommended charts based on the data that you have selected. As an example, we will use the following dataset that we have seen earlier.

data set for which the chart needs to be made

We will plot the Total Sale on the primary axis and the Units Sold on the secondary axis.

  1. Select the entire dataset as shown. (From cell A1 to cell C9, including column headings)
select the data set
  1. Go to the Insert tab.
click the insert tab
  1. Under the Insert tab, click on the Recommended Charts option.
Click on Recommended charts
  1. The Recommended Charts window will open.
  1. From the tabs on the top, click on the All Charts tab.
  1. The following panel will appear.
all charts appear
  1. From the options on the left, click on Combo.
select the combo option in the left pane
  1. The Combo Options panel will appear as shown.
  1. Select the Custom Combination option from the top.
click on the custom combination option
  1.  Check the Secondary Axis option against the Units Sold series.
check the secondary axis option
  1.  Click on OK.
click OK
  1.  You will see that the chart has been created as shown.
chart with secondary axis has been created

You can see that the Total Sale is shown on the primary axis on the left whereas the Units Sold are shown on the secondary axis on the right.

Method 2: Add Secondary Axis by Manually Setting a Series on a Secondary Axis

In this method, we will see how to move a data series on a secondary axis manually in a chart.

As an example, we will use the same dataset as we did in the previous method to plot the no. of units sold on one axis and the total sale on a secondary axis.

  1. Select cell A2. (This is the first entry of the dataset, excluding column headings)
select the first entry in the data set
  1. Go to the Insert tab.
click the insert tab
  1. Under the Insert tab, click on the Insert Column or Bar Chart option as shown.
click on the insert column or bar chart option
  1. The following menu will appear.
chart menu appears
  1. Under the 2-D Column section, selects the Clustered Column option.
select the 2D column chart option
  1. The chart will be inserted as shown.
2D column chart is inserted
  1. Right-Click on any of the visible data bars. The context menu will appear as shown.
right click on any of the visible data bars
  1. From the context menu, select the Format Data Series option.
select the format data series option
  1. The Format Data Series panel will appear on the right as shown.
format data series pane appears
  1.  From the panel, select the Series Option dropdown.
select the series option
  1.  The following menu will appear.
series option menu appears
  1.  Select the Units Sold option as shown. 
select the unit sold option
  1.  This will highlight the Units Sold series on the chart.
units sold series is highlighted in the chart

As you can see, the data value for the no. of units sold is so small that it cannot be seen visually on the primary axis scale on the left.

  1.  Under the Series option section, select the Secondary Axis option.
select the secondary axis option
  1.  This will add a secondary axis on the right and the Units Sold bars will appear. (They are shown in blue).
secondary axis appears in the chart

The bars for the Units Sold are overlapping with the bars of the Total Sale. Let’s fix that so that the Units Sold are shown as bars whereas the Total Sale is displayed as a Line as shown in the end result of the previous method.

  1.  Go to the Design Tab on the ribbon bar.
click the design tab
  1.  Under the Design tab, click on the Change Chart Type Option.
click on the change chart type option
  1.  The Change Chart Type window will appear.
change chart type window
  1.  Click on the Drop Down menu against the Total Sale series (which is the one that we want to show in the secondary axis as a line chart).
select the dropdown for the series for which you want to create the secondary axis
  1.  From the options that appear, select the Line chart option. 
select the line chart option
  1.  Click on OK.
click OK
  1.  The end result will look like the one below.
secondary axis is inserted in bar and line combo chart

You can now format this chart to your liking.

As in the previous method, you can see that the Units Sold are shown on the secondary axis on the right, whereas the Total Sale is shown on the primary axis on the left.

So in this tutorial, we have seen how to plot a data series on a secondary axis.

Method 1 is a quick and automatic method that recommends a chart based on the selected dataset. You will find yourself using this method most of the time (as it’s easier and faster).

Method 2 is a manual method. You may use it if, in some cases, the recommended charts option does not give you the secondary axis option you are looking for.

Other Excel articles you may also like: