Home // Video Tutorials // 6 - 10 (Advanced) // 7 // Excel Chart Conditional Formatting

Excel Chart Conditional Formatting

How to do a excel chart conditional formatting so that colours on the chart or graph change depending on what is entered.

Excel Chart Conditional Formatting

In a roundabout way it is possible to do a Excel Chart conditional format.

So let’s explain what I mean by this. Here we have got Product B – sales during various months. What we want is somehow to clearly see where the highest month was and where the lowest months are. Now it is simple enough to do that – you have got your data and you’ll see we have created two more columns, one for the maximum and all we do is using formulae we determine what the maximum is and using the IF function, to see that if this is the maximum month, pull through the maximum, otherwise put a zero. And here is the minimum – same thing. It checks to see what is the minimum and over here it checks to see if that is a minimum and it brings it through.

Let’s now take this chart which currently only looks at this column and we’ll add in where the maximum is. I’m going to drag it across and you’ll see what it’s done – its still a column chart but its shown with another colour where the maximum is happening. This however, doesn’t look great and so there must be a better way of showing this. An option would be to change this series – the maximum series – to a different chart type. So looking at this, perhaps we are happy to change it to a Line with markers – when I say OK. You’ll see that looks a little bit better. We have now got a marker, the problem is – where all the zeros are – its actually showing us the markers which doesn’t look great. The reason these markers exist is because over here the cell says zero, zero is a number that can be plotted. What you need to do to remove these points, is to somehow convince Excel that it doesn’t exist. And the way you do that – is an interesting option. In your Function Wizard – we’ll just click in another cell here, and I go to “All” my functions you’ll see there is function available called NA and what this does is return the error value #N/A (value not available). What this does, is forces Excel to have no value. So let’s just create it – you’ll see I have created a #N/A. Now let’s see what happens if, instead of this IF function ending up in a zero I make it =#N/A. You’ll see the marker has disappeared. If we copy this formula all the way down, notice what has happened. Using this error message we have told Excel to ignore all the other markers and only show the marker that has a real number on it. And this must be one of the very few uses for this particular function.

So let’s see how it works – that is currently the maximum number and there it is showing there. Let’s pretend we made a mistake in this month and the number here is actually 260. Watch what happens on the graph – I click Enter – the graph changes but more importantly the maximum now moves to where the maximum exists, so it is a form of conditional formatting. The graphs will move around depending on the underlying data. I’ll just change it back to 130, and there it goes back again.

Let’s now do the same thing for the minimum portion – you’ll see we have set up a cell to figure out what the minimum number is and at the moment we have an IF statement – that if it is the minimum it gives us the number and if not it’s zero. We now know that that needs to change so it becomes an N/A – say OK – I am just going to copy and paste it down. We can now incorporate it into the chart. Notice that we now have the minimum – the two points identified but there is a line in between. That was because we chose a Line Chart. You may want this to stay there, however you may also want only the points to be shown. So we are going to achieve this as follows:- Click on that item, say Change Series Chart Type and then go and put on instead an XY Scatter. When I click and I say OK, you’ll notice that the following has happened. We are now at a point when for whatever reason, Excel has now put a secondary axis in, for, as the result of that, the minimum which we know should be sitting in January is being shown in November. This is because of the secondary axis and it is easy enough to clear – it is just important that you notice it. Just right click on that series – go to Format Data Series and instead of being secondary axis which Excel has automatically put on, change it to Primary Axis and when I say “Close” you’ll see I have now got a graph that points out the maximum point and it points out the two minimum points. So, if, for example, let’s go here and this 108 wasn’t actually 108 but it was 8 – when I click Enter, suddenly it will only show the one place where the minimum exists. So that allows you to change your graph depending on some criteria here and probably the more important part than the chart is just getting the actual spreadsheet correct.

Now what happens if you aren’t happy with the markers that you have here. You can go, right click on the series, say Format Data Series and go and play with the marker options. But there is another useful way of doing it – to the right here I have created some Clip Art and I have just got two arrows, a green one and a red one. To use these as my markers, rather, what I can do is I click on my picture and I copy it, I go back to my chart and I click on the Series – so you’ll see we are on the Data Series at the moment and then all I do is I push Control V to paste. What you’ll see is the marker has now changed to the marker I want. Now let’s see if I change this to 260 what happens to the chart – you’ll see it moves around but the marker I have chosen is now the marker of choice. Let’s do it again – this time we are going to take the red, I am going to copy it, go back here and this time I am going to click on this Data Series, you see I highlight it and push Control V and you’ll see I now have two arrows there. I am just going to change that to 130 and just make that 50. So you can see that your graph now will change depending on the data and instead of having to draw arrows into the graph and then move them every time your data changes – the graph itself will move the markers around.