Transcripts for the above video clip:

Excel 2007 Graphs Conditional Formatting

In a roundabout way it is possible to conditional format a chart.  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.