Charts are an excellent way of visualizing data.
Whether you want to show a trend or a comparison between multiple values, charts are one of the best ways to represent data visually.
Microsoft Excel makes it quite easy to create charts from a given dataset. There are also many options available for customizing the chart to your liking.
In some cases, we may require to plot multiple data series on a single chart. This helps to display full information in a single place.
Why You May Need to Add a Secondary Axis to Your Charts?
Take a look at the following dataset, which shows the statistics for products sold by different company representatives.
The first column shows the name of the representative. The second column shows the no. of units sold by each representative. The third column shows the total sale in dollars made by each representative.
It may be helpful to display both the Units Sold and the Total Sales on the same chart so that more information may be gathered at a glance.
However, displaying both data on a single axis may not correctly display the Units Sold.
This is because the total sale is in the thousands whereas the units sold are less than a hundred.
To correctly display the trend for the units sold, it needs to be shown on a secondary axis with a different scale.
So in this tutorial, I will show you how to create a secondary axis in an excel chart using different methods.
Method 1: Add Secondary Axis by Using the Recommended Charts Option
In this method, we will look at the Recommended Charts option available in excel.
In this option, Excel gives a set of recommended charts based on the data that you have selected. As an example, we will use the following dataset that we have seen earlier.
We will plot the Total Sale on the primary axis and the Units Sold on the secondary axis.
- Select the entire dataset as shown. (From cell A1 to cell C9, including column headings)
- Go to the Insert tab.
- Under the Insert tab, click on the Recommended Charts option.
- The Recommended Charts window will open.
- From the tabs on the top, click on the All Charts tab.
- The following panel will appear.
- From the options on the left, click on Combo.
- The Combo Options panel will appear as shown.
- Select the Custom Combination option from the top.
- Check the Secondary Axis option against the Units Sold series.
- Click on OK.
- You will see that the chart has been created as shown.
You can see that the Total Sale is shown on the primary axis on the left whereas the Units Sold are shown on the secondary axis on the right.
Also read: How to Add Axis Titles in Excel?
Method 2: Add Secondary Axis by Manually Setting a Series on a Secondary Axis
In this method, we will see how to move a data series on a secondary axis manually in a chart.
As an example, we will use the same dataset as we did in the previous method to plot the no. of units sold on one axis and the total sale on a secondary axis.
- Select cell A2. (This is the first entry of the dataset, excluding column headings)
- Go to the Insert tab.
- Under the Insert tab, click on the Insert Column or Bar Chart option as shown.
- The following menu will appear.
- Under the 2-D Column section, selects the Clustered Column option.
- The chart will be inserted as shown.
- Right-Click on any of the visible data bars. The context menu will appear as shown.
- From the context menu, select the Format Data Series option.
- The Format Data Series panel will appear on the right as shown.
- From the panel, select the Series Option dropdown.
- The following menu will appear.
- Select the Units Sold option as shown.
- This will highlight the Units Sold series on the chart.
As you can see, the data value for the no. of units sold is so small that it cannot be seen visually on the primary axis scale on the left.
- Under the Series option section, select the Secondary Axis option.
- This will add a secondary axis on the right and the Units Sold bars will appear. (They are shown in blue).
The bars for the Units Sold are overlapping with the bars of the Total Sale. Let’s fix that so that the Units Sold are shown as bars whereas the Total Sale is displayed as a Line as shown in the end result of the previous method.
- Go to the Design Tab on the ribbon bar.
- Under the Design tab, click on the Change Chart Type Option.
- The Change Chart Type window will appear.
- Click on the Drop Down menu against the Total Sale series (which is the one that we want to show in the secondary axis as a line chart).
- From the options that appear, select the Line chart option.
- Click on OK.
- The end result will look like the one below.
You can now format this chart to your liking.
As in the previous method, you can see that the Units Sold are shown on the secondary axis on the right, whereas the Total Sale is shown on the primary axis on the left.
So in this tutorial, we have seen how to plot a data series on a secondary axis.
Method 1 is a quick and automatic method that recommends a chart based on the selected dataset. You will find yourself using this method most of the time (as it’s easier and faster).
Method 2 is a manual method. You may use it if, in some cases, the recommended charts option does not give you the secondary axis option you are looking for.
Other Excel articles you may also like:
