How to work with a chart series including changing colours, markers and other useful customisation features.
- 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.
It is also possible to customize the actual series within the graphs – if you see we have got two series here. If we hover over them you’ll see we have got the various series. If I choose one of them, I right click and I go to Format Data Series, I get a couple of options. The first set relates to the Series Overlap, so if you watch over here, these two series sit next to each other. They are neither separated or overlapping, they are at 0%. Let’s click on this and move it slowly to the left and notice we are slowly starting to separate the gap between the two- you can go to the extreme of all the way to 100% and you’ll see there is a big gap between them. Alternatively let’s take them to a positive 23 and you’ll see there is now a bit of an overlap between the two and at the extreme it is so overlapped that you don’t actually see the one item. Let’s just get it back to what we were – 0%.
The next question is the gap width. What is the width between the various items. Let’s go to no gap at the extreme all the way to the left and notice that there is no gap between the various points and we’ll go to large gap and you’ll see there is a massive gap between them. Let’s go back to a reasonable number.
You can see as well that you can plot the series on a primary axis or a secondary axis. We will cover this later.
The rest of the options consist of Fill, Border Colour, Style, Shadow, 3D-Format – these are all pretty much the same. I just want to show you what happens in the item called Fill. You’ll see the Fill decides the colour so, for example, or if I say No Fill the colour disappears. Solid Fill, Gradient Fill – you can do that. Automatic is the option that we prefer. But notice there is the one item here called “Invert if negative” . What you’ll see is this data has got a couple of negative items where the numbers have gone negative. If I click Invert if negative, you’ll see what it does it changes the colour of any item that is negative. So if I close it, you’ll see we have now got it appearing as a white colour. Let’s just take this number and change it to -100, then when I click Enter you should be able to watch here, you’ll see not only does the formula change, the number change, but the graph actually inverts to a new colour to show that it is negative which is quite a nice feature. I am just going to switch back to that. Getting back to Format Data Series, all the other options are fairly standard and have been covered before elsewhere.