Would you like to inspect the original subtitles? These are the user uploaded subtitles that are being translated: 1 00:00:08,670 --> 00:00:15,430 In this lecture I'm going to use icon sets, up and down arrows, to visually indicate the difference 2 00:00:15,670 --> 00:00:18,150 from actual to budget. 3 00:00:18,400 --> 00:00:24,050 The icon sets are basically driven by this number but I don't want to add the arrows everywhere. 4 00:00:24,060 --> 00:00:29,310 I want to add a threshold to this to only emphasize the bigger differences. 5 00:00:30,610 --> 00:00:33,660 I'm going to do this in two different ways. 6 00:00:33,670 --> 00:00:36,780 One way, is to use the conditional formatting 7 00:00:36,810 --> 00:00:45,700 the icon sets that you see here. And a second way, which is my preferred way is to use my own icon sets. 8 00:00:46,800 --> 00:00:53,910 Now, the good or the advantage of using the conditional formatting icon sets is that it's already defined. 9 00:00:53,910 --> 00:00:56,190 The color is defined, the shape is defined. 10 00:00:56,320 --> 00:01:00,310 Just click on it and it's easy and simple to use. 11 00:01:00,330 --> 00:01:07,770 The disadvantage of using them is that you're restricted to these icons and I'm going to be honest with you. 12 00:01:07,890 --> 00:01:11,500 I'm not a big fan of these icons. 13 00:01:11,730 --> 00:01:13,790 If I have to use them. 14 00:01:13,860 --> 00:01:19,190 The one that I normally would use, is this one. 15 00:01:19,270 --> 00:01:24,160 And the reason I'm not a big fan is I find them to be a bit too bulky. 16 00:01:24,550 --> 00:01:27,270 That's why I prefer to use my own icon sets. 17 00:01:27,280 --> 00:01:32,890 But in the first example that I do, I'm going to use these. And in the second example for for this one, for 18 00:01:32,910 --> 00:01:35,080 the profit variance to budget, 19 00:01:35,080 --> 00:01:41,010 I'm going to use my own icon sets. And then you can pick the method that you like best. 20 00:01:41,980 --> 00:01:48,680 To do the first way, using the conditional formatting icon sets, the steps that we did for the 21 00:01:48,710 --> 00:01:53,330 data bars is basically identical to the steps that we need to do now. 22 00:01:53,680 --> 00:01:57,580 And that means we need to have numbers in the cells that we're going to add 23 00:01:57,610 --> 00:01:59,840 icon sets to. 24 00:02:00,000 --> 00:02:01,340 That's my number. 25 00:02:01,350 --> 00:02:02,850 That's the difference to budget. 26 00:02:02,850 --> 00:02:07,980 I'm going to go to conditional formatting, and I can either do it directly from here. 27 00:02:07,980 --> 00:02:09,520 Let's just see what happens. 28 00:02:10,560 --> 00:02:15,570 The good thing is that you get an immediate preview of how the icons work with this. 29 00:02:15,660 --> 00:02:20,940 But you see this one and this one, I just find it like information overload. 30 00:02:21,060 --> 00:02:26,700 It's just so many things and you don't really know where to look or what to concentrate on. 31 00:02:26,730 --> 00:02:35,290 I'm going to go with this one but I don't want to show the parts where I'm not that far from reaching 32 00:02:35,290 --> 00:02:36,210 budget. 33 00:02:36,330 --> 00:02:38,820 I don't want to bring attention to those. 34 00:02:39,150 --> 00:02:44,270 I'll go to more rules and let's do this from scratch together. 35 00:02:44,670 --> 00:02:52,430 You can see here the last one is the icon sets. Now, the icon set that we were looking for is this one. 36 00:02:52,710 --> 00:02:56,800 Again you have the option to show icon only. That's what we want. 37 00:02:58,790 --> 00:03:03,320 And now you can decide if you want a threshold or not. 38 00:03:03,320 --> 00:03:10,790 Here there is a threshold that says if it's greater than or equal to 67 percent then put this. Otherwise 39 00:03:10,820 --> 00:03:11,720 put this, 40 00:03:11,930 --> 00:03:16,930 and if it's less than 33 then show it as red. 41 00:03:16,940 --> 00:03:21,140 One option you do have to minimize the icons that you use. 42 00:03:21,140 --> 00:03:27,940 You can click on this and say don't show an icon for if it's within the threshold. 43 00:03:28,160 --> 00:03:31,920 Another thing you can do is define your own threshold. 44 00:03:32,240 --> 00:03:38,450 In my case let's say I want to bring attention to anything above 5 percent. 45 00:03:38,840 --> 00:03:42,140 I have to change the type to number. 46 00:03:42,140 --> 00:03:47,630 And now I can say if it's greater than, let's say 5 percent. 47 00:03:47,880 --> 00:03:54,520 And here again change it to a number because the moment you change this it resets what you enter in the value. 48 00:03:54,540 --> 00:03:58,500 So change it first before you enter your number. 49 00:03:58,860 --> 00:04:02,950 And this should be minus 5 percent. 50 00:04:03,120 --> 00:04:12,140 Let's take a look, and say okay and that looks good. 51 00:04:12,150 --> 00:04:18,240 I'm not putting anything on the values that are within this plus and minus 5 percent. 52 00:04:18,360 --> 00:04:27,810 I could make it less bulky by changing the font size and making it smaller, like this. 53 00:04:27,810 --> 00:04:31,140 But you don't really have much influence on anything else. 54 00:04:31,690 --> 00:04:39,330 The other option that I mentioned is to use symbols in here that you choose yourself. 55 00:04:39,450 --> 00:04:41,120 You can pick your own symbols. 56 00:04:41,130 --> 00:04:50,390 If I go to insert symbol, you see a lot of useful symbols under Arial, under shapes. And you can see 57 00:04:50,390 --> 00:04:52,000 my recent ones are these. 58 00:04:52,000 --> 00:04:52,050 . 59 00:04:52,320 --> 00:04:56,200 So you can just, you just need to click on them. 60 00:04:56,220 --> 00:04:59,620 Let me bring this down so we can see it and insert. 61 00:04:59,700 --> 00:05:03,850 Then click on the next one and insert and bring it into your sheet. 62 00:05:05,260 --> 00:05:11,020 So what I want to do is to use a formula that's going to dynamically assign the right symbol to these 63 00:05:11,020 --> 00:05:11,800 numbers. 64 00:05:12,690 --> 00:05:20,520 What I'm going to do is to just highlight this. I'm going to Press Ctrl+C+C and just add it to my clipboard. 65 00:05:20,880 --> 00:05:25,940 And I'm going to do Ctrl+C+C on this so that I have both of them available. So that I could easily use 66 00:05:25,940 --> 00:05:27,640 it in my formula. 67 00:05:27,660 --> 00:05:38,580 I'm going to do if this value is greater or equal to 0.05, then use quotation 68 00:05:38,580 --> 00:05:41,810 marks because a symbol is a text. 69 00:05:42,000 --> 00:05:47,190 So if it's greater, I want this symbol. 70 00:05:47,320 --> 00:06:01,770 Otherwise, if this value is lower than -0.05 then quotation mark, this symbol. 71 00:06:01,950 --> 00:06:04,050 Otherwise, I want nothing. 72 00:06:04,080 --> 00:06:05,580 I'm going to close bracket. 73 00:06:05,710 --> 00:06:07,770 I have to close bracket again. 74 00:06:08,190 --> 00:06:09,940 Let's just double check. 75 00:06:10,200 --> 00:06:11,700 OK, that looks good. 76 00:06:11,700 --> 00:06:16,880 I'm just going to add another If for If this is empty. 77 00:06:17,910 --> 00:06:23,700 Because when I scroll down and I don't have any apps here, and it's going to be empty. I want it to show 78 00:06:23,700 --> 00:06:24,490 nothing. 79 00:06:24,750 --> 00:06:28,690 So if this is empty then empty. Otherwise, 80 00:06:28,980 --> 00:06:35,560 do the other if thing, and now I need another bracket. 81 00:06:35,630 --> 00:06:37,400 OK, so looks good. 82 00:06:37,820 --> 00:06:44,700 Let me close this for now and let's scroll. 83 00:06:44,770 --> 00:06:45,400 That looks good. 84 00:06:45,400 --> 00:06:47,420 There's nothing written here. 85 00:06:47,650 --> 00:06:53,500 Now the next step is to conditionally format this. 86 00:06:53,660 --> 00:06:56,040 I'm going to highlight this. 87 00:06:56,210 --> 00:07:01,970 Go to Home, conditional formatting, and add a new rule. 88 00:07:02,120 --> 00:07:04,930 This time I need a slightly different rule. 89 00:07:05,180 --> 00:07:14,670 I want to format only cells that contain. And I can say if the cell value is equal to my symbol. 90 00:07:14,750 --> 00:07:18,710 I just pasted this from the clipboard that I had, then 91 00:07:19,070 --> 00:07:20,840 what color do I want to have? 92 00:07:20,990 --> 00:07:22,850 And the color is not the fill. 93 00:07:22,940 --> 00:07:27,390 It's actually the font because the symbol is just like a font. 94 00:07:27,500 --> 00:07:32,930 I want it to be a red but not such a bright red. 95 00:07:34,380 --> 00:07:37,300 Let's go to a custom and make it lighter. 96 00:07:40,270 --> 00:07:41,230 OK. 97 00:07:41,770 --> 00:07:43,790 Let's say OK. 98 00:07:44,070 --> 00:07:45,630 That looks good. 99 00:07:45,680 --> 00:07:50,570 Now I can go back to my conditional formatting and let's add a new rule. 100 00:07:50,810 --> 00:07:57,500 But before I do that I'm going to close this because I can't copy from the clipboard directly to this. 101 00:07:57,650 --> 00:08:01,420 So I'm just going to close this and copy this symbol here. 102 00:08:02,250 --> 00:08:06,250 Let's go back to conditional formatting, manage rules. 103 00:08:06,360 --> 00:08:11,050 I'm going to add a new rule. Format only cells that contain. 104 00:08:11,220 --> 00:08:17,170 And this time my cell values should be equal to the up arrow, and my color 105 00:08:17,190 --> 00:08:21,700 I'm going to go with this green. 106 00:08:21,710 --> 00:08:28,980 That looks good and I can make it smaller. 107 00:08:29,020 --> 00:08:32,140 Now I can copy the formatting that I like. 108 00:08:32,140 --> 00:08:37,940 I'm going to copy this one and paste it here, and also paste it here. 109 00:08:38,110 --> 00:08:43,180 The only thing is always when you're copying and pasting conditional formatting. 110 00:08:43,180 --> 00:08:50,050 Do check your rules and do check if you are bringing over just conditional formatting multiple times 111 00:08:50,050 --> 00:08:56,350 to your cells or not. Because it's so easy to really expand this whole conditional formatting rule. 112 00:08:56,560 --> 00:09:03,570 This looks good and this should be fine. Let's just double check these as well. 113 00:09:04,980 --> 00:09:14,600 It's the full range and my rules are good. OK so that's how you can use conditional formatting either 114 00:09:14,600 --> 00:09:17,330 with your own symbols or with the conditional formatting 115 00:09:17,330 --> 00:09:19,650 icon sets in the dashboard reports. 11462