Ever had to plot two different, but related relationships on one graph?
For example, average temperature and rainfall are very closely related and often influence one another. When plotted together, they’ll provide a insightful snapshot of the week’s weather on a single graph. In situations like this, it’s really helpful to add a secondary axis to the graph.
How to Add a Secondary Vertical Axis
As a practical example, let’s plot a graph of the daily sales at Kyle’s t-shirt company. The data is presented below.
There are two columns of data to plot – number of shirts sold per day, and percentage of ladies shirts sold. Having both metrics on the same graph would provide an insightful visual, so let’s give it a try!
Step 1: Plot the Data
To plot the data, select the three columns and choose a chart type in the Chart section of the Insert tab. For this example, we’ll select the 2D Column Chart option.
As expected, Excel plots both metrics on one graph, and it’s immediately obvious that we’re running into some problems. Since percentages are merely values between 0 and 1, they are dwarfed by the larger values representing the number of shirts sold.
Step 2: Activate the Secondary Axis
Thankfully, adding a secondary vertical axis in Excel is a really straightforward process. Double-click on any of the columns representing the percentage ladies shirts sold (the orange columns in this case) which will bring up the Format Data Series box on the right hand side. Click on the Series Options icon at the top of the box, and then tick the radio button that says Secondary Axis.
This will add the secondary axis to our graph, as well as a second set of axis labels on the right.
However, Excel now tries to plot two column charts on top of one another, obscuring some information.
Step 3: Change the Chart Type of the Second Set of Data
To rectify this issue, we have to change the chart type of the second data series to a different chart type. To do this, click on the orange bars once again to bring up the Design tab, and then click on Choose Chart Type in the Chart group.
This will open the Change Chart Type dialog box and display the two data series. We can now change the chart type individually for each data series. Click on the dropdown arrow next to the second series and choose your desired chart type (Line in this case).
This rectifies the issue and our chart now looks much more insightful.
Step 4: Add Finishing Touches
All that’s left to do now is to add the final touches to our graph, including a chart title and axis titles.
Now it’s your turn. Try adding a secondary axis to your own graph.
Have more questions? Please ask me in the comments below. I’d love to help!