1 00:00:00,05 --> 00:00:02,00 - [Instructor] Okay, so now we are ready 2 00:00:02,00 --> 00:00:06,00 to create the elements and the layout for our dashboard. 3 00:00:06,00 --> 00:00:07,07 And we're going to actually create two different things. 4 00:00:07,07 --> 00:00:10,05 We'll do a Gantt chart and then we'll also do a pivot table. 5 00:00:10,05 --> 00:00:12,04 So let's start with the Gantt chart. 6 00:00:12,04 --> 00:00:14,08 I'm going to create a new worksheet tab here. 7 00:00:14,08 --> 00:00:16,09 I'm just going to call it Gantt, 8 00:00:16,09 --> 00:00:19,06 and then I'm going to zoom out. 9 00:00:19,06 --> 00:00:24,03 So, in cell B2, I'm going to type in Start Year. 10 00:00:24,03 --> 00:00:26,07 In cell B3, I'm going to do End Year. 11 00:00:26,07 --> 00:00:28,05 I'm going to make both of these bold. 12 00:00:28,05 --> 00:00:32,01 And then for the formulas here, 13 00:00:32,01 --> 00:00:35,08 I am going to say Year, Min 14 00:00:35,08 --> 00:00:38,07 and I'm going to go back to our Project Data over here. 15 00:00:38,07 --> 00:00:41,03 Like that, highlight that whole thing, 16 00:00:41,03 --> 00:00:42,03 close the parentheses. 17 00:00:42,03 --> 00:00:45,02 And then here, we're going to do Year, Max 18 00:00:45,02 --> 00:00:48,07 and I'm going to head over here to the Project Data. 19 00:00:48,07 --> 00:00:50,07 In this case, we're going to do the end date 20 00:00:50,07 --> 00:00:52,06 So the max of the end date. 21 00:00:52,06 --> 00:00:55,06 So we get 2023 through 2025. 22 00:00:55,06 --> 00:00:58,00 So what I'm going to do is I'm going to start first 23 00:00:58,00 --> 00:01:01,09 by just creating a series of 12 cells. 24 00:01:01,09 --> 00:01:03,08 I like doing this 'cause it just makes it 25 00:01:03,08 --> 00:01:05,05 easier for me to plan. 26 00:01:05,05 --> 00:01:07,04 So I have 1 through 12 here. 27 00:01:07,04 --> 00:01:08,05 Alright, so this is going to be 28 00:01:08,05 --> 00:01:09,08 where we're going to place our first year. 29 00:01:09,08 --> 00:01:11,06 I'm going to use a merge and center here. 30 00:01:11,06 --> 00:01:14,03 If you don't like merge and center, I understand. 31 00:01:14,03 --> 00:01:16,00 But I don't think it's going to be a big deal here. 32 00:01:16,00 --> 00:01:17,02 Just don't drag anything around. 33 00:01:17,02 --> 00:01:18,07 Don't use any VBA. 34 00:01:18,07 --> 00:01:22,00 All right, so I'm going to start this with 2023 right here. 35 00:01:22,00 --> 00:01:24,03 I'll hit F4 to lock that in. 36 00:01:24,03 --> 00:01:25,06 And then, I just like doing this 37 00:01:25,06 --> 00:01:28,00 because it makes my life easier 38 00:01:28,00 --> 00:01:30,06 when it comes to just laying things out. 39 00:01:30,06 --> 00:01:33,07 So you hold Shift, select all these columns, 40 00:01:33,07 --> 00:01:35,03 and then you can just resize here 41 00:01:35,03 --> 00:01:39,04 and see if you can make this a little bit smaller like that. 42 00:01:39,04 --> 00:01:41,02 Okay, so again, what I like to do 43 00:01:41,02 --> 00:01:45,00 is I hit Ctrl-C on this, go to P over here. 44 00:01:45,00 --> 00:01:46,03 Hit Ctrl-C there. 45 00:01:46,03 --> 00:01:48,08 Move this over one more. 46 00:01:48,08 --> 00:01:50,01 And then do a Ctrl-C there. 47 00:01:50,01 --> 00:01:52,09 Then from within cell P here, 48 00:01:52,09 --> 00:01:56,06 I'll do an equals and select D6+1. 49 00:01:56,06 --> 00:02:04,00 And I'm just going to drag that over one as well. 50 00:02:04,00 --> 00:02:08,03 I am going to highlight all the columns from here to the end 51 00:02:08,03 --> 00:02:11,05 and just make it smaller. 52 00:02:11,05 --> 00:02:13,03 Remember we don't have to make these too wide. 53 00:02:13,03 --> 00:02:15,02 So, just keep making it smaller. 54 00:02:15,02 --> 00:02:16,01 No big deal. 55 00:02:16,01 --> 00:02:20,08 All right, so we have 2023, 2024, 2025 here. 56 00:02:20,08 --> 00:02:23,01 Then what I like to do is I get rid of these numbers in here 57 00:02:23,01 --> 00:02:26,04 'cause I'm going to replace it with a sequence formula. 58 00:02:26,04 --> 00:02:28,09 I just like that 1 through 12 as a guide. 59 00:02:28,09 --> 00:02:31,09 So in this case, what do we want to sequence? 60 00:02:31,09 --> 00:02:33,00 So recall how we did this. 61 00:02:33,00 --> 00:02:38,03 So in cell D7 we're going to say =SEQUENCE like this. 62 00:02:38,03 --> 00:02:39,08 How many rows do we want to sequence? 63 00:02:39,08 --> 00:02:41,00 Just one row. 64 00:02:41,00 --> 00:02:43,00 And then what we want to do is 65 00:02:43,00 --> 00:02:46,01 we want to take the difference between these two. 66 00:02:46,01 --> 00:02:50,02 So that's going to be 2025 less 2023. 67 00:02:50,02 --> 00:02:55,06 Add 1 back in, then multiply by 12 like that. 68 00:02:55,06 --> 00:02:57,06 We'll do a close parentheses. 69 00:02:57,06 --> 00:03:02,01 And this will automatically populate these numbers 70 00:03:02,01 --> 00:03:06,01 to where we want them. 71 00:03:06,01 --> 00:03:11,01 So next thing we want to add in is the dates down below. 72 00:03:11,01 --> 00:03:13,03 And to do that, what we're going to do is 73 00:03:13,03 --> 00:03:15,03 we're going to use the DATE function. 74 00:03:15,03 --> 00:03:17,04 So I'm going to type in DATE here. 75 00:03:17,04 --> 00:03:19,03 It's going to ask for the year. 76 00:03:19,03 --> 00:03:20,07 In that case, we're just going to put in 77 00:03:20,07 --> 00:03:23,08 the start year here and the month. 78 00:03:23,08 --> 00:03:26,04 If I put in a 1 here, that's going to be January. 79 00:03:26,04 --> 00:03:28,01 And the day we'll just keep as a 1. 80 00:03:28,01 --> 00:03:30,04 So what's cool about this, as you might recall, 81 00:03:30,04 --> 00:03:34,08 as I drag this out, the 13th month using 2023 82 00:03:34,08 --> 00:03:36,04 as the start year is going to take us 83 00:03:36,04 --> 00:03:38,03 to January of the next year. 84 00:03:38,03 --> 00:03:39,09 So I'll highlight all of these dates. 85 00:03:39,09 --> 00:03:41,02 I'm going to go from on the home tab. 86 00:03:41,02 --> 00:03:42,05 I'll click the pop out. 87 00:03:42,05 --> 00:03:43,06 I'll go to Custom. 88 00:03:43,06 --> 00:03:47,00 And then, I'm going to change this date type here, 89 00:03:47,00 --> 00:03:49,08 this type encoding to three m's. 90 00:03:49,08 --> 00:03:53,01 That's going to give us our three letter 91 00:03:53,01 --> 00:03:55,05 version of our months. 92 00:03:55,05 --> 00:03:57,07 This is really just a way to refer to them 93 00:03:57,07 --> 00:03:58,08 or a way to look them up. 94 00:03:58,08 --> 00:04:00,07 But remember, the actual date value 95 00:04:00,07 --> 00:04:02,05 is still inside of there. 96 00:04:02,05 --> 00:04:05,03 So we can still use it. 97 00:04:05,03 --> 00:04:07,05 And I'm just going to make everything a little bit bigger. 98 00:04:07,05 --> 00:04:09,09 Okay, so now that we've done that, 99 00:04:09,09 --> 00:04:14,04 let us go to cell B9. 100 00:04:14,04 --> 00:04:16,04 I'll type in Projects here. 101 00:04:16,04 --> 00:04:18,02 Ctrl-B to make that big. 102 00:04:18,02 --> 00:04:20,09 And then over at the end over here, 103 00:04:20,09 --> 00:04:25,06 starting in cell AO, we'll make column AN 104 00:04:25,06 --> 00:04:27,05 just a little bit smaller here. 105 00:04:27,05 --> 00:04:30,08 So starting in cell AO, we'll say Start Date, 106 00:04:30,08 --> 00:04:32,03 End Date over here. 107 00:04:32,03 --> 00:04:33,03 And then, I'm going to add 108 00:04:33,03 --> 00:04:35,03 a little extra one here called Status, 109 00:04:35,03 --> 00:04:37,08 and we're going to use that later on. 110 00:04:37,08 --> 00:04:41,04 Okay, so let's just pull in all the important information. 111 00:04:41,04 --> 00:04:43,03 In this case, I want to pull in all my projects 112 00:04:43,03 --> 00:04:45,09 so I can type in ProjectData here, 113 00:04:45,09 --> 00:04:49,06 left square open bracket, and I can select Project Name. 114 00:04:49,06 --> 00:04:54,03 This is just going to pull the entire list in right there. 115 00:04:54,03 --> 00:04:58,05 And assuming that everything stays in the same order, 116 00:04:58,05 --> 00:05:01,02 so we don't actually need an XLOOKUP here. 117 00:05:01,02 --> 00:05:04,00 What we're going to do is, I'm just going to say 118 00:05:04,00 --> 00:05:07,08 ProjectData, Start Date here. 119 00:05:07,08 --> 00:05:09,06 Like that, drag this to the right. 120 00:05:09,06 --> 00:05:11,05 That's probably going to be the End Date over here. 121 00:05:11,05 --> 00:05:17,06 And then, I'm going to pull in the ProjectData, 122 00:05:17,06 --> 00:05:22,00 Project Status like that. 123 00:05:22,00 --> 00:05:24,04 And I'll highlight these here. 124 00:05:24,04 --> 00:05:33,00 Make sure that these are short dates. 125 00:05:33,00 --> 00:05:35,01 Okay, so our Gantt chart is laid out. 126 00:05:35,01 --> 00:05:37,08 Next thing we want to do is let's click on Safety Incidents 127 00:05:37,08 --> 00:05:44,01 and let's insert a new pivot table on a new worksheet. 128 00:05:44,01 --> 00:05:45,05 And then I'm going to go to Sheet3, 129 00:05:45,05 --> 00:05:51,06 rename that to Pivot Table. 130 00:05:51,06 --> 00:05:53,02 And I'm just going to get us set up again. 131 00:05:53,02 --> 00:05:55,06 So I'm going to drop in Project Name over here. 132 00:05:55,06 --> 00:05:56,07 We're going to take Severity 133 00:05:56,07 --> 00:05:58,05 and we're going to drop that into the columns there. 134 00:05:58,05 --> 00:06:00,06 So now we have Project Name and Severity. 135 00:06:00,06 --> 00:06:02,09 And then, we just want a incident count. 136 00:06:02,09 --> 00:06:04,08 In this case, you can just take Project Name 137 00:06:04,08 --> 00:06:06,08 and re-drop it back into Values. 138 00:06:06,08 --> 00:06:08,06 And here, it's going to give us the count. 139 00:06:08,06 --> 00:06:09,09 So this is just going to give us 140 00:06:09,09 --> 00:06:13,02 how many times it appears under a specific type. 141 00:06:13,02 --> 00:06:16,03 Okay, with our data laid out in the next video, 142 00:06:16,03 --> 00:06:18,05 we are going to create the Gantt chart in full 143 00:06:18,05 --> 00:06:22,00 and we're going to add some interactivity to this pivot table.