1 00:00:00,05 --> 00:00:01,03 - [Instructor] So let's talk about 2 00:00:01,03 --> 00:00:03,01 how to create in cell bar charts. 3 00:00:03,01 --> 00:00:04,02 And what this allows us to do 4 00:00:04,02 --> 00:00:08,05 is actually see record level data visualizations. 5 00:00:08,05 --> 00:00:11,01 So we've actually already done one of these, 6 00:00:11,01 --> 00:00:13,00 but I'm going to show you a few other options 7 00:00:13,00 --> 00:00:16,05 just so you can see all the variety that there is. 8 00:00:16,05 --> 00:00:18,00 As well, this will allow you 9 00:00:18,00 --> 00:00:19,09 to choose the best data visualization 10 00:00:19,09 --> 00:00:22,04 that fits your data and your audience. 11 00:00:22,04 --> 00:00:23,08 So let's get started. 12 00:00:23,08 --> 00:00:25,06 As we look at this data here, 13 00:00:25,06 --> 00:00:27,07 you can see that this is very similar 14 00:00:27,07 --> 00:00:29,04 to the data sets we've been working with. 15 00:00:29,04 --> 00:00:33,00 The main difference is I've moved progress outside of here. 16 00:00:33,00 --> 00:00:34,07 What are the ways we can visualize progress? 17 00:00:34,07 --> 00:00:36,09 Well, one of them we've actually already done. 18 00:00:36,09 --> 00:00:39,03 So that would be using conditional formatting, 19 00:00:39,03 --> 00:00:42,09 we can create data bars. 20 00:00:42,09 --> 00:00:45,01 My suggestion is don't go for the gradient fill. 21 00:00:45,01 --> 00:00:46,03 There's no reason to have a gradient. 22 00:00:46,03 --> 00:00:48,03 Visually, it doesn't tell us anything. 23 00:00:48,03 --> 00:00:50,09 Always go for a solid fill. 24 00:00:50,09 --> 00:00:52,08 That's the best way to go. 25 00:00:52,08 --> 00:00:55,02 Now, one of the things we notice with data bars 26 00:00:55,02 --> 00:00:57,03 is that usually it figures out the min 27 00:00:57,03 --> 00:00:59,01 and max of that bar for you. 28 00:00:59,01 --> 00:01:00,09 So the min is usually going to be zero 29 00:01:00,09 --> 00:01:02,07 and the max is usually going to be 100%, 30 00:01:02,07 --> 00:01:04,07 but sometimes that's not always what we want. 31 00:01:04,07 --> 00:01:08,02 So often we want some other options 32 00:01:08,02 --> 00:01:10,03 and I'm just going to show you some of those options here. 33 00:01:10,03 --> 00:01:11,04 So in cell J1, 34 00:01:11,04 --> 00:01:15,03 I'm going to type in in cell bar chart like this. 35 00:01:15,03 --> 00:01:16,02 And the way this works 36 00:01:16,02 --> 00:01:19,06 is we're going to use the repeat function, so Rept in quotes, 37 00:01:19,06 --> 00:01:21,00 we're going to put the pipe symbol. 38 00:01:21,00 --> 00:01:22,05 On my American style keyboard, 39 00:01:22,05 --> 00:01:24,06 the pipe symbol is going to be above the backslash, 40 00:01:24,06 --> 00:01:26,01 which is above the Enter key. 41 00:01:26,01 --> 00:01:27,08 Just double check on your keyboard. 42 00:01:27,08 --> 00:01:29,04 It might be in a different placement. 43 00:01:29,04 --> 00:01:30,03 We're going to surround it in quotes 44 00:01:30,03 --> 00:01:32,04 because it is a text type. 45 00:01:32,04 --> 00:01:35,01 So we want to repeat this pipe symbol 46 00:01:35,01 --> 00:01:36,03 a certain amount of times. 47 00:01:36,03 --> 00:01:38,07 We can select this progress right here. 48 00:01:38,07 --> 00:01:40,08 And we note that that says 51%, 49 00:01:40,08 --> 00:01:42,07 so that's going to be less than zero. 50 00:01:42,07 --> 00:01:45,07 We'll multiply it by 100 to make it more. 51 00:01:45,07 --> 00:01:46,06 And now you can see 52 00:01:46,06 --> 00:01:48,08 that we have these little in cell bar charts. 53 00:01:48,08 --> 00:01:50,04 I'll highlight the whole thing here. 54 00:01:50,04 --> 00:01:52,06 There's actually some fonts you can choose here 55 00:01:52,06 --> 00:01:54,03 that'll make this a little bit better looking. 56 00:01:54,03 --> 00:01:55,03 The first is Playbill. 57 00:01:55,03 --> 00:01:57,00 That's probably the most common one. 58 00:01:57,00 --> 00:02:00,02 You can use Playbill like that. 59 00:02:00,02 --> 00:02:02,07 The other one for your own records is Stencil. 60 00:02:02,07 --> 00:02:07,09 So Stencil also looks good. 61 00:02:07,09 --> 00:02:09,02 And the advantage of this one 62 00:02:09,02 --> 00:02:12,00 is that it doesn't always go to 100%. 63 00:02:12,00 --> 00:02:15,00 You do get a little bit more choice 64 00:02:15,00 --> 00:02:17,06 in terms of how to make it look, 65 00:02:17,06 --> 00:02:18,09 because since this is all a font, 66 00:02:18,09 --> 00:02:20,01 you can actually change the color 67 00:02:20,01 --> 00:02:21,06 and you can apply conditional formatting 68 00:02:21,06 --> 00:02:23,07 to the fonts colors as well. 69 00:02:23,07 --> 00:02:25,05 So you get a little bit more fun with that. 70 00:02:25,05 --> 00:02:29,03 Then the last one I want to show you is using sparklines. 71 00:02:29,03 --> 00:02:31,04 So with a sparkline, 72 00:02:31,04 --> 00:02:33,08 you could create a little box inside of each of these. 73 00:02:33,08 --> 00:02:35,06 That's going to be one bar chart 74 00:02:35,06 --> 00:02:38,06 and it will go from zero to 100%. 75 00:02:38,06 --> 00:02:42,00 So note that this progress is still 51% in here. 76 00:02:42,00 --> 00:02:43,08 We just have conditional formatting on top of it. 77 00:02:43,08 --> 00:02:45,01 So we're just going to actually use 78 00:02:45,01 --> 00:02:48,09 the same values here like that. 79 00:02:48,09 --> 00:02:51,09 Okay, so what I'm going to do is I'm going to insert a spark line. 80 00:02:51,09 --> 00:02:53,08 I'll go to insert, I'll click column 81 00:02:53,08 --> 00:02:56,00 and our location range, we're going to select this 82 00:02:56,00 --> 00:02:58,07 as our location range here. 83 00:02:58,07 --> 00:03:00,04 Hit Enter. 84 00:03:00,04 --> 00:03:02,00 Okay, so we've done this, 85 00:03:02,00 --> 00:03:03,06 but they all look like they're the same height. 86 00:03:03,06 --> 00:03:05,04 So what we need to do is we need to go to sparkline 87 00:03:05,04 --> 00:03:06,08 and we need to go to axis 88 00:03:06,08 --> 00:03:10,05 and where it says vertical axis minimum and maximum options. 89 00:03:10,05 --> 00:03:11,04 So for minimum, 90 00:03:11,04 --> 00:03:13,04 we're going to make that a custom value of zero. 91 00:03:13,04 --> 00:03:14,08 Just make sure that that's a zero. 92 00:03:14,08 --> 00:03:16,01 Looks good, no changes 93 00:03:16,01 --> 00:03:17,09 'cause it probably did that by default. 94 00:03:17,09 --> 00:03:20,01 And then we'll do for the maximum, 95 00:03:20,01 --> 00:03:21,09 we're going to click same for all sparklines. 96 00:03:21,09 --> 00:03:24,07 So then we can tighten this up a little bit like that. 97 00:03:24,07 --> 00:03:26,04 And putting the font in here 98 00:03:26,04 --> 00:03:28,00 just gives us something a little extra. 99 00:03:28,00 --> 00:03:29,09 So I'm going to make this smaller, 100 00:03:29,09 --> 00:03:32,05 maybe make it center, maybe make it above, 101 00:03:32,05 --> 00:03:33,08 and you can play around with these. 102 00:03:33,08 --> 00:03:35,09 Usually I like picking a sparkline color 103 00:03:35,09 --> 00:03:37,04 that's in the fourth row. 104 00:03:37,04 --> 00:03:39,06 I find that these are the lightest colors. 105 00:03:39,06 --> 00:03:40,05 So I did that. 106 00:03:40,05 --> 00:03:42,05 So I think that this actually does a pretty good job 107 00:03:42,05 --> 00:03:43,08 and you can compare this 108 00:03:43,08 --> 00:03:45,09 to the conditional formatting we did previously 109 00:03:45,09 --> 00:03:48,08 that used that pie chart Harvey Balls font. 110 00:03:48,08 --> 00:03:49,08 Well, now that we know 111 00:03:49,08 --> 00:03:52,09 how to visualize progress of our projects, 112 00:03:52,09 --> 00:03:55,07 both in a larger chart and also at the record level, 113 00:03:55,07 --> 00:03:57,08 let's talk about creating KPIs. 114 00:03:57,08 --> 00:03:59,08 Those are key performance indicators 115 00:03:59,08 --> 00:04:01,09 that are going to help us measure project success 116 00:04:01,09 --> 00:04:04,00 in the next video.