Secondary Axis In Excel

A Secondary Axis In Excel helps view the second dataset on a different axis because we cannot show two different data sets on a single axis. So this separate axis is called the “Secondary Axis” in an Excel chart.

For example, we have the data below of a student score for different subjects and his overall percentage.

We will plot a Bar Graph along with the Secondary Axis In Excel, as shown below.

The output is shown above, i.e., Marks are displayed in the form of Bars as the primary axis, and the Percentage in the form of Lines as the secondary axis.

We will see how to add a Secondary Axis in Excel with the example given below.

Here, we have two different data sets for each month. The first data set represents “Sales” numbers for the month. Next is to define the “Target Achieved %” for each month.

Both these numbers are not related to each other, so it is inappropriate to show them on a single axis.

Below is the chart example if we show both numbers on a single axis.

From the above, can you tell what the target achieved percentage is?

The problem is that the axis numbers of “Sales” are too high, and the “Sales Conversion” numbers are also less. So, we cannot show the trend with a single axis.

Since both the numbers are plotted under a single axis, we cannot tell the numbers of the smaller data set. Therefore, we cannot recognize other data along with the chart.

We can add the Secondary Axis in multiple ways. We will see each technique separately. Below is the image of the Secondary Axis chart.

As we can see on the right side, another vertical axis shows the sales conversion percentage. Now, we will see how to add a Secondary Axis in Excel.

We will consider a couple of specific examples or the following methods.

• Method 1: Simple to Add/Remove Secondary Axis in Excel.
• Method 2: Manually Add/Remove Secondary Axis to the Chart

Method #1 - Simple to Add/Remove Secondary Axis in Excel

• Once you have applied the column chart, we will get a chart like this.

• We cannot see the sales conversion percentage column, so select the chart, go to the “Design” tab, and click “Change Chart Type”.

• It will open the below dialog box.

• Choose “Recommended Chart.”

• Under this, we can see Excel has a recommended chart based on the data set. Choose the first- “Clustered Column Chart in Excel.”

• The preview shows that the sales conversion percentage comes under a Secondary Axis with a different chart type -“Line Chart in Excel.”

It looks easy, but we have one more method - manually. Let us see the method below.

Method #2 - Manually Add/Remove a Secondary Axis to the Chart

We cannot directly select the “Sales Conversion Percentage” columns in this method, so choose the chart and click “Format”.

• Under the "Format" tab, click on the drop-down list in Excel of “Current Selection” and select “Series Target Achieved %”.

• It will select the "Target Achieved %" column bars.

• Now, press Ctrl+1 to open the "FORMAT DATA SERIES" option.

• In this window, select “Secondary Axis.”

• We can get the following chart.

As we can see from the chart, it has given the “Target Achieved %” in the column bar with a Secondary Axis. But reading both the data in columns is not ideal, so we need to change the chart type to a line chart.

• Select the "Target Achieved %" column and click "Change Chart Type."

• In the below window under the combo chart in excel, select “Custom Combination”, in that, select “Target Achieved %” as “Line”, and click “OK”.

• Finally, we will have a secondary chart like the below one.

We can easily see the trend from one month to month from this chart.

• The Secondary Axis requires a different chart type from the primary axis chart. This type of chart is also called the “Combo Chart”.
• One can read Secondary Axis numbers from the right-side vertical axis.
• One can read primary axis numbers from the left side vertical axis.