How to Easily Add a Secondary Axis in Excel Charts
Creating visualizations in Excel can be really powerful, however when working with multiple metrics sometimes things can get a little bit unreadable. This is where creating a secondary axis in our Excel charts can be super handy.
In this simple guide, we are going to run through how to enhance our data visualizations through the use of a secondary axis. We will cover:
- Why it is beneficial to split data across two separate axis
- How to add a secondary axis to an existing chart
- How to use combination charts
Let’s get started with a quick example of why we would want to use a secondary axis.
Why should we split out our metrics?
Let’s say we are running a business that measures online advertising, and we want to see for each month the number of times our ads were displayed to users (known as an impression), and how many times people click on them.
As I’m sure you can imagine – the difference between these two figures is going to be pretty massive! (Fun fact – only around 0.05% of ads are clicked on).
Looking at this over the course of 12 months, if we made a simple column chart with both metrics included, our result would be as follows:
We can easily see here that there was quite a variance month on month with the overall impressions served – but for the most part we can’t really tell here which months were the strongest in terms of overall clicks because visually the difference between those orange lines is pretty hard to read.
This is where the use of a secondary axis comes in!
Before we move on to how to achieve this, let’s quickly look at the desired output:
By adding a second axis on the right of the visualisation using a 0 – 4,000 range, and swapping the orange bars to a line overlay this chart is now far more insightful and easy to read. We can very easily tell which months had the highest number of clicks, and which were the lowest.
Now let’s move on to a couple of methods for creating this type of chart.
Adding a Secondary Axis to an Existing Chart
Let’s use our first double column chart as the base here, and look to add in the new axis and swap to a line.
First, we need to click on one of the orange lines, and in the pop up menu that appears on the right, under Series Options, tick the Secondary Axis button:
This will then move the axis to the right, but actually makes the chart harder to read initially, due to the stacked approach it has taken:
To fix this, we need to turn the orange markers into a different type of visualization option. To do this, right click on one of the orange lines, and select Change Series Chart Type…
We will be presented with a pop up box where we can select the chart type for each metric. Click on Clustered Column under Clicks and select one of the Line options, and then press OK.
This then results in our desired chart!
For this example, working with a combination of column and line worked, but definitely feel free to play around with which metric has which type and which axis depending on the data you are working with.
Next, let’s look at how to create a chart that starts with two separate axis.
How to use Combination Charts
The second approach is the easier one to work with if we haven’t actually made our chart yet. To achieve this, highlight all of our data in the table we are working from, and then under the Insert ribbon, in the Charts section, click on the combination chart box and select Create Custom Combo Chart…
This brings us back to the same pop up box we had earlier when changing our second set of columns to a line. It will default to the desired combination, but the key here is to make sure to tick the Secondary Axis box for the second metric, otherwise we will essentially have the same problem we started this tutorial with, just with a very flat looking line instead of small columns:
As we can see from the example output that is displayed in the pop up box – this gives us our exact desired column and line combination chart split out over on to a second axis that is very clean and readable.
This sums up our guide on how to add a Secondary Axis in Excel. We have covered how to edit existing charts that have multiple metrics sitting on the same axis, and a second quicker approach to making sure our charts are set up the way we want from the start. For more handy guides on working with Excel, be sure to check out our Excel Tips page.
