How to Add a Secondary Axis in Excel?
Secondary Axis is nothing but the two different data sets which cannot show in a single axis requires separate axis only, so this axis is called “Secondary Axis” in an excel chart.
For example, look at the below data.
Here we have two different data sets for each month. The first data set is to represent “Sales” numbers for the month and it is to represent “Target Achieved %” for each month.
Both these numbers are not related to each other so it is the wrong thing to show both the numbers in single-axis itself.
If we show both the numbers in the single-axis below is the chart example.
From the above can you tell what is the target achieved percentage is???
The problem here is the axis numbers of “Sales” is too high and numbers of “Sales Conversion” is too less, so we cannot show the trend with a single axis.
Since both the numbers are plotted under single-axis we cannot tell the numbers of smaller data set, in fact, we don’t even recognize there is another data with the chart.
For this, We can add the secondary axis in multiple ways we will see each way separately. Below is the image of the secondary axis chart.
As we can see on the right hand, there is another vertical axis showing the percentage of sales conversion. Now we will see how to add a secondary axis in excel.
Method #1: Simple to Add Secondary Axis in Excel
- Once you have applied the column chart we will get the chart like this.
- We cannot see the sales conversion percentage column, so select the chart and go to the Design tab. And from design tab click on “Change Chart Type”.
- This will open below the dialogue box.
- Choose “Recommended Chart”.
- Under this, we can see excel has a recommended chart based on the data set. Choose the first one which says “Clustered Column Chart in Excel”.
- As we can see in the preview sales conversion percentage comes under a secondary axis with different chart type i.e. “Line Chart in Excel”.
This looks easy, but we have one more way i.e. manual way, we will see that method below.
Method 2: Manually Add Secondary Axis to the Chart
In this method, we cannot select “Sales Conversion Percentage” columns directly, so select the chart and click on “Format”.
- Under the Format tab click on the drop-down list in excel of “Current Selection” and select “Series Target Achieved %”.
- This will select the “Target Achieved %” column bars.
- Now press Ctrl + 1 to open FORMAT DATA SERIES option.
- In this window select “Secondary Axis”.
- We can get the following chart.
As we can see the chart. It has given the Target Achieved % in column bar with a secondary axis, but reading both the data in columns only isn’t the ideal thing, so we need to change the chart type to the line chart.
- Select the Target Achieved % column and click on Change Chart Type.
- In the below window under combo chart in excel select Custom Combination, in that select target achieved % as “Line”. Click on OK…
- We will have a secondary chart like below one.
Now from this chart, we can easily see the trend from one month to another month.
Things to Remember
- Secondary axis always requires different chart type from the primary axis chart.
- This type of chart is also called as “Combo Chart”.
- Secondary axis numbers can be read from the right side vertical axis.
- Primary axis numbers can be read from the left side vertical axis.
This has been a guide to Secondary Axis in Excel. Here we discuss how to add a secondary axis in an excel chart along with an example and downloadable excel template. You may learn more about excel from the following articles –