Icon Sets

Quick reference

• 00:04 In this module we are going to start applying some techniques to one of
• 00:08 the dashboards that we're building in this course.
• 00:11 And this one's gonna focus on working with icon sets.
• 00:14 You'll notice we have a nice little summary table here.
• 00:17 It's got a beautiful little piece of data bars on here to show that tents
• 00:21 are the largest product line that we have.
• 00:23 But what I'd like to focus on right now is putting some indicators
• 00:26 on the variance to see how these actually look.
• 00:29 Highlighting them with a little bit of eye candy to let us know if they're
• 00:33 good or bad.
• 00:33 Now what we're gonna start with is we're gonna build a traffic light rule for this.
• 00:37 So we're gonna go to Conditional Formatting, we're gonna go to Icon Sets.
• 00:41 And we have a few under the shapes for different things.
• 00:45 I'm gonna pick the second rule with the three signs, again,
• 00:48 trying to be sensitive to color-blindness issues here for red green.
• 00:52 This way at least we have different shapes.
• 00:55 So we're gonna say OK to this.
• 00:57 And what you'll notice is that it puts a rule on for variance.
• 00:59 Now this is okay but it's not ideal.
• 01:03 The negative 2172 has the red indicator.
• 01:06 That makes sense, it's really negative.
• 01:09 The positive values, 1,698 and 1,031, are showing green.
• 01:13 But then I've got two yellow values, 212 negative and 345 positive.
• 01:19 This is a variance to budget.
• 01:21 If the variance is greater than 0,
• 01:23 it needs to be green because that's a good thing.
• 01:26 If it's yellow, well I never expect to exactly hit budget,
• 01:29 we're trying to be within a range.
• 01:31 We try to budget aggressively as an inspiration to get people there.
• 01:35 So really, I'm kinda looking at going well, yellow,
• 01:38 mediocre performance is let's say that if it's less than $500, that's really bad.
• 01:44 If it's between 0 and negative 500, that's where I actually want to see yellow.
• 01:48 So I need to modify this rule.
• 01:50 So I'm gonna go in and I'm going to highlight the rule,
• 01:55 go to Conditional Formatting > Manage rules.
• 01:58 We'll select our Icon Set and say Edit.
• 02:01 And in here you'll notice that the rule is set up based on percentages.
• 02:06 Now every time you create a different conditional formatting rule it
• 02:08 starts this way.
• 02:09 So if it's between 0 and 33%,
• 02:12 it's gonna give it of the items that are in this dataset, then it'll give it red.
• 02:18 If it's between 33 and 67% of whatever that range is then yellow.
• 02:21 67% or higher and it's gonna be green.
• 02:24 I need to change this so that it's based on values.
• 02:27 So we'll say type number, type number.
• 02:31 Now when the rule tells me if it's greater than or equal to 0 it'll go green,
• 02:36 I really want it to be only greater than 0 is green.
• 02:40 Less than or equal to 0, where do I want this to be?
• 02:43 Well I actually want it to be greater than or equal to negative 500.
• 02:49 That way if it's negative, it's greater than this value but less than 0,
• 02:52 so it'll give me a yellow rule.
• 02:54 And if it's less than -500 it will be red.
• 02:57 And now when we say OK and we say OK again, you'll notice the rule updates and
• 03:02 my 345, which is positive, is now showing in green.
• 03:06 That's great.
• 03:08 One challenge I have with this though.
• 03:10 These traffic lights because they're aligned to the left-hand side of the cell,
• 03:13 they look like they're actually applicable to budget and they're not.
• 03:16 They're applicable to the variance.
• 03:18 But there's no way to align our icon sets to the right.
• 03:22 So I'm gonna show you a quick little trick here to make that work.
• 03:25 We're gonna insert a new column and then what we're gonna
• 03:31 do is we're gonna say =, the ell to the left, Enter.
• 03:35 And I'm actually going to go and apply this to all the same cells by pressing F2.
• 03:40 And I'm gonna hold down my Ctrl key and press Enter and
• 03:42 that will commit everything to the same spot.
• 03:44 Now it inherits the conditional formats.
• 03:46 If it doesn't, you can copy and paste the conditional format rules.
• 03:49 The key part that I do want to do now, I'm gonna highlight this.
• 03:53 We're gonna clear the rules from these cells.
• 03:57 And now I'm gonna modify this rule.
• 04:02 Go to Conditional Formatting > manage rules, we're gonna edit it.
• 04:07 We're gonna check the box to show icon only.
• 04:10 And what you'll see is the values disappear.
• 04:13 I can now shorten this guy out quite nicely.
• 04:17 Cool, now what I'm gonna do now is I'm also going to
• 04:21 copy the format of my % of target and
• 04:25 say = the cell to the left, Ctrl+Enter to commit these.
• 04:30 And we're gonna set up a conditional formatting rule for this guy as well.
• 04:34 This rule though will start with an icon set, and we're gonna go with
• 04:40 these green and red check marks and Xs but I'm again gonna modify this one.
• 04:45 I'm gonna manage this rule and edit.
• 04:50 Once again I'm gonna set this to a value set of rules.
• 04:57 For this one, my value is gonna be if I'm greater than 1,
• 05:02 it's gonna be a green check mark.
• 05:04 If I'm greater than or equal to 95%,
• 05:07 I don't want a green check mark this time, I want something different.
• 05:11 So I'm gonna click on this little guy here and I'm gonna choose this yellow line.
• 05:17 So I can modify and pick different items.
• 05:19 What I can't do unfortunately is change the colors or change the directions.
• 05:23 I can't have a red arrow that goes up or a green arrow that goes down.
• 05:26 What I see here I'm stuck with unfortunately.
• 05:29 But when I do this and click show cell icon only, you'll notice that I
• 05:33 get a nice custom rule that actually lines up nicely for my % of target.
• 05:38 Which I can shrink down and nobody knows the difference.
• 05:41 Everything looks good.

Lesson notes are only available for subscribers.