Tip:
Highlight text to annotate it
X
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.