Create a chart with a secondary axis in Excel 2016

 Oct 28, 2016

If you have two series of data that are on very different scales you can change your chart to have two sets of axes, one on the left hand side (a primary axis) and one on the right hand side (a secondary axis).

Create the chart as normal, here’s a chart I’ve created in Excel 2016:

As you can see, I’ve got mountains and molehills here.

If you click on the series data that you want to put on the secondary axis to select it and then right click you come to this menu:

Choose the bottom option, Format Data series…

And then choose Secondary Axis from the radio button

Your chart now most likely looks like this:

Which is good in some ways because there are now two different scales, one on the left side going from $0 to $100, and the other on the right going from $0 to $3.50.

But it isn’t great because it is difficult to read the orange data at the back because the blue data at the front covers it.

To fix this, we’ll make one of these series a line chart rather than a column chart so that we can read both series at the same time without one covering the other.

Click on the data series that you want to make a line chart (I’m picking the blue data for mine) and then right click and choose Change Data Series Type…

From this menu click on the dropdown for the series you want to change and pick one of the line chart options (I’m picking Line with markers).

And now you have a chart on two axes that is very readable.

You can learn more about charts in our Excel 2016 – Level 2 training course.

To find out more on our  BSB51415 – Diploma of Project mananagement click here.

 

 

How do your Excel skills stack up?   

Test Now  

 

How do your Excel skills stack up?   

Test Now  

About the Author:

Matthew Goodall  

Matthew is a qualified Microsoft Office Specialist, Microsoft Certified Applications Specialist and a Microsoft Certified Trainer with over 11 years of hands-on experience in a training facilitation role. He is one of New Horizons most dynamic instructors who consistently receives high feedback scores from students. Matt enjoys helping students achieve real professional and personal growth through the courses he delivers. He is best known for creating “fans” of students, who regularly request him as an instructor for any future courses they undertake at New Horizons.

Read full bio
top