Conditional Formatting of Chart Axes

Quick reference

• 00:05 Even though this dashboard is quite small so far,
• 00:07 you can see it's starting to employ a lot of techniques.
• 00:11 We have a data validation drop down list that allows us to leverage index and
• 00:15 match formulas to restate our entire dashboard.
• 00:18 We have a sparkline, which is being used to
• 00:21 actually grab a hidden series of data in trend, or revenue.
• 00:24 We've got a data bar showing up.
• 00:26 We also have some custom number formats to show our variances as positive and
• 00:30 negative.
• 00:31 And, again, as we flip through different categories, show them a favorable and
• 00:35 unfavorable variances.
• 00:36 And we've got a nice little chart here that is a combination
• 00:40 chart built out of a column and an area chart to show product shrinkage.
• 00:45 This is kind of a pretty big concept in the retail industry,
• 00:48 because we want to know how much product is disappearing.
• 00:51 And you can see here that we've got what is obviously an inventory counting
• 00:55 issue that corrects itself the next month.
• 00:57 I'm pretty happy with the way this is shaping up, but
• 00:59 there's something that I'd like to add to this particular chart.
• 01:02 And that is to indicate when the values exceed positive or negative $500.
• 01:07 Because that's the threshold of tolerance I have for
• 01:10 looking at this particular data set.
• 01:12 So we're going to go and
• 01:14 we're going to actually take advantage of custom number formats to do that.
• 01:17 Now the selected data range here is what's feeding the chart,
• 01:21 the columns are fed from the Variance line.
• 01:24 The Cumulative is what drives the area chart that's in behind, so
• 01:28 you can see it building up over time.
• 01:30 Now in order to illustrate this in detail for what we're going to do here,
• 01:34 I'm actually going to go and create a new custom number format.
• 01:38 And this one is going to look a little bit funny to start with.
• 01:40 But the idea is that I want you to be able to recognize exactly what's going on
• 01:45 once we start adding more complications to this.
• 01:48 So we're going to go with the hash comma, hash hash 0 space.
• 01:52 And I'm just putting a character in quotes afterwards just so
• 01:55 that we can see how these things actually play.
• 01:58 Because I think this is important to understand.
• 02:00 So we have our positive format is going to be A, our negative format is B and
• 02:05 our 0 format is C.
• 02:06 And when that shows up, you'll notice that it carries right through to the chart.
• 02:10 You can see that 0 shows up with a C, negatives are B, and the positives are A.
• 02:15 Now this is the way that I'll build a format, if I've never tried doing
• 02:20 something new with this before, is I might try and build it slowly.
• 02:24 So at this point, I say, you know what?
• 02:25 That's all cool and everything else, but my As and
• 02:28 my Bs are both showing as positive numbers.
• 02:30 So let's go and throw a negative number in front of the B series,
• 02:35 so I should get -10B.
• 02:37 And that looks a little bit better,
• 02:39 I'm starting to get something that works for me here.
• 02:42 Now, the next thing that I want to do is I actually want to format this to try and
• 02:47 figure out if I can actually get it to show me as red if I'm greater than $500 or
• 02:53 less than negative $500.
• 02:55 And to do this, there's a little trick that we can apply to our custom number
• 03:00 formats, which is to add a condition to it.
• 03:03 So first off, we're going to say that we want it to be red.
• 03:06 But we want this rule to kick in only if the values are greater than or
• 03:11 equal to $500.
• 03:13 We're then going to move across to the end of the positive number format and
• 03:17 work on the negative number format.
• 03:19 So, again, we'll say Red, but for this one,
• 03:23 this is going be less than or equal to -$500.
• 03:27 So what I'm hoping here is that this rule will kick in and
• 03:31 turn it red only if it's greater than or equal to $500.
• 03:34 Otherwise it will use this format, and likewise only if it's less
• 03:39 than negative $500 use red, otherwise, apply this other format.
• 03:45 And what you're going to see is that that's actually not exactly what happens.
• 03:50 When we go and apply this, you'll notice that the greater than or
• 03:54 equal to $500 gets applied with the A format.
• 03:58 But then, the $250 actually picks up C.
• 04:02 So those conditions that we're actually working with inside, and
• 04:07 we can use greater than, less than.
• 04:10 Greater than or equal to, less than or equal to, or equal to,
• 04:13 any of those will work between square brackets.
• 04:15 But what you'll notice is that this kicks it out,
• 04:18 it says now this condition only applies to the values greater than 500.
• 04:21 If it isn't, move on.
• 04:23 This condition only applies if it's less than or equal to negative 500,
• 04:27 if it's not, move on.
• 04:28 And now our 0 format becomes the third format which gets applied to
• 04:32 everything else.
• 04:33 Fortunately for us, with the hash comma hash hash 0 here in the final format,
• 04:39 it does inject the nice little negative sign in front.
• 04:43 So now I'm going to go back and I'm going to
• 04:48 take out my space quote A, and my space quote B.
• 04:54 And also the space quote C to clear them out.
• 04:59 When I say OK,
• 05:00 I now get a pretty nice looking number format here that shows in red.
• 05:05 So anything that comes up in this range is outside of my acceptable target,
• 05:10 which is kinda nice to see.
• 05:11 What's really cool too is that this already carries over to the other chart
• 05:15 that I have.
• 05:16 And if I start playing around with this, and I go to Sleeping Bags,
• 05:19 we can see that everything is within my tolerance range.
• 05:22 Although it's making me a little nervous that this starts creeping up, but
• 05:25 the value isn't exactly huge.
• 05:27 If I go into my Tarps, these are really tiny numbers.
• 05:30 So we don't really need anything that's showing us that something
• 05:33 has gone seriously wrong.
• 05:34 The same with Accessories, they're pretty close.
• 05:36 But Tents definitely has got some kind of a problem going between these two months,
• 05:40 although it does seem to straighten out.
• 05:42 So that's how we can use a condition inside a custom number format to actually
• 05:46 even get a little bit more intelligence.
• 05:48 It's kind of like the poor man's conditional formatting, if you will.

Lesson notes are only available for subscribers.