1 00:00:00,580 --> 00:00:05,520 Now let's take a look at how we can use icons and data bars with conditional formatting. 2 00:00:05,530 --> 00:00:10,390 This is going to help us bring attention to specific areas of our dataset. 3 00:00:10,450 --> 00:00:12,320 So let's start with icons. 4 00:00:12,340 --> 00:00:17,810 Let's bring attention to people that earn more than 100,000 5 00:00:17,830 --> 00:00:21,200 and those that earn below 30,000. 6 00:00:21,220 --> 00:00:25,870 I'm going to highlight this area, go to conditional formatting. This time 7 00:00:25,870 --> 00:00:28,830 Let's see the options we have under icon sets. 8 00:00:28,900 --> 00:00:34,730 When I go over these with my mouse I can already see a preview in my cells. 9 00:00:34,740 --> 00:00:40,270 Now Excel it's using its own logic to decide which icon to give this data. 10 00:00:40,870 --> 00:00:44,290 Let's get to more rules and see what it has. 11 00:00:44,330 --> 00:00:49,010 Here is checking if the number is in the top 67%. 12 00:00:49,330 --> 00:00:51,610 It's going to get a green icon. 13 00:00:51,610 --> 00:00:56,560 So it takes a look at each number in relation to what you selected. 14 00:00:56,590 --> 00:01:02,850 Now you can change these percentages but in our example we're interested in looking at the full numbers. 15 00:01:02,860 --> 00:01:08,590 We want people greater than 100,000 to have this green icon. 16 00:01:08,650 --> 00:01:12,990 So we have to change this from percent to a number. 17 00:01:12,990 --> 00:01:17,340 Now we can input our value here so that was a 100,000. 18 00:01:18,130 --> 00:01:18,900 Now check this out. 19 00:01:18,970 --> 00:01:25,360 If I put in 30,000 and then change this percent to number, it resets it. 20 00:01:25,410 --> 00:01:25,680 Right. 21 00:01:25,680 --> 00:01:30,390 So just change the type first and then go and input your value. 22 00:01:30,430 --> 00:01:32,040 So that's 30,000. 23 00:01:32,180 --> 00:01:36,460 Let's click away and that's going to be our new allocation. 24 00:01:36,460 --> 00:01:39,610 The yellow one is going to be for people in between. 25 00:01:39,730 --> 00:01:44,830 If I don't want to bring attention to the people in between and in this example I don't, I can click 26 00:01:44,830 --> 00:01:49,890 on this down arrow and select no cell icon. And then click on OK. 27 00:01:49,900 --> 00:01:52,420 And those are my icons. 28 00:01:52,420 --> 00:01:58,110 This way I can quickly see I have two people earning less than 30,000. 29 00:01:58,120 --> 00:02:01,140 If I change this one to 40,000. 30 00:02:01,140 --> 00:02:02,740 My icon is gone 31 00:02:02,740 --> 00:02:07,720 and if they go above 100,000 my icon comes back. 32 00:02:07,750 --> 00:02:11,960 What if you just wanted to have the icons and not the numbers? 33 00:02:12,010 --> 00:02:17,320 There is a setting for this, so let's go back to conditional formatting. This time I want to update 34 00:02:17,320 --> 00:02:17,980 my rules. 35 00:02:17,980 --> 00:02:19,800 I'm going to go to manage rules. 36 00:02:19,930 --> 00:02:27,510 Click on edit rule and put a check mark beside show icon only. When I click on OK and apply. 37 00:02:27,700 --> 00:02:29,900 See what happens. The numbers disappear, the icon stay. 38 00:02:29,950 --> 00:02:34,030 Now in this case I do want to see my numbers. 39 00:02:34,030 --> 00:02:41,170 But what if I wanted to have my numbers in their own separate cells and the icons in their own separate 40 00:02:41,170 --> 00:02:42,540 cells? 41 00:02:42,550 --> 00:02:45,850 Let's go back and show the numbers. 42 00:02:45,850 --> 00:02:48,600 I'm going to click on OK and OK. 43 00:02:48,640 --> 00:02:53,060 Now the way you can do this is to insert another column in between. 44 00:02:53,230 --> 00:02:55,690 This is where the icons are going to sit. 45 00:02:55,740 --> 00:03:04,900 Now to make sure that the icon is always looking at the same number, I'm gonna copy this, right mouse click, 46 00:03:04,990 --> 00:03:08,680 go to paste special, and paste link. 47 00:03:08,860 --> 00:03:12,400 These values are linked to this one. 48 00:03:12,520 --> 00:03:18,910 The one I don't want to have the conditional formatting on, are these ones. I'm going to click on 49 00:03:18,910 --> 00:03:26,350 conditional formatting and clear the rules from the selected cells. For these ones, 50 00:03:26,350 --> 00:03:27,750 we can go back now 51 00:03:28,300 --> 00:03:30,780 and, where was that setting? 52 00:03:30,790 --> 00:03:40,960 Show icon only, click on OK, and OK. Now I have a dynamic report where my icons sit in their own separate 53 00:03:40,960 --> 00:03:41,500 cells. 54 00:03:41,830 --> 00:03:44,380 OK so let's just double check this one. 55 00:03:44,380 --> 00:03:55,410 Let's change this value to 20,000 press enter and both are dynamic. Now let's take a look at using data bars. 56 00:03:55,410 --> 00:03:59,440 To show the difference in salary from previous year to this year 57 00:03:59,460 --> 00:04:07,950 we want to represent that using data bars. Data bars are similar to a bar chart inside cells. 58 00:04:08,010 --> 00:04:12,810 All we have to do, highlight our range, go to conditional formatting. 59 00:04:12,810 --> 00:04:15,330 This time let's go to Data bars. 60 00:04:15,870 --> 00:04:22,320 When I hover over these, we can already see how they're going to look in the cell. The size of the 61 00:04:22,320 --> 00:04:26,070 data bars are automatically adjusted. By default, 62 00:04:26,070 --> 00:04:31,190 Excel takes a look at this data set, decides what is the biggest number, what's the smallest number 63 00:04:31,290 --> 00:04:34,230 and it adjusts the size of these bars. 64 00:04:34,230 --> 00:04:37,650 Let's go to more rules and see the other options we have. 65 00:04:37,710 --> 00:04:39,060 We can select the color. 66 00:04:39,060 --> 00:04:42,070 This is the color of your positive bars. 67 00:04:42,090 --> 00:04:44,120 Let's go with a green color. 68 00:04:44,220 --> 00:04:50,640 You can select the border if you want and you can also decide how negative values should be filled. 69 00:04:50,640 --> 00:04:55,200 If you're okay with the default red you can go with that or you can change it here. 70 00:04:55,410 --> 00:04:58,530 You can also decide on some Axis settings. 71 00:04:58,530 --> 00:05:00,590 So the default is automatic. 72 00:05:00,660 --> 00:05:02,060 It's not always in the middle. 73 00:05:02,070 --> 00:05:03,450 It depends on your data set. 74 00:05:03,750 --> 00:05:09,390 But if you're really want it in the middle of the cell, you can select cell midpoint. And you can also 75 00:05:09,390 --> 00:05:11,780 decide on the color of your axis. 76 00:05:11,790 --> 00:05:17,830 I'm going to go with white so that I don't see that axis going through here and then click on OK. 77 00:05:17,970 --> 00:05:24,150 And let's just click on OK and we see our numbers and our bars in there. 78 00:05:24,150 --> 00:05:29,910 Now with a quick look we can see this person seem to get a huge salary increase but that's not really 79 00:05:29,910 --> 00:05:30,750 the case right. 80 00:05:30,750 --> 00:05:32,250 Maybe they weren't there last year. 81 00:05:32,250 --> 00:05:36,700 They have no previous year's salary. So let's actually update this formula. 82 00:05:37,290 --> 00:05:42,690 If previous year's salary is zero we shouldn't see anything in the cell. 83 00:05:42,690 --> 00:05:45,300 Otherwise it should do the calculation. 84 00:05:45,300 --> 00:05:51,690 If previous your salary equals zero then let's just do nothing. 85 00:05:51,750 --> 00:05:55,650 Otherwise it should run our calculation, close bracket, 86 00:05:55,650 --> 00:05:56,870 press enter. 87 00:05:57,010 --> 00:06:03,530 Now let's send this down and our data bars updates automatically. 88 00:06:03,540 --> 00:06:07,590 So here this person really did receive a big salary increase. 89 00:06:08,040 --> 00:06:14,580 Now again you have the option not to show the number or do the same trick with it before. Show the number 90 00:06:14,580 --> 00:06:18,090 in their own cells and show the bars in own cells. 91 00:06:18,150 --> 00:06:21,880 This time I just want to show the bars and no numbers here. 92 00:06:21,880 --> 00:06:23,790 So I'm gonna go to manage rules. 93 00:06:23,790 --> 00:06:29,100 Edit rule, put a checkmark besides show bar only. And click on OK and 94 00:06:29,160 --> 00:06:31,060 OK. That's my report. 95 00:06:31,620 --> 00:06:33,760 It's super easy to set up. 96 00:06:33,780 --> 00:06:38,700 All you have to do is select and apply conditional formatting to this.