Create a secondary axis in your Excel chart to show data points that have different scales.
- For updated video clips in structured Excel courses with practical example files, have a look at our MS Excel online training courses . You can even try the Free MS Excel tips and tricks course.
- To see if this video matches your skill level (see the suggested skill score below) do our free MS Excel skills assessment.
- If you are based in South Africa look at the live courses we offer in Johannesburg and Cape Town.
- - 02:49
- - Skill Level: 5
- - What's my skill level
Create Secondary Axis in Excel Charts
Occasionally you may have a graph which looks something like this. You’ll see we have got one series which is fairly high and another series which is particularly low. Now in this case we are not interested in the difference between the two, we are actually looking at the shape. It is very difficult when there is such a low item here to actually tell the shape. And this is where you can use something which is called the secondary axis.
So what you typically do is choose the item that is smaller, go to that series, right click on it, and go and say Format Data Series. And you’ll see one of the options here is to put it on a primary or secondary axis. Now if you look at this axis you’ll see it goes from zero to 500 because these calls, the Vodacom calls, seem to show high numbers. When I say Please put a secondary axis, what it does, is it takes the blue line, creates a new axis appropriate for that blue line (as you can see these numbers are significantly lower) and plots that graph so that this, the blue line is based on this axis, and the red line is based on this axis. As you can imagine, it is difficult to tell which line belongs to which axis. So there are a couple of ways you can do it, and as previously mentioned, every part of the chart is customizable – so perhaps a good way – is this is a blue colour. Let’s go to this axis, right click on it, say Format the Axis. You’ll see all the options exist, for both sides of the axes – you can customize each of them. Go to the Line Colour- I am going to change it to look the same colour as the series. Let’s just go to Line Style – make it a little bit thicker. When I close it, you’ll see that this axis is now the same colour as that line which makes it a little bit easier to identify what we are looking at. Now another thing you can do is label the axes – so if I go to my Chart Tools, under Layout, you’ll see we have got Axis Titles. Now because it has seen that you have got a secondary axis, you’ll see there are now more options. We are going to go to the Secondary Vertical Axis – we are going to ask for a rotated title. You’ll see I have now got a title here and I can go and change it to tell me what I should be looking at.