A secondary axis is used to plot data series on a separate axis. In Excel, a secondary axis is useful for comparing data series measured in different units.
For this guide, I will use the Office 365 version of Excel (build 2203). However, the instructions will be similar to other versions.
Example of when to use a secondary axis
I have created an example table to visualize monthly profit margins in relation to items sold. The profit margin series is measured in percentages, whereas the Items sold series is measured in absolute values.
In a standard clustered column chart, the profit margin series is not visible when plotted on the same axis as the Items sold series. This is because both series are measured in different units, and their values differ significantly.
We will plot the Profit margin series on a secondary axis to fix this issue.
How to add a secondary axis
Using Recommended Charts (fastest way)
- Navigate to Insert > Recommended Charts.
Select the Recommended Charts option. - Excel will recommend a chart based on the selected table. For our chart, Excel automatically plots the Profit margin series on a secondary axis. Select your preferred chart type and click OK to apply changes.
Select your preferred chart type and click OK. - The recommended chart automatically plots the Profit margin series on a secondary axis.
Profit margin series is plotted on a secondary axis.
Using Format Data Series
- Select the data that will be plotted on the secondary axis. For example, we will select the Profit margin series.
Select the Profit margin series. - Right-click and select the Format Data Series.
Right-click and select Format Data Series. - From the Series Options section, select Secondary Axis. This plots the selected series on a secondary axis.
Select the Secondary axis option. - The Profit margin series is now plotted on a Secondary axis.
Profit margin series is plotted on a secondary axis. The Profit margin series is displayed on top of the Items sold series. We can fix this by changing the Profit margin series chart type.
- Select the Profit margin series from the chart—Right-click to view more options and select Change Series Chart Type.
Select Change Series Chart Type option. - In the pop-up, navigate to your selected series and open the Chart Type drop-down.
Open the Secondary Series drop-down menu. - Select the chart type that works best and click OK. For example, we selected the Line chart type.
Select a chart type for the secondary axis. Our data chart is complete with the secondary axis displayed.
Our data chart is complete.
