Excel Chart Secondary Axis

One of the Excel questions I get asked often is; how do I add a secondary axis to my chart? It’s actually quite easy but there is a trick to it.

Excel Secondary Axis Trick Step 1

Let’s take this data. Now, the first thing you want to do is simply insert your chart. Insert > Charts > Select Line Chart.

I hear you…you might not want a line chart, but trust me this is the easiest way to perform the secondary axis manoeuvre. You can change it to the chart type you want after you’ve inserted the secondary axis.

Now, what you may find is that you can’t even see the data you want to move to the secondary axis since it’s on a massively different scale.

As you can see in the example below the percentages are so small you can’t even see the line for them. Note: this is in Excel 2010, but in Excel 2007 you can generally always see the line at the bottom.

Tip: as a general rule the line that you want to plot on the secondary axis is generally the data on the smaller scale. So in this example it’s the cumulative percentage.

Excel Secondary Axis Trick Step 2

If you can’t see the line you want to plot on the secondary axis at the bottom of your chart simply squash the chart until it comes into view. Click the chart > grab the handle on the right side and drag to the left.

Voila! Now your chart should show the second line at the bottom like this:

Excel Secondary Axis Trick Step 3

Click on the line you want to plot on the secondary axis > Right-click > Format Data Series > Series Options > Secondary Axis.

Tip: If you’ve got Excel 2010 you can double click the line to open the Format Data Series dialog box. That’ll save you a few clicks

Now your chart will look like this. Ugly! But now that you have your secondary axis you can go about fixing the formatting and change the chart type for the Sales value to your preferred chart etc.

Here’s what I’ve done:

What can I interpret from my chart?

1. Monthly sales are higher in the first half of the year.
2. Over 50% of my sales are achieved by May
3. Over 80% of the annual sales are made with only 2/3rds of the year gone.

If you tried to get that same insight from a plain table of numbers it would take you a lot longer than the few seconds you can look at a chart and understand what’s going on.

The chart formatting steps I took:

1. Removed the grid lines.
2. Widened the chart.
3. Changed my sales data to a column chart.
4. Changed my number format to thousands.
5. Modified my legend to show ‘Sales $’000’.
6. Moved my legend to give space for my axis labels so they aren’t vertical aligned.
7. Changed my secondary axis maximum to 1.
8. Added data labels to my secondary axis to aid with interpretation.
9. Removed every second data label to reduce clutter.
10. Changed the colours of my chart to allow labels to be more easily read.

Those 10 steps took me less than 2 minutes.

Download the workbook here and try it for yourself.

If you’d like to learn the exact steps to make these changes you’ll find comprehensive video tutorials in our Premium Excel Training. Click here to sign up.