Would you like to inspect the original subtitles? These are the user uploaded subtitles that are being translated: 1 00:00:08,090 --> 00:00:13,640 In this lecture I'm going to add the data bars to my scrolling table. 2 00:00:13,690 --> 00:00:21,040 What I want to see is right in front of previous year. I want to see bars and the bars are related 3 00:00:21,040 --> 00:00:25,600 to the value, that variance from actual to previous year. 4 00:00:25,600 --> 00:00:31,580 The bigger the bar is the further away I am from reaching previous year numbers. 5 00:00:31,690 --> 00:00:37,270 This can be a good thing because I can be further away in terms of I am higher this year than previous 6 00:00:37,270 --> 00:00:43,930 year. But it can also be a bad thing because I might be far lower than my previous year values. 7 00:00:43,930 --> 00:00:48,370 So this is something that I want to visually show in the bars. 8 00:00:48,370 --> 00:00:51,950 It's going to look like a chart but it's not going to be a chart. 9 00:00:51,970 --> 00:00:54,670 It's actually going to be conditional formatting. 10 00:00:54,670 --> 00:01:01,420 I want to use these in here. Now, to use those these data bars 11 00:01:01,440 --> 00:01:08,770 I need to have values in my cells. Because the cell values tell Excel how big should the bar be. And what 12 00:01:08,770 --> 00:01:11,410 it does is it's kind of like an array. 13 00:01:11,500 --> 00:01:16,880 It compares the size of the bar to the other values that are in this list. 14 00:01:17,110 --> 00:01:20,530 The values here are basically these values. 15 00:01:20,530 --> 00:01:27,280 I'm just going to reference the cells right beside it, and then I'm going to go to conditional formatting. 16 00:01:27,880 --> 00:01:32,560 And add the data bars directly from here. I have a few choices. 17 00:01:32,800 --> 00:01:42,290 I'll go with this one. You can see this is the same blue that I specified as accent 1 in my setup 18 00:01:42,380 --> 00:01:45,750 for the theme, for the colors here under page layout. 19 00:01:45,920 --> 00:01:50,450 That's the same color that I get as my positive values. 20 00:01:50,450 --> 00:01:56,480 And actually I'm fine with that. But I want to change this a bit because I don't want to see those values 21 00:01:56,480 --> 00:01:57,010 again. 22 00:01:57,020 --> 00:02:02,500 I already have the values here and I don't like the red color here, it's too bright. 23 00:02:02,690 --> 00:02:05,070 It really is too distracting, 24 00:02:05,210 --> 00:02:13,490 I find in this report. And I also don't want to see those dashes for the axis. To update it I'm going to 25 00:02:13,490 --> 00:02:20,260 go to conditional formatting. And I can go to manage rows and here I see my row. 26 00:02:20,360 --> 00:02:27,320 I see that it's applied to the whole thing and I can go and edit it. In the editing, 27 00:02:27,350 --> 00:02:30,140 I have a few options like show bar only. 28 00:02:30,230 --> 00:02:31,730 That's what I want. 29 00:02:31,730 --> 00:02:33,860 Do not show me the numbers. 30 00:02:33,860 --> 00:02:36,580 And here I can control the fill that I see. 31 00:02:36,580 --> 00:02:39,140 So that's the positive values. 32 00:02:39,320 --> 00:02:47,030 And then here I can go to negative value and axis and I have some additional options. For the fill color 33 00:02:47,030 --> 00:02:48,220 for negative, 34 00:02:48,350 --> 00:02:52,900 I want it to be a gray. I think I'll go with this one. 35 00:02:53,240 --> 00:03:02,130 And for the axis color, I don't want the black and I'm going to go with white. Let's see if we can apply 36 00:03:02,130 --> 00:03:04,200 it. 37 00:03:04,310 --> 00:03:05,480 That looks good. 38 00:03:06,560 --> 00:03:14,020 Now, I'm going to scroll through this. 39 00:03:14,050 --> 00:03:16,550 Did you notice something right there? 40 00:03:16,570 --> 00:03:17,770 Did you see what happened? 41 00:03:20,410 --> 00:03:22,380 Keep your eye on number 12. 42 00:03:22,390 --> 00:03:22,990 OK. 43 00:03:23,050 --> 00:03:25,830 Keep your eye on the size of the bar here. 44 00:03:25,840 --> 00:03:27,290 Ok I'm going to scroll down. 45 00:03:29,260 --> 00:03:30,550 Did you see what happened? 46 00:03:32,830 --> 00:03:35,270 You see, it gets bigger. 47 00:03:35,270 --> 00:03:36,910 Why does it get bigger? 48 00:03:38,040 --> 00:03:45,390 Because the conditional formatting is always comparing the values here, to the other ones here. 49 00:03:45,570 --> 00:03:50,330 It's completely ignoring all the other ones that are behind this. 50 00:03:50,430 --> 00:03:54,110 Because when I'm scrolling I'm actually changing these values here. 51 00:03:55,130 --> 00:03:56,950 It's comparing it in respect to that, 52 00:03:56,960 --> 00:04:02,330 and then the sizes are changing because the maximum in this range is changing. The minimum in this range is 53 00:04:02,330 --> 00:04:08,700 changing. And I don't want that because I don't want people to think, that looks really big 54 00:04:08,720 --> 00:04:13,010 and then they scroll down and then it's smaller. Or smaller and then it gets really big. 55 00:04:13,010 --> 00:04:20,180 I want to have a consistent size while I'm scrolling through this list and there is an option for 56 00:04:20,180 --> 00:04:20,850 that. 57 00:04:21,140 --> 00:04:24,510 Let's go back to manage rules and find that option. 58 00:04:26,390 --> 00:04:27,700 Ok, it's right here. 59 00:04:27,700 --> 00:04:30,010 Minimum, I have it on automatic. 60 00:04:30,010 --> 00:04:32,800 That's the default one, and maximum as well. 61 00:04:33,040 --> 00:04:37,160 We could give it a minimum and a maximum. 62 00:04:37,330 --> 00:04:38,890 Let's see our options here. 63 00:04:38,890 --> 00:04:42,650 We can give it a number. We can give it a percent. 64 00:04:42,680 --> 00:04:46,940 But I don't want the percent because that's going to be the percent of my percent. 65 00:04:47,050 --> 00:04:47,750 I don't want that. 66 00:04:47,770 --> 00:04:51,100 My actual percent is my number. 67 00:04:51,100 --> 00:04:58,940 I want the number but I need to give it a value, and I need to reference it somewhere in my cell. 68 00:04:59,130 --> 00:05:02,830 I don't have the maximum calculated anywhere yet. 69 00:05:02,860 --> 00:05:04,220 That's what we want. 70 00:05:04,410 --> 00:05:08,040 We want to have a part in the calculation sheet. 71 00:05:08,070 --> 00:05:16,380 A section that has the maximum of not just these what it sees in the dashboard but of the entire app range. 72 00:05:17,550 --> 00:05:23,700 Let me click cancel and we can just leave this. Let's close. 73 00:05:24,000 --> 00:05:25,870 Let's see where to put it. 74 00:05:27,550 --> 00:05:32,580 These are my percentages. They're going to be related to basically these three KPIs because I 75 00:05:32,580 --> 00:05:36,690 want the same bars for the profit, and cash as well. 76 00:05:36,790 --> 00:05:39,330 So I'm going to put it down here. 77 00:05:39,400 --> 00:05:45,870 Let me be good and put headers as well here. 78 00:05:47,690 --> 00:05:56,770 This is calculations for data bars in scrolling table. 79 00:05:56,770 --> 00:06:05,950 Now I need a Min for revenue percentage and a Max for revenue percentage. 80 00:06:08,620 --> 00:06:10,270 This is the entire set. 81 00:06:10,270 --> 00:06:10,660 Right? 82 00:06:10,660 --> 00:06:18,220 And this is dynamic because every time someone changes this here, these are going to change. 83 00:06:18,220 --> 00:06:29,820 I can use this entire range and reference that for my Min and Max. My formula is the Min of my revenue 84 00:06:29,820 --> 00:06:31,640 range and that's this one. 85 00:06:36,100 --> 00:06:39,330 And for the Max it's basically the same thing. 86 00:06:39,340 --> 00:06:46,290 It's just the Max of my revenue range. 87 00:06:46,360 --> 00:06:51,270 Now I'm going to go to the dashboard. I'm going to go to conditional formatting, and manage rules. 88 00:06:51,380 --> 00:06:54,220 And notice I'm not highlighting the whole area. 89 00:06:54,340 --> 00:07:00,400 Excel figures it out because it still applies whatever I change to this whole range here. 90 00:07:01,350 --> 00:07:09,210 And I'm going to edit my rule. And now for the minimum I'm going to say it's a number and that number 91 00:07:09,270 --> 00:07:10,970 is on this sheet. 92 00:07:11,070 --> 00:07:15,030 It's this one. And for the maximum, 93 00:07:15,320 --> 00:07:20,540 I also want a number, and that's this number. 94 00:07:23,260 --> 00:07:25,640 I'm going to apply this now. 95 00:07:25,640 --> 00:07:28,930 See, the size of the bars changed. 96 00:07:29,240 --> 00:07:34,900 Now let's go back and do some scrolling. 97 00:07:34,920 --> 00:07:41,890 Let's start from the top and just keep an eye on the size of the bars, and see if they change. 98 00:07:42,720 --> 00:07:47,330 Now let's scroll down. This is my Max, right? 99 00:07:52,520 --> 00:07:55,110 Get this Max out of view. You see this one 100 00:07:55,220 --> 00:07:57,380 didn't change. 101 00:07:57,500 --> 00:07:59,070 The size is the same as before. 102 00:08:00,050 --> 00:08:03,350 That makes everything consistent. 103 00:08:03,350 --> 00:08:09,770 Now all I have to do for the other ones is to just to repeat the same thing. And with conditional formatting 104 00:08:09,800 --> 00:08:10,730 it's pretty easy. 105 00:08:10,730 --> 00:08:17,350 You can copy this section in and paste it here because the formulas behind this are relative. 106 00:08:17,780 --> 00:08:21,900 They are going to update and the conditional formatting is going to come with it. 107 00:08:22,070 --> 00:08:28,370 But the only thing I do need to update are the references to my Min and Max because I don't want my 108 00:08:28,370 --> 00:08:30,410 profit to reference my revenue. 109 00:08:30,420 --> 00:08:31,990 I need it to be consistent. 110 00:08:32,150 --> 00:08:33,570 So I need to update that as well. 111 00:08:33,590 --> 00:08:40,100 I'm just going to go ahead and quickly add the Mins and the Maxs for the profit and cash, and update 112 00:08:40,140 --> 00:08:42,570 my data bars for those as well. 113 00:09:14,260 --> 00:09:16,420 Now that I have these, 114 00:09:16,480 --> 00:09:23,500 let's go to the dashboard and let's copy this conditional formatting section. And I'm going to paste 115 00:09:23,500 --> 00:09:27,890 it right here and then I'm going to paste it right here as well. 116 00:09:28,360 --> 00:09:30,330 Let's go to the first one, 117 00:09:31,430 --> 00:09:37,540 and manage rules. Let's just make sure it's this section and I'm going to edit it. 118 00:09:37,640 --> 00:09:40,710 Now this was based on the first one. 119 00:09:40,750 --> 00:09:43,350 That's the 39 and that's 40. 120 00:09:43,370 --> 00:09:46,720 Actually I want this to be 41 121 00:09:47,060 --> 00:09:49,610 and this is going to be 42. 122 00:09:52,020 --> 00:09:52,490 Ok. 123 00:09:52,500 --> 00:09:57,300 I'm going to apply this, good. Now, the same for here. 124 00:09:59,200 --> 00:10:00,820 Edit. 125 00:10:01,130 --> 00:10:06,500 This is going to be this one, which is going to be 43. 126 00:10:09,090 --> 00:10:13,030 And this one is going to be 44, this one. 127 00:10:16,070 --> 00:10:17,090 So that looks good. 128 00:10:17,090 --> 00:10:24,290 They're all consistent, their size is not going to change and it's a nice effect to quickly bring 129 00:10:24,290 --> 00:10:27,780 attention to the biggest deviations. 130 00:10:28,510 --> 00:10:31,800 Right now, when I see this. The first thing I see is wow. 131 00:10:32,000 --> 00:10:32,060 . 132 00:10:32,110 --> 00:10:39,670 Right App is actually doing great compared to previous year. And they're actually doing great in terms of cash 133 00:10:39,670 --> 00:10:44,810 compared to previous year, and not bad in terms of their profit. 134 00:10:44,830 --> 00:10:53,570 It's a real fast way of looking at the ones that have the biggest deviations. 135 00:10:53,620 --> 00:10:58,790 If you take a look at Halotot, it's not doing so well in terms of profit. 136 00:10:58,820 --> 00:11:02,390 I chose gray here but it's really a matter of preference. 137 00:11:02,410 --> 00:11:09,670 I just didn't want the red to overrule this whole dashboard, and be too distractive in terms of looking 138 00:11:09,670 --> 00:11:12,960 at the other data that's also important here. 139 00:11:14,040 --> 00:11:22,090 This is dynamic, when I switch between productivity apps to game apps, all the bars are going to update, 140 00:11:22,150 --> 00:11:29,110 all the values are going to update. And I can scroll through and I notice here that Jellyfish is actually 141 00:11:29,530 --> 00:11:32,390 a really good app in terms of revenue, 142 00:11:32,650 --> 00:11:39,520 and in terms of profit when I compare it to previous year data. In the next lecture I'm going to 143 00:11:39,520 --> 00:11:46,390 do a different type of formatting for the budget where we're going to use arrows, up and down arrows, 144 00:11:46,780 --> 00:11:51,290 to highlight where we have positive differences and negative differences. 145 00:11:51,430 --> 00:11:56,840 But we're also going to add a threshold to this because we don't just want to populate this whole dashboard 146 00:11:56,840 --> 00:11:58,460 with up and down arrows. 147 00:11:58,800 --> 00:11:59,230 Okay. 148 00:11:59,350 --> 00:12:01,750 Stay tuned for that in the next lecture. 14547