You are reading the article How To Add A Secondary Axis In Excel Charts (Easy Guide) updated in October 2023 on the website Dacquyenphaidep.com. We hope that the information we have shared is helpful to you. If you find the content interesting and meaningful, please share it with your friends and continue to follow and support us for the latest updates. Suggested November 2023 How To Add A Secondary Axis In Excel Charts (Easy Guide)
Excel charts allow you to do a lot of customizations that help in representing the data in the best possible way.
And one such example of customization is the ease with which you can add a secondary axis to Excel charts.
But before I get into the mechanics of adding a secondary axis, let me take a step back and explain why it’s needed.
Let me try and explain this by using an example.
Suppose you have the following data set of Sales and Profit margin of a company for six years.
Below is the chart that I have created using this data. The blue bars represent the sales value and the red ones (the little bars next to the blue sales bars) represents the profit margin.
Do you see the problem?
What if I ask you to tell me how much change happened in profit margin in 2023 (compared with 2023).
I am sure you can’t tell by looking at the chart.
But if you look at the numbers, the profit soared by 125% in 2023.
And that’s the issue with this Excel chart.
When you plot all the data on the same axis, you lose the ability to compare data of different scales. While Sales numbers are likely to be high, profit margins are usually very low values.
And these two can’t be plotted on the same axis.
Solution – adding a secondary axis to plot the profit margin numbers.
So, we add a secondary axis to the mix and make the chart better (as shown below).
A secondary axis has been added to the right which has different scales. The lowest value is 0% and the highest is 4% (which is determined by the profit margin percentage values in your dataset).
This combination chart now allows you to see the variation in both series of data – Sales and profit margin values.
Adding a secondary axis is very simple in all the versions of Excel (more so in the latest ones).
In this section, I will show you the steps to add a secondary axis in different versions.
In Excel 2013 and higher versions (Excel 2023, 2023 and Office 365), there is a quick way to create charts using the recommended charts feature.
This feature quickly analyzes your data and show you a few options.
If you have a simple dataset (like the one we are using in this example), it’s likely that recommended charts will show you an option that already includes a second axis as a part of the chart.
Below are the steps to add a secondary axis to a chart:
Select the dataset.
Scan the charts in the left pane and select the one that has a secondary axis.
Note: You also get other chart options that you can use. Excel tries to be helpful but may not always be on point. If you don’t find the chart that you want to use, you can create it manually (covered next).
One of the things I like about the ‘Recommended charts’ feature is that it identifies the time data (such as years or months). This sometimes doesn’t happen when creating a chart manually, in which case you have to manually set the axis value.
Below are the steps to add a secondary axis to the chart manually:
Select the data set
In the resulting chart, select the profit margin bars. If these are too small to select, select any of the blue bars and hit the tab key.
In the right-pane that opens, select the Secondary Axis option. This will add a secondary axis and give you two bars.
In the Change Chart Type dialog box, change the Profit Margin chart type to ‘Line with Markers’
That’s it!
This will give you a chart that has the secondary axis and the chart type of data on a secondary axis is a line chart.
Caution: In case you have a dataset where the axis values are numbers (such as years), the column chart you create may not be what you expect. It’s likely to consider the year’s data as something that should be plotted in the chart as bars instead of the axis (the axis in such case takes the numbers 1,2,3 and so on..).
The fix here would be to delete the series where the year’s data has been plotted as columns and then manually change the axis or convert the axis values in the dataset into text.
Note that in the above example, we are adding a vertical secondary axis.
Note: When creating Excel charts, it’s always a good idea to keep in mind that these might be printed. In such cases, using light and dark colors to show contrast can help even when the printed charts are seen (which are often printed in black and white).
In case you’re using Excel 2010, you can follow the below steps to add a secondary axis:
Select the data and insert the chart
In the current selection group, select the series for which you want to add a secondary axis
In the dialog box, select Series Options in the left pane
Select the Secondary Axis option
Close the dialog box
Removing the secondary axis is as simple as hitting the Delete key (literally).
Note: In most cases, you’ll need to add a vertical secondary axis only. But in cases, you want a horizontal secondary axis you can use the same steps to do add it.
You May Also Like the Following Excel Charting Tutorials:
You're reading How To Add A Secondary Axis In Excel Charts (Easy Guide)
Update the detailed information about How To Add A Secondary Axis In Excel Charts (Easy Guide) on the Dacquyenphaidep.com website. We hope the article's content will meet your needs, and we will regularly update the information to provide you with the fastest and most accurate information. Have a great day!