- [Instructor] Okay, so now we are ready
to create the elements and the layout for our dashboard.
And we're going to actually create two different things.
We'll do a Gantt chart and then we'll also do a pivot table.
So let's start with the Gantt chart.
I'm going to create a new worksheet tab here.
I'm just going to call it Gantt,
and then I'm going to zoom out.
So, in cell B2, I'm going to type in Start Year.
In cell B3, I'm going to do End Year.
I'm going to make both of these bold.
And then for the formulas here,
I am going to say Year, Min
and I'm going to go back to our Project Data over here.
Like that, highlight that whole thing,
close the parentheses.
And then here, we're going to do Year, Max
and I'm going to head over here to the Project Data.
In this case, we're going to do the end date
So the max of the end date.
So we get 2023 through 2025.
So what I'm going to do is I'm going to start first
by just creating a series of 12 cells.
I like doing this 'cause it just makes it
easier for me to plan.
So I have 1 through 12 here.
Alright, so this is going to be
where we're going to place our first year.
I'm going to use a merge and center here.
If you don't like merge and center, I understand.
But I don't think it's going to be a big deal here.
Just don't drag anything around.
Don't use any VBA.
All right, so I'm going to start this with 2023 right here.
I'll hit F4 to lock that in.
And then, I just like doing this
because it makes my life easier
when it comes to just laying things out.
So you hold Shift, select all these columns,
and then you can just resize here
and see if you can make this a little bit smaller like that.
Okay, so again, what I like to do
is I hit Ctrl-C on this, go to P over here.
Hit Ctrl-C there.
Move this over one more.
And then do a Ctrl-C there.
Then from within cell P here,
I'll do an equals and select D6+1.
And I'm just going to drag that over one as well.
I am going to highlight all the columns from here to the end
and just make it smaller.
Remember we don't have to make these too wide.
So, just keep making it smaller.
No big deal.
All right, so we have 2023, 2024, 2025 here.
Then what I like to do is I get rid of these numbers in here
'cause I'm going to replace it with a sequence formula.
I just like that 1 through 12 as a guide.
So in this case, what do we want to sequence?
So recall how we did this.
So in cell D7 we're going to say =SEQUENCE like this.
How many rows do we want to sequence?
Just one row.
And then what we want to do is
we want to take the difference between these two.
So that's going to be 2025 less 2023.
Add 1 back in, then multiply by 12 like that.
We'll do a close parentheses.
And this will automatically populate these numbers
to where we want them.
So next thing we want to add in is the dates down below.
And to do that, what we're going to do is
we're going to use the DATE function.
So I'm going to type in DATE here.
It's going to ask for the year.
In that case, we're just going to put in
the start year here and the month.
If I put in a 1 here, that's going to be January.
And the day we'll just keep as a 1.
So what's cool about this, as you might recall,
as I drag this out, the 13th month using 2023
as the start year is going to take us
to January of the next year.
So I'll highlight all of these dates.
I'm going to go from on the home tab.
I'll click the pop out.
I'll go to Custom.
And then, I'm going to change this date type here,
this type encoding to three m's.
That's going to give us our three letter
version of our months.
This is really just a way to refer to them
or a way to look them up.
But remember, the actual date value
is still inside of there.
So we can still use it.
And I'm just going to make everything a little bit bigger.
Okay, so now that we've done that,
let us go to cell B9.
I'll type in Projects here.
Ctrl-B to make that big.
And then over at the end over here,
starting in cell AO, we'll make column AN
just a little bit smaller here.
So starting in cell AO, we'll say Start Date,
End Date over here.
And then, I'm going to add
a little extra one here called Status,
and we're going to use that later on.
Okay, so let's just pull in all the important information.
In this case, I want to pull in all my projects
so I can type in ProjectData here,
left square open bracket, and I can select Project Name.
This is just going to pull the entire list in right there.
And assuming that everything stays in the same order,
so we don't actually need an XLOOKUP here.
What we're going to do is, I'm just going to say
ProjectData, Start Date here.
Like that, drag this to the right.
That's probably going to be the End Date over here.
And then, I'm going to pull in the ProjectData,
Project Status like that.
And I'll highlight these here.
Make sure that these are short dates.
Okay, so our Gantt chart is laid out.
Next thing we want to do is let's click on Safety Incidents
and let's insert a new pivot table on a new worksheet.
And then I'm going to go to Sheet3,
rename that to Pivot Table.
And I'm just going to get us set up again.
So I'm going to drop in Project Name over here.
We're going to take Severity
and we're going to drop that into the columns there.
So now we have Project Name and Severity.
And then, we just want a incident count.
In this case, you can just take Project Name
and re-drop it back into Values.
And here, it's going to give us the count.
So this is just going to give us
how many times it appears under a specific type.
Okay, with our data laid out in the next video,
we are going to create the Gantt chart in full
and we're going to add some interactivity to this pivot table.