Locked lesson.
About this lesson
Sometimes showing the chart just isn't enough and you want to display a text message with an indicator on the chart canvas. This lesson explores how to make that happen
Exercise files
Download this lesson’s related exercise files.
Displaying indicators on charts.xlsx74.5 KB Displaying indicators on charts - Completed.xlsx
75.1 KB
Quick reference
Displaying Indicators On Charts
How to show indicators on or behind a chart.
When to use
Sometimes you need to add an overall trend indicator or a special message to a chart. These techniques can be used to make that happen.
Instructions
Showing a message on the chart
- Craft your message in a cell using formulas
- Add a Text box to the worksheet with the chart
- Select the Text box, click in the formula bar, and then select the cell with your message
- Format the text box to remove borders (and the background fill) if necessary
- Position the text box where you need it
Showing items behind the chart
- Move the chart out of the way temporarily
- Set up the message, formulas or conditional formats as required
- Move the chart back to cover up the information
- Right click the chart and set the background to “No Fill”
- You may also need to right click the Plot Area and set it to “No Fill” as well
Hints & tips
- When showing an indicator behind a chart, it’s useful to hide the worksheet gridlines. You can do this via View à Gridlines.
- Combine this technique with inserting symbols or Emojis to really add some pizazz to your chart!
- 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.