Khan Muhammad Kawser
If you are looking for how to add a secondary axis in Excel, then you are in the right place. In Excel, while getting graphs of different kinds of data, we often face problems of showing different kinds of data individually. The solution to this is to add a secondary axis. In this article, we’ll try to discuss how to add a secondary axis in Excel.
Download Practice Workbook
Why Adding Secondary Axis in Excel Is Necessary?
Some Excel charts, almost or completely, are unable to show insights from values. Especially, when you’re using two data series with big differences like this data.
Just compare the Quantity column and Average Sales Price column. Min and Max values of Quantity column are 112 and 150. Where Min and Max values of the Average Sales Price column are 106722 and 482498.
So, there is a big difference between these two data series. In the case of showing both of these data in one Excel chart, it becomes difficult to understand the smaller values in the chart as the scale becomes large in the chart due to the large values. To solve this problem, we have to add a secondary axis to show data individually.
3 Ways to Add Secondary Axis in Excel
Excel offers a couple of ways to add a secondary axis. To show this, we have made a dataset named Sales in 2021. It has column headers for Month, Quantity and Average Sales Price.
1. Using Dual Axis Chart First
We can add a dual axis, i.e. an extra secondary axis, directly by following some simple steps. We’ll work on the dataset below.
Steps:
- Firstly, select all the data, or select a cell in the data.
- Secondly, go to the Insert tab > click on the Recommended Charts command in the Charts window or click on the little arrow icon on the bottom right corner of the window.
- Eventually, this will open the Insert Chart dialog box. In the Insert Chart dialog box, choose the All Charts
- Thirdly, choose the Combo option from the left menu. On the right side, we’ll find the data Series Names, 2 drop-down menus under the Chart Type heading, and 2 checkboxes under the Secondary Axis
- Fourthly, choose the Line with Markers chart for the Average Sales Price data series and tick the checkbox (on the right) for showing this data in the secondary We’ll also see the preview of the chart in the middle of the dialog box. If you like the preview, click on the OK button.
- Eventually, we’ll get a chart with a secondary axis like this.
Read More: How to Add Secondary Axis in Excel Pivot Chart (with Easy Steps)
2. Utilizing Format Data Series Option to the Existing Chart
We can add a secondary axis in the existing chart using the Format Data Series option. Suppose we have made a Column Chart using the dataset below like this.
We need to add a secondary axis, which is Average Sales Price. We just need to follow the steps below.
Steps:
- Firstly, right-click on any of the bars on the chart.
- Secondly, go to Format Data Series.
- Eventually, a Format Data Series window will appear.
- Thirdly, check the circle before the Secondary axis.
Alternatively, we can bring this Format Data Series by following another way. Just right-click on any place of the bars > go to Format > select Format Selection and eventually we’ll get the same Format Data Series window. We can also have this by double-clicking on any of the bars on the chart.
- Consequently, a Secondary Axis is added like this.
- And finally, we have named the Axis Title as Average Sales Price.
Read More: How to Hide Secondary Axis in Excel Without Losing Data
3. Changing Chart Type
If we have a chart that doesn’t have the option of a secondary axis, we also have a solution to create a secondary axis in this case. Suppose we have a Pie Chart below based on the following dataset.
This Pie Chart doesn’t have the option of adding a secondary axis. The solution to this is to first change the chart type and then add a secondary axis.
Steps:
- Firstly, click on any place of the chart > then go to Chart Design > select Change Chart Type.
- Eventually, a Change Chart Type window will appear.
- Secondly, go to All Charts > select Column (or you can select any kind of chart type which allows secondary axis) > choose Clustered Column chart shown in the figure > click OK.
- We’ll see the Column Chart like this with Data Labels.
- To remove this Data Label, select the chart > click on the icon of Chart Elements shown in the figure > deselect Data Labels.
- Eventually, we’ll get our chart without Data Labels.
- Thirdly, right-click on any of the bars of the chart > select Format Data Series.
- Fourthly, in the Format Data Series window, select Secondary Axis.
- Eventually, we’ll get our chart with secondary axis like this.
Read More: How to Hide Secondary Axis in Excel Without Losing Data
How to Remove Secondary Axis
After adding a secondary axis, we can remove that easily. Suppose we have the following Secondary Axis named Average Sales Price. We have the following secondary axis named Average Sales Price. We want to remove it.
- Firstly, click on the secondary axis.
- Secondly, press DELETE.
Eventually, we’ll get the chart without a secondary axis like this.
- Finally, delete the Axis Title which is Average Sales Price. And change the Axis Title to Quantity Vs Average Sales Price.
Read More: How to Hide Secondary Axis in Excel Without Losing Data
How to Add Secondary X Axis in Excel
Excel also offers easy ways to add a secondary X axis. Suppose we have a chart like this. We just want to add a secondary X axis.
Steps:
- Firstly, right-click on any of the bars of the chart > go to Format Data Series.
- Secondly, in the Format Data Series window, select Secondary Axis.
- Now, click the chart > select the icon of Chart Elements > click the Axes icon > select Secondary Horizontal.
- We’ll see that a secondary X axis is added like this. We’ll give the Chart Title as Month.
Conclusion
That’s all about today’s session. And these are the ways to add a secondary axis. in Excel. We strongly believe this article would be highly beneficial for you. Don’t forget to share your thoughts and queries in the comments section and explore our website, Exceldemy, a one-stop Excel solution provider.
