1 00:00:00,05 --> 00:00:02,01 - [Instructor] Okay, so let's build out 2 00:00:02,01 --> 00:00:04,05 our last features of our dashboard 3 00:00:04,05 --> 00:00:06,03 so that when people ask us questions about it, 4 00:00:06,03 --> 00:00:08,00 we can actually give them answers, 5 00:00:08,00 --> 00:00:12,00 and we can change how things look on the dashboard, 6 00:00:12,00 --> 00:00:14,08 and give the ability for people to interact 7 00:00:14,08 --> 00:00:16,05 and to really understand the data. 8 00:00:16,05 --> 00:00:18,06 So the first thing we want to do for our scenario analysis 9 00:00:18,06 --> 00:00:23,03 is I have selected the data in AO through AQ, 10 00:00:23,03 --> 00:00:25,02 so I'll hit Control + C there. 11 00:00:25,02 --> 00:00:28,01 Over here I'm going to do a paste value, 12 00:00:28,01 --> 00:00:29,08 so we're going to paste to complete. 13 00:00:29,08 --> 00:00:32,04 I probably did want to paste, not just the values, 14 00:00:32,04 --> 00:00:37,06 but also the number formatting with it. 15 00:00:37,06 --> 00:00:39,07 Okay, so one of these here 16 00:00:39,07 --> 00:00:41,06 is going to be our scenario, 17 00:00:41,06 --> 00:00:44,03 one of them is going to be our actuals. 18 00:00:44,03 --> 00:00:46,00 So I'm going to type I... 19 00:00:46,00 --> 00:00:47,07 I'm going to type in "SCENARIO" above here, 20 00:00:47,07 --> 00:00:49,04 again I did a merge and center. 21 00:00:49,04 --> 00:00:50,09 If you don't like merge and center, 22 00:00:50,09 --> 00:00:53,02 okay do center across, that's your life, 23 00:00:53,02 --> 00:00:54,09 I won't judge you, okay? 24 00:00:54,09 --> 00:00:57,07 But I do like merge and center, I'm sorry, 25 00:00:57,07 --> 00:01:00,02 I'm sorry, guys. 26 00:01:00,02 --> 00:01:01,06 I'm going to do Control + C here 27 00:01:01,06 --> 00:01:03,01 and then I'm going to do Control + V here, 28 00:01:03,01 --> 00:01:04,05 but I'm going to replace the "Scenario" 29 00:01:04,05 --> 00:01:07,01 with the word "Original." 30 00:01:07,01 --> 00:01:08,03 And it's always a good idea 31 00:01:08,03 --> 00:01:12,06 just to really pay attention to which one is which 32 00:01:12,06 --> 00:01:14,04 'cause I mess it up all the time 33 00:01:14,04 --> 00:01:15,05 when I do these formulas. 34 00:01:15,05 --> 00:01:17,04 So one of these, we want to change, 35 00:01:17,04 --> 00:01:19,00 one of them, we don't. 36 00:01:19,00 --> 00:01:21,06 So in this case, I already made this mistake, 37 00:01:21,06 --> 00:01:23,01 so I'm just going to keep this here. 38 00:01:23,01 --> 00:01:25,00 The original needs to have 39 00:01:25,00 --> 00:01:27,05 the formulas associated with it, okay? 40 00:01:27,05 --> 00:01:30,03 But the scenario is the one 41 00:01:30,03 --> 00:01:33,04 that we want to have as values 42 00:01:33,04 --> 00:01:35,02 because we can always change it back. 43 00:01:35,02 --> 00:01:37,04 Okay, so remember one of these gets hidden, 44 00:01:37,04 --> 00:01:39,02 the other one gets shown. 45 00:01:39,02 --> 00:01:41,08 So, we need to update the formula in here 46 00:01:41,08 --> 00:01:44,03 to reflect whether we are dealing 47 00:01:44,03 --> 00:01:46,07 with the original or the scenario. 48 00:01:46,07 --> 00:01:47,07 But to even do that, 49 00:01:47,07 --> 00:01:49,09 we have to create a mechanism to drive it. 50 00:01:49,09 --> 00:01:51,00 So, what I'm going to do is 51 00:01:51,00 --> 00:01:53,07 I'm going to put my cursor in cell B4, 52 00:01:53,07 --> 00:01:56,06 and I'm going to go to the Data tab over here, 53 00:01:56,06 --> 00:01:58,04 and under this drop down, 54 00:01:58,04 --> 00:02:00,05 I'm going to select Data Validation. 55 00:02:00,05 --> 00:02:02,02 Under Allow, I'm going to select List, 56 00:02:02,02 --> 00:02:03,07 and then under Source, 57 00:02:03,07 --> 00:02:05,04 I'm going to type in "Original," 58 00:02:05,04 --> 00:02:08,04 and then I'm going to type in "Scenario." 59 00:02:08,04 --> 00:02:12,01 So, if it's on Scenario, we want it to pull from here, 60 00:02:12,01 --> 00:02:14,08 if it's on Original, we want to pull from here. 61 00:02:14,08 --> 00:02:20,03 Let us now go and make that update 62 00:02:20,03 --> 00:02:21,08 to the formula. 63 00:02:21,08 --> 00:02:25,00 So, let's pop this open, 64 00:02:25,00 --> 00:02:27,00 and I'm going to show you how we do this. 65 00:02:27,00 --> 00:02:29,00 Okay, so first thing is, 66 00:02:29,00 --> 00:02:32,01 I'm going to do a Alt + Enter here, 67 00:02:32,01 --> 00:02:34,09 to go to the next line, so that I have a new line here, 68 00:02:34,09 --> 00:02:37,04 and I'm going to type in IsScenario, like that, 69 00:02:37,04 --> 00:02:39,03 so comma, and then what we're going to do 70 00:02:39,03 --> 00:02:42,05 is we're going to test if cell B4, over here, 71 00:02:42,05 --> 00:02:46,05 and I'm going to lock it in for good measure, 72 00:02:46,05 --> 00:02:49,01 and I'm going to type does that equal "Scenario". 73 00:02:49,01 --> 00:02:50,08 Okay, so if that's true, 74 00:02:50,08 --> 00:02:54,00 then that means we want to deal with the data 75 00:02:54,00 --> 00:02:56,09 that's on the Scenario side versus the Original side. 76 00:02:56,09 --> 00:03:00,02 So next, we have IsBetween here, 77 00:03:00,02 --> 00:03:01,08 I'm going to modify this. 78 00:03:01,08 --> 00:03:02,08 Watch how I modify this. 79 00:03:02,08 --> 00:03:04,05 So I'm going to highlight this whole thing here 80 00:03:04,05 --> 00:03:07,00 and I'm going to hit Control + X, 81 00:03:07,00 --> 00:03:08,04 but I am going to reuse it. 82 00:03:08,04 --> 00:03:10,07 So now I'm going to do a Command + Enter, 83 00:03:10,07 --> 00:03:13,02 one, two, three, four, 84 00:03:13,02 --> 00:03:14,05 one, two, three, four, 85 00:03:14,05 --> 00:03:15,07 So I'll do an IF here: 86 00:03:15,07 --> 00:03:18,07 IF (IsScenario), 87 00:03:18,07 --> 00:03:23,02 so if this thing is true here, we'll do a comma, 88 00:03:23,02 --> 00:03:26,06 and in this case, I'm going to say, AND, 89 00:03:26,06 --> 00:03:28,07 and what I want to do is I want to test 90 00:03:28,07 --> 00:03:33,09 if this value here 91 00:03:33,09 --> 00:03:37,04 is greater than or equal to, 92 00:03:37,04 --> 00:03:39,01 and I'm going to use, 93 00:03:39,01 --> 00:03:40,04 since we're doing Scenario, 94 00:03:40,04 --> 00:03:44,08 we want to use this one over here. 95 00:03:44,08 --> 00:03:49,02 So let's hit F4 to lock that in correctly. 96 00:03:49,02 --> 00:03:51,00 Make sure that the dollar sign 97 00:03:51,00 --> 00:03:52,00 is on the last part of this, 98 00:03:52,00 --> 00:03:53,07 and of course, if this happens, 99 00:03:53,07 --> 00:03:55,00 like something pops back up, 100 00:03:55,00 --> 00:03:57,05 just hit Alt + Enter and move that out of the way. 101 00:03:57,05 --> 00:04:00,06 Okay, so now we're going to hit comma on this again, 102 00:04:00,06 --> 00:04:06,00 we want to test if D8, 103 00:04:06,00 --> 00:04:11,07 one, two, is less than or equal to 104 00:04:11,07 --> 00:04:15,02 this value right here, 105 00:04:15,02 --> 00:04:18,09 and make sure to cycle through it correctly, like that. 106 00:04:18,09 --> 00:04:21,07 Okay, so if that test is correct, 107 00:04:21,07 --> 00:04:22,08 we'll know it's between. 108 00:04:22,08 --> 00:04:24,06 Now I'm going to hit Control + V here 109 00:04:24,06 --> 00:04:25,05 'cause I'm just going to reuse 110 00:04:25,05 --> 00:04:27,01 the other one that we had. 111 00:04:27,01 --> 00:04:29,00 Close this off right here. 112 00:04:29,00 --> 00:04:30,07 Okay, so look at this, right? 113 00:04:30,07 --> 00:04:32,06 If it's Scenario, we're going to pull from here. 114 00:04:32,06 --> 00:04:36,06 If it's not this Scenario, we're going to pull from here. 115 00:04:36,06 --> 00:04:37,08 Pretty easy, right? 116 00:04:37,08 --> 00:04:40,06 Okay, so now I'm going to put ProjectCode back 117 00:04:40,06 --> 00:04:43,02 in its place, but we are going to have to do 118 00:04:43,02 --> 00:04:44,02 an additional test. 119 00:04:44,02 --> 00:04:47,01 So, I'm going to do another Alt + Enter, 120 00:04:47,01 --> 00:04:50,02 and move this over here. 121 00:04:50,02 --> 00:04:55,06 And what I'm going to say is: IF (IsScenario), 122 00:04:55,06 --> 00:04:57,02 do a comma here. 123 00:04:57,02 --> 00:04:58,08 Now, if it's this Scenario 124 00:04:58,08 --> 00:05:02,08 we want it to pull from this set of data here, 125 00:05:02,08 --> 00:05:04,06 we want to evaluate AU. 126 00:05:04,06 --> 00:05:06,01 So what you can do is... 127 00:05:06,01 --> 00:05:08,02 I'm a big believer in just reusing stuff. 128 00:05:08,02 --> 00:05:12,00 So I'm going to go one, two, three, four, 129 00:05:12,00 --> 00:05:14,01 one, two, three, four, okay. 130 00:05:14,01 --> 00:05:16,05 And then also I have to move these in too, 131 00:05:16,05 --> 00:05:21,08 just for my own sanity. 132 00:05:21,08 --> 00:05:24,09 Okay, and then let's just keep doing that. 133 00:05:24,09 --> 00:05:26,03 This is if it's not this scenario. 134 00:05:26,03 --> 00:05:28,00 So I'll hit Control + C here, 135 00:05:28,00 --> 00:05:31,04 then I'm going to do a Command + Enter, Control + V. 136 00:05:31,04 --> 00:05:33,05 Don't worry, if you're following along here, 137 00:05:33,05 --> 00:05:35,05 just make sure that your formula looks like mine 138 00:05:35,05 --> 00:05:36,04 at the end of the day. 139 00:05:36,04 --> 00:05:37,03 But I do want to show you 140 00:05:37,03 --> 00:05:38,08 how to build these things quickly 141 00:05:38,08 --> 00:05:40,08 because this actually does make a difference, all right? 142 00:05:40,08 --> 00:05:42,02 So if it's the scenario, 143 00:05:42,02 --> 00:05:44,09 we want to be testing from AU. 144 00:05:44,09 --> 00:05:49,02 So I'm going to replace the AQ here with a U, like that. 145 00:05:49,02 --> 00:05:55,04 I'll just keep going in and replacing that. 146 00:05:55,04 --> 00:05:59,07 And we can remove this here. 147 00:05:59,07 --> 00:06:01,07 So, in this case, what I'd want to do 148 00:06:01,07 --> 00:06:04,01 is I want to add another parentheses and do a comma. 149 00:06:04,01 --> 00:06:06,01 Okay, so let's take a look at this. 150 00:06:06,01 --> 00:06:07,04 If it IsScenario, 151 00:06:07,04 --> 00:06:09,09 that means B4 = "Scenario", that becomes a true. 152 00:06:09,09 --> 00:06:12,06 So, now we want to find out is it between? 153 00:06:12,06 --> 00:06:15,02 So, if it is the scenario, we want to test, 154 00:06:15,02 --> 00:06:20,04 pulling from the scenario start and end dates. 155 00:06:20,04 --> 00:06:23,06 So, we can see AS10, AT10, that looks good 156 00:06:23,06 --> 00:06:26,05 and otherwise, we want to pull from the other side. 157 00:06:26,05 --> 00:06:28,02 All right, so now we want to get the project code. 158 00:06:28,02 --> 00:06:29,07 So, if it's the scenario, 159 00:06:29,07 --> 00:06:30,09 if we're dealing with scenario, 160 00:06:30,09 --> 00:06:32,00 we want to make sure we're pulling 161 00:06:32,00 --> 00:06:33,06 from the correct project codes. 162 00:06:33,06 --> 00:06:36,08 Otherwise, if not, we want to pull 163 00:06:36,08 --> 00:06:38,07 from a different location. 164 00:06:38,07 --> 00:06:41,01 All right, so I'm going to hit Enter here, 165 00:06:41,01 --> 00:06:42,09 and we'll close this up. 166 00:06:42,09 --> 00:06:46,01 I'm going to take this here, drag it over here, 167 00:06:46,01 --> 00:06:47,07 double click it the way down. 168 00:06:47,07 --> 00:06:49,02 It shouldn't matter yet, right, 169 00:06:49,02 --> 00:06:50,02 it shouldn't matter yet. 170 00:06:50,02 --> 00:06:54,08 But if I go here and I change this to 4/1, like that. 171 00:06:54,08 --> 00:06:56,06 Okay, so we see that updates. 172 00:06:56,06 --> 00:07:01,04 Now, what if I change this to Ongoing, like that? 173 00:07:01,04 --> 00:07:02,08 All right, so we see that updates, 174 00:07:02,08 --> 00:07:03,08 and what you can always do here 175 00:07:03,08 --> 00:07:06,04 is you can go and highlight all of this, 176 00:07:06,04 --> 00:07:11,06 and go to Data, do Data Validation here, List, 177 00:07:11,06 --> 00:07:13,03 you can always type these in 178 00:07:13,03 --> 00:07:15,00 or put them somewhere on the spreadsheet 179 00:07:15,00 --> 00:07:16,08 and do a Source to it. 180 00:07:16,08 --> 00:07:19,09 I like making things easy for example purposes, 181 00:07:19,09 --> 00:07:21,09 so I'm going to just keep it like this, 182 00:07:21,09 --> 00:07:25,07 and of course you can just change it and so forth. 183 00:07:25,07 --> 00:07:27,09 Okay, so as we make those changes, 184 00:07:27,09 --> 00:07:29,02 we definitely want to see 185 00:07:29,02 --> 00:07:31,04 some sort of visualization at the bottom. 186 00:07:31,04 --> 00:07:34,07 So I'm going to do an =Sum here. 187 00:07:34,07 --> 00:07:40,05 Remember, this is summing 3, 2, 1, 188 00:07:40,05 --> 00:07:41,04 like that. 189 00:07:41,04 --> 00:07:45,01 You can decide for yourself exactly what that means. 190 00:07:45,01 --> 00:07:48,00 I'm going to apply a new conditional format here. 191 00:07:48,00 --> 00:07:49,08 Let's see if the color scales, I like that. 192 00:07:49,08 --> 00:07:51,05 So in this case 193 00:07:51,05 --> 00:07:53,08 what we're really seeing is that 194 00:07:53,08 --> 00:07:55,02 completed and ongoing projects 195 00:07:55,02 --> 00:07:56,03 are going to have a bigger color 196 00:07:56,03 --> 00:07:58,00 'cause they have a higher number encoding. 197 00:07:58,00 --> 00:08:00,00 Again, you can decide if that's what you really want, 198 00:08:00,00 --> 00:08:01,09 I can see arguments being the other way. 199 00:08:01,09 --> 00:08:03,06 I'm actually going to do this red here, 200 00:08:03,06 --> 00:08:06,02 whereas the ones that I have not started 201 00:08:06,02 --> 00:08:10,05 are going to have a lesser encoding associated with them. 202 00:08:10,05 --> 00:08:13,05 So I'm going to close that up here. 203 00:08:13,05 --> 00:08:15,06 Now the other thing is I'm going to go 204 00:08:15,06 --> 00:08:17,07 to Original over here, 205 00:08:17,07 --> 00:08:21,00 and I'll hit the Data tab and click Group 206 00:08:21,00 --> 00:08:23,09 just so it's not in the way. 207 00:08:23,09 --> 00:08:25,05 You could of course make this smaller 208 00:08:25,05 --> 00:08:26,08 if you want as well. 209 00:08:26,08 --> 00:08:28,02 Okay, so last thing is, 210 00:08:28,02 --> 00:08:30,01 let's hit our pivot table here. 211 00:08:30,01 --> 00:08:32,04 So I just want to give you one last bit of interactivity 212 00:08:32,04 --> 00:08:33,07 that is specific to pivot tables. 213 00:08:33,07 --> 00:08:35,09 So, with this pivot table selected, 214 00:08:35,09 --> 00:08:37,03 I can go to the Insert tab 215 00:08:37,03 --> 00:08:38,03 and I have these two choices, 216 00:08:38,03 --> 00:08:39,06 I have a Slicer and a Timeline. 217 00:08:39,06 --> 00:08:42,00 So, let's hit the slicer first. 218 00:08:42,00 --> 00:08:46,02 We can click on Incident Type here, 219 00:08:46,02 --> 00:08:49,09 and this will just allow us to choose 220 00:08:49,09 --> 00:08:51,07 what kind of type of incident we have. 221 00:08:51,07 --> 00:08:53,05 And then the other one I want to show you here, 222 00:08:53,05 --> 00:08:54,05 is I'm going to Insert, 223 00:08:54,05 --> 00:08:56,01 and I'm going to click Timeline. 224 00:08:56,01 --> 00:08:57,05 This only works on pivot tables. 225 00:08:57,05 --> 00:08:59,05 So, you'll note that the only thing 226 00:08:59,05 --> 00:09:02,04 it gives me a choice for is a date type, 227 00:09:02,04 --> 00:09:03,09 because it only works on dates. 228 00:09:03,09 --> 00:09:05,03 So timelines are really cool 229 00:09:05,03 --> 00:09:10,09 because they allow us to slice on dates, like that. 230 00:09:10,09 --> 00:09:13,09 So now, I can take a look at my different projects, 231 00:09:13,09 --> 00:09:15,07 take a look at the average SPI, CPI, 232 00:09:15,07 --> 00:09:16,08 and Total Safety Count. 233 00:09:16,08 --> 00:09:17,07 And then I can also look 234 00:09:17,07 --> 00:09:21,08 at my safety issues per project and per date. 235 00:09:21,08 --> 00:09:25,04 And with that, we have taken our project management data 236 00:09:25,04 --> 00:09:27,01 and we've added so much more life to it 237 00:09:27,01 --> 00:09:29,01 and so much more data visualization to it. 238 00:09:29,01 --> 00:09:30,03 I can't wait to see 239 00:09:30,03 --> 00:09:32,04 how you're going to use this information 240 00:09:32,04 --> 00:09:34,00 in your own work.