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 a 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 the target achieved percentage is???
The problem here is the axis numbers of “Sales” are too high and the numbers of “Sales Conversion” is 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; 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 a chart like this.
- We cannot see the sales conversion percentage column, so select the chart and go to the Design tab. And from the 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 ExcelClustered Column Chart In ExcelIn Excel, a clustered column chart depicts data in a series of vertical columns. Though these charts are simple to create, analyzing them becomes increasingly difficult as the number of categories increases from single to multiple. .”
- As we can see in the preview, sales conversion percentage comes under a secondary axis with a different chart type i.e., “Line Chart in ExcelLine Chart In ExcelLine Graphs/Charts in Excels are visuals to track trends or show changes over a given period & they are pretty helpful for forecasting data. They may include 1 line for a single data set or multiple lines to compare different data sets. .”
It looks easy, but we have one more way i.e., manual way. We will see the method below.
Method 2: Manually Add Secondary Axis to the Chart
In this method, we cannot select “Sales Conversion Percentage” columns directly, so choose the chart and click on “Format.”
- Under the Format tab, click on the drop-down list in excelDrop-down List In ExcelA drop-down list in excel is a pre-defined list of inputs for users to select an option. In simple terms, the user's response is limited to the options presented by the drop-down list. This prevents the user from manually typing entries, reducing a garbage value in the data. of “Current Selection” and select “Series Target Achieved %.”
- This 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, 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 the combo chart in excelCombo Chart In ExcelA combination chart or combo chart in excel is a combination of two or more two different charts. We can create a combo chart from the insert menu in the chart tab. Also, to combine two charts, we must have two different data sets but one common field., select Custom Combination, in that select target achieved % as “Line.” Click on OK…
- We will have a secondary chart like the below one.
Now from this chart, we can easily see the trend from one month to another month.
Things to Remember
- The secondary axis always requires a different chart type from the primary axis chart.
- This type of chart is also called “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 –