How to Add a Secondary Axis in Excel? 2 Easy Ways
(Note: This guide on how to add a secondary axis in Excel is suitable for all Excel versions including Office 365)
When large and intricate data are involved, representing them using charts and graphs proves to be an effective way to help the reader understand and interpret data effectively. Excel provides a variety of graphs and charts to help represent the data.
Sometimes, more than one chart or a combination of data in a single might be needed to depict the data in the most effective way. In such a case, we can use the secondary axis to represent two various data in a single chart.
In this article, I will tell you what a secondary axis is, and how to add a secondary axis in Excel using 2 easy ways.
You’ll Learn:
Watch our video on how to add a secondary axis in Excel
Related Reads:
How to Graph a Function in Excel? 2 Easy Ways
How to Make a Line Graph in Excel? 4 Best Line Graph Examples
Bar Graph in Excel — All 4 Types Explained Easily (Excel Sheet Included)
What Is a Secondary Axis and Why Is It Important?
Before we see how to add a secondary axis in Excel. Let us learn what a secondary axis is and why it is important.
When depicting data in the form of a graph or a chart there is usually one primary x-axis and y-axis and the data is plotted accordingly.
In some cases, you might additionally need a secondary axis to understand and conclude the inference from the data in a more effective way.
The secondary axis helps you represent and visualize two or more data in the same chart. This is especially helpful when you want to compare and contrast data of different units and scales.
Consider an example where the given data represents the sales made by a particular company over a year. The data represents the sales made in particular regions east, west, north, and south. In addition to the total sales, the data also contains the profit made by each region.
Generally, if we plot a graph with the above data. Either two separate graphs have to be plotted or both the data will be plotted on the same graph along the same scale.
When we plot a graph with the above data, we can see that the region is plotted along the x-axis and the sales made are plotted along the y-axis. In addition, the profit made is also scaled in terms of the sales made.
You can see that compared to the sales, the profit made is nearly negligible in terms of numeric value. So, it would be better if an additional scale and axis to represent the profit made value.
How to Add a Secondary Axis in Excel?
Now that we know what a secondary axis is and its importance. Let us now see how to add a secondary axis in excel in 2 ways.
1. Create a Chart Using Recommended Charts
This is by far the easiest method to create a graph or chart with data from different units. Depending on your selected data, Excel offers you suggestions of charts to plot the data.
- To plot the data in the form of a graph, first, select the data.
- Navigate to Insert. Under Charts, click on Recommended Charts.
Depending on the data you selected, excel offers you a couple of recommendations. You can choose from the choices. So we choose the first recommended chart.
- Clicking on Recommended Charts opens up an Insert Chart dialog box.
Depending on the data selected, Excel offers you suggestions to choose the type of chart to help plot the data. In this case, choosing the bar graph to represent the sales made and the line chart to comparatively represent the made would be ideal.
- Click on your preferred choice of the chart and the description of the chart appears with its use cases. Click OK.
- Once you click OK. the chart gets populated in the middle of the Excel sheet.
- Once the chart is populated, you can now customize the chart to your liking.
- By clicking on the chart, you can edit the title and names of the headings, legends, and titles. Also, using the anchor points, you can move and size the chart.
- Using the Chart Design option from the main menu. You can add chart elements, change the layout, change the axis and change the type of chart.
- Using the Format option in the main menu, you can select the chart style, shape, fill, outline of the text, then align and resize the chart.
Also Reads:
How to Create a Step Chart in Excel?
How to Make a Box Plot Excel Chart? 2 Easy Ways
How to Make a Pie Chart in Excel? A Step by Step Guide
2. Create a Chart Manually
Sometimes, based on the data selected Excel might not provide the recommendation to suit your purpose, or the preferences might not show up. In such a case, you can create the appropriate chart manually and add the necessary secondary axis to it.
- First, we need to create a chart. To create a chart, select the necessary data.
- Navigate to Insert.
- Under Charts, select any of the charts you want to use to represent the data. In this case, we’ll create a clustered column chart. However, you can create any type of chart to depict your data.
- Once you click on the chart, it populates in the center of the screen.
- From the chart, you can see that along with the sales made, the profit is also plotted with the same scale. This makes the profit representation look negligible and unappealing in comparison with the sales made.
- Adding a secondary axis will help showcase the secondary data with different units on a different scale in the same chart.
- To add a secondary axis, select the data in the chart which you want to represent using a secondary axis.
- In case the data is too small to select, click on the larger data and press the Tab key.
- Right-click on the selected data. Since the data is small, right-clicking on it might be a little tricky. To overcome this, hover the mouse pointer over the secondary data. This changes the mouse pointer to a (+) shaped pointer.
- Now, right-click and then select the Format Data Series option.
In case you find this method too intricate. There is another way to arrive at the Format pane using the Format main menu.
- Click on the chart.
- Navigate to the Format main menu.
- Under the Current Selection section, click on the dropdown in the Chart Area.
- Select the required data for the secondary axis. In this case, we’ll select the Series ”Profit Made in Each Region”. This highlights the profit made data in the chart.
- Now, click on Format Selection which is right below the dropdown.
- This opens up the Format pane on the right side of the sheet. In the Format pane, under Series Options, select Secondary Axis.
You can now see the secondary axis to the right of the chart and the data being plotted accordingly. By default, the secondary data gets populated on the primary data.
- To change the secondary data into the preferred chart type, right-click on the secondary data and click on Change series Chart Type.
- This opens up a Change Chart Type dialog box. This dialog box provides a variety of options that enables you to create, customize and combine any chart independently.
- To change the chart type of the secondary axis, click on the dropdown from Series 2. This shows you different types of charts to choose from.
- In this case, we’ll select the Line with Markers chart as the profit made can be compared and contrasted with the sales made.
- Click OK.
This plots the necessary graph with the primary and secondary axes.
Suggested Reads:
How to Make a Pareto Chart Excel Dashboard? 4 Easy Steps
How to Make an Excel Bullet Chart – 2 Easy Examples
Pivot Charts in Excel Tutorial
Closing Thoughts
Just as charts help in improving the readability of the data, adding a secondary axis to the chart culls the need to create another chart and aids the reader to interpret the data easily. You can combine any type of chart to improve the readability and comprehensibility of the data.
In this article, we saw what the secondary axis is, why it is important and how to add a secondary axis in Excel using two easy ways. Recommended Charts offer you an easy way to create charts with a secondary axis, whereas manually creating them offers you a wide range of customization. You can choose any method to add a secondary axis in excel based on your preference.
Please visit our free resources center for more high-quality guides on Excel and other Microsoft Suite applications.
Ready to dive deep into Excel? Click here for advanced Excel courses with in-depth training modules.
Simon Sez IT has been teaching Excel and other business software for over ten years. For a low, monthly fee you can get access to 100+ IT training courses.
Simon Calder
Chris “Simon” Calder was working as a Project Manager in IT for one of Los Angeles’ most prestigious cultural institutions, LACMA. He taught himself to use Microsoft Project from a giant textbook and hated every moment of it. Online learning was in its infancy then, but he spotted an opportunity and made an online MS Project course - the rest, as they say, is history!