Displaying Indicators on Charts

Quick reference

• 00:05 I'm pretty thrilled with the way my dashboard is actually shaping up here,
• 00:08 but there's one thing that does kinda bother me and
• 00:10 that's on my product shrinkage chart.
• 00:13 I don't really have a good indicator of what the total volume of shrinkage is for
• 00:17 the entire year.
• 00:18 I can see the area chart, but the total value is kinda hidden behind the 330
• 00:23 which is the data label for the current month, so I'd like to do something better.
• 00:29 Now, on my Source Data page, you'll notice that I have a nice little message here
• 00:33 that says year to date shrinkage is $595.
• 00:35 This is based off of a formula, as you can see at the top, year to date shrinkage in
• 00:39 quotes, and then what I've done is I've taken cell N49, which has my $595.
• 00:46 And I've wrapped in the TEXT function,
• 00:48 with which the second parameter is to come back and provide the custom number format.
• 00:52 So this is just like the custom number formats that you've been working with.
• 00:55 So that's kinda nice, I can format it to come out with a dollar sign in front of
• 00:58 it, which looks pretty good.
• 01:00 Now, I'd like to return this to the canvas of my chart, but
• 01:05 the problem is there's nowhere really to do that.
• 01:07 So what I'm gonna do is I'm actually going to add a text box and put it in there.
• 01:12 The text box can be found on the Insert tab, again, my ribbon's collapsed, but
• 01:16 it's way over near the symbols, there's a text box.
• 01:20 And what I'm gonna do is I'm gonna draw my text box here, left click and
• 01:24 drag, and that will create the text box.
• 01:26 And at this point it goes into edit mode with a nice little flashing piece here.
• 01:30 Now, if this were like a chart title, you would select it and then press equals.
• 01:35 But you'll notice that the = now starts showing up in the text box.
• 01:38 And that's not what I want,
• 01:39 because that means that when I'm done it'll just end up being text.
• 01:44 So what I'm gonna do instead is I'm gonna grab the text box,
• 01:47 I'm gonna come to the formula bar here, and I'm gonna type in =.
• 01:51 Now I'm gonna jump over to the Source Data tab, and grab my message and hit Enter.
• 01:57 And you'll notice that now,
• 01:59 I get a nice little message that shows me my year to date shrinkage.
• 02:02 Now I'm not thrilled because it's got a little background around it, so
• 02:06 I'm gonna grab that, go to the drawing tools.
• 02:08 And under my Shapes Styles, I'm gonna choose the preset with nothing,
• 02:13 because now that goes away.
• 02:16 And I can now grab it and drag it right on top of my chart,
• 02:19 I don't ever even really have to align it anywhere specifically, although I will.
• 02:24 The next thing that I'd like to do is add a little conditional formatting flag
• 02:27 beside it.
• 02:28 So what I'm gonna do now is I'm gonna come up to the cell beside, and
• 02:33 we're gonna say =, and I'm gonna go grab the value of the 595, and we'll say Enter.
• 02:40 Now the challenge is I can't see this through the chart, so
• 02:43 here's what I'm gonna do.
• 02:44 I'm gonna click on the chart, right click,
• 02:47 I'm gonna go say Fill, I'm gonna choose No Fill.
• 02:51 So that's kinda interesting because now I can see values through the chart.
• 02:55 So I can come over here and apply the conditional formatting rule,
• 03:00 we'll go and choose an icon set with our little flags.
• 03:03 And I'll even modify this rule now, say Manage, Edit.
• 03:09 We'll choose to show only the icon, and
• 03:11 we're gonna set our values consistent with the number formatting that we did before.
• 03:16 So if it's greater than 500 or equal to or
• 03:20 if it's greater than or equal to -500.
• 03:24 At this point in time we should get a rule that says that if it's greater than 500,
• 03:31 we want a red flag, if it's -500, a red flag.
• 03:36 Otherwise, we'll throw a green flag on there
• 03:39 to say that it's within tolerance range.
• 03:41 We can now say OK, and OK, and you'll notice that we have
• 03:45 a red flag that I could now go and line this up nicely with.
• 03:49 There we are, that looks good, and now hopefully as I switch back and
• 03:53 forth between my categories, you'll notice that things show up quite nicely.
• 03:58 So that's how we actually can go and
• 04:00 put indicators behind the chart, as well as put something else on top of the chart,
• 04:05 should we actually need to do that to control something into a place where
• 04:08 it doesn't usually have something that we can work with.

Lesson notes are only available for subscribers.