Charts in Excel give you a variety of ways to visualize your data. Charts that use an X and Y axis layout give you the ability to display a range of data, which allows you to compare two different things, but those things usually have the same unit of measurement. In this article, we will show you how adding a secondary axis to Excel allows you to visualize different things in a single chart.
When should you use a secondary axis in Excel
As mentioned earlier, secondary axes are used to compare two things that don’t have the same unit of measure.
Consider the example above where we look at a company’s revenue versus its expenses for the past five years. Both are measured in dollars so we can draw a line graph showing these two in the same view to determine the relationship.
As you can see in the screenshot above, the y-axis on the left shows USD units, but what if you want to see if there’s a similar trend in costs and employees? The unit for employees is number of people, not dollar amount, so you can’t use the existing y-axis. Here you need to add a second axis so that your reader can clearly understand the meaning of the numbers.
A second reason is that the two series do not contain data of the same magnitude. Take the company’s sales in relation to employees, for example. The chart above shows that taken together they don’t tell much because the number of employees is so small that it’s impossible to tell what’s going on.
Instead, you can add a second axis that has both its own units and scale so you can really compare the two.
These instructions work in Excel on Microsoft 365, Excel 2019, Excel 2016, and Excel 2013.
- First, select the row (or columns, etc.) associated with the second data series.
- When you select an item in a chart, the Chart Tools tab appears on the ribbon.
- Choose format Tab.
- On the far left, the Current Selection field should already show the series you have selected. In this example it is the series “Employees”.
- Choose size selection.
- In the right pane, under Recurrence, select Options secondary axis.
- Once added, this second axis can be adjusted just like the main axis. You can change the alignment or direction of the text, give it a unique axis label, or change the number format.
- Now look at your diagram. The secondary axis is shown on the right, and Excel even assumes some default estimates for scaling. Compared to the first version of this chart, the addition of a second axis makes it much easier to compare trends.