- [Instructor] So this is a Gantt chart
that I've built in Excel.
Notice that we have a bar chart here
that effectively shows us the duration
of a project when it starts and ends,
as well as a visualization at the bottom,
which shows us the highest concentrations
of projects at a single point.
Now, this may look like a lot of work,
but don't worry, I'm going to show you exactly how to do it.
If you want to familiarize yourself with the data,
you can go ahead and click Start and see the data over here.
So now what I'm going to have you do
is just create a new sheet.
You can call this Gantt if you'd like
and make sure that you spell Gantt correctly.
So I'm going to zoom out here
to give myself a little bit more space.
In cell B2, I'm going to type in Start Year.
In cell B3, I'm going to type in End Year.
Okay, so in cell C2, we want to grab the Starting Year
for all of our projects.
So I'm going to type in Year and then I'm going to type in Min
and I'm going to highlight from on this Start over here,
on the Start worksheet tab,
I'm going to highlight this entire thing.
So ProjectData, Start Date, close parentheses,
close parentheses, hit Enter.
That's 2022.
We're going to do the same over here.
We'll do Year Max and then go back to Start.
And then we're going to do the End Dates over here
like this, close, close, Enter.
Okay, so we got our Starting Year and our Ending Year.
Now, in cell D5,
I'm going to type in a 1 here and then I'll type in a 2.
And this is just to kind of help me understand
how far we want to take this thing.
So I have 36 periods in all.
So I'm just going to take a look at 12 right here.
And I'm just going to use that as a guide.
Above here, I am going to use merge and center.
Please do not attack me.
I don't think it's that bad.
All right, so I did merge and center here.
D4 through D0
I'm going to do an = and I'm going to do 2022 here.
And that's just going to give us our year header.
I'm going to highlight all of these
and let's just bring 'em in a little closer,
just a little bit closer, like that.
Okay, so we have our year header here.
The way I like to do this, I keep it simple.
I'll do a Control + C, I'll do Control + V right here,
and then I'm going to do a Control + V on that one over there.
And all we have to do here
is actually in this box, I'm going to say =.
And then I'll select the 2022 and do a plus one.
And the reason I'm doing this is, if you want to add more,
this is going to make your life a lot easier.
We like to keep things very formulaic
so that all we have to do is just make a few updates
to get to where we want.
Okay, so now I want to add the next year in.
I'm going to click on this big box over here.
Click 2023 and do a plus one.
Of course, you could have just dragged it over.
That would've been the smarter move.
But you know, all of this is just to make your life
a little bit easier.
Okay, so I'm going to highlight this whole thing here.
Let me do that again.
I'll just try to make everything kind of the same size.
Bring it in a little tighter so we can actually visualize.
All right, so you could either take this
and just drive it all the way through to get to 36 points.
That's one way to do it.
The way I have done it,
because I would like
to make things a little bit formulaic,
is I use the SEQUENCE function.
So I'm going to type in SEQUENCE here.
We're going to SEQUENCE 1 row,
and I'll do an open parentheses for the calculation here.
So what we're going to do is we're going to take this one here,
that's our End Year,
less our Start Year plus one,
and then multiplied by 12.
So that automatically populates 36.
Again, what I like about this,
let's say we wanted to expand it,
we could expand it.
I mean, you would have to kind of do the same thing here,
but I think you got that.
Okay, so the next thing we want to do
is bring in the months below this
so we understand the months that we're looking at.
So I'm going to do a Date function here.
So I'll do Date, I'm going to select C2.
We will hit F4 to lock it in.
What month are we interested in?
Well, we're going to be interested in the month
that is given by this number right here.
And then we'll just do the day here.
Now, what's fun about this, right,
is that it doesn't matter if we put in
the 13th month here, it's still going to work.
So I'll show that to you.
I'm going to take this and drag it all the way over here.
I'll make just this one bigger
just so we can see that it looks good.
Okay, that looks good.
Let me just show you this on the 13.
You can see that it actually just grows by a year,
which is really cool.
It makes our life really easy.
So I'm not going to like these hash marks here.
What I want to do is I'm going to do this pop-out here,
and then I'm going to go to Custom.
I only want it to show the month,
even though I'm treating it as a date.
So I'll just do MMM,
that's going to give me the three-digit month.
And then, I'll just make it kind of smaller like that.
Don't worry.
We're going to work on formatting everything
at the end of this.
So here, I'll do a Project Name,
Do Control + B here,
make it bold and let's do our projects.
So in this case, I am going to go to the start over here
and I think I just pull in the whole thing, right?
You just pull this whole thing in like that.
Boom, all right?
That comes in.
I'll just double click that, make that a little bit bigger.
All right, so before I populate my Gantt chart,
what I want to do is I want to bring in the Start Date
and End Date over here, not just for the user,
but it's going to make my life easier.
So we'll do Start Date, End Date, like that,
Control + B, make it bold.
And what I'm going to do is I'm going to actually look up
the Start Date and the End Dates from this project name.
So I'll do XLOOKUP.
We're going to search for this value right here,
comma, what's our lookup array?
It's going to be back on our table
over here under project name.
I just selected the top, Control + Shift + Down.
Note how beautiful that is.
It just brings in both of them.
And then what we want to do is return
the Start Date and the End Date.
So you can just take this whole thing
and highlight it here like that,
close the parentheses, and I'm going to take this
and drag it all the way down.
And if you get that N/A, it's 'cause you dragged it too far.
Okay, so with these set up,
let's change the dates to do a short date.
Make our life a little bit easier.
Okay, so next things
we want to do here are actually populate this entire thing.
So you may be wondering how does it work?
Well, we're going to actually just use conditional formatting.
So if it's a one,
we want it to actually show a color in that cell.
If it's a zero, we don't want it to do that.
So I'm going to use a really interesting formula
called the IFS formula to do that.
And that's going to keep us
from having to use nested IFs all the time
and I find it a lot easier to use.
So I'm going to type in IFS here.
That's the IFS formula.
And the way it works is it tests if something is true
and if it is true, it does that thing.
If not, it just tests for another thing being true.
And if it that's true,
it does that thing and it just continues to go
until it runs out of things to test.
So let's go ahead and do this.
So I'm going to type in IFS,
and then, on your keyboard, you can hit Alt + Enter.
Go to the next line, one, two, three, four to indent.
Now, what we want to do is test is this date right here
in between Start Date and End Date,
'cause that's going to tell us
that we actually want to put a one there.
So I'm going to type in AND here.
And what we're going to test is
if this cell right here
is greater than this cell and less than this cell.
So since we're going to be dragging this across,
but we want it to stay in its current row,
we will lock in the six, but we're going to leave that D free,
and then we're going to do >=
and I'm going to select cell A07 here.
And then in this case, since we want it to go down,
but we want to lock in the column,
we're going to keep that A0 locked and that seven free.
We're going to do the same thing here.
Is this less than or equal to this one here?
And we'll structure it the same way.
So if it is, we want it to return a one, Alt + Enter.
If not, we want it to return a zero.
Now, again, you could put this in all one IF formula,
but what I like to do is to separate
these out so I can think about it correctly.
So if you needed,
you could actually put more conditions in here,
but in this case, we don't need to.
But what I like to do is,
since we want it to return a zero
if it doesn't satisfy this first condition,
but we need to have this logical test return it TRUE
I'm just going to type in a TRUE here,
and this may be a little confusing,
but if you think about it, this will always work
that if this doesn't hit this condition
and you put a TRUE here, we'll always hit that condition.
So what you can do is you can stack all these IFs up
and then for your last else, if nothing else is true,
you just put a TRUE in there and it's going to do that thing.
And I like doing that 'cause I think it just makes it
a lot easier to read and understand.
So I'm going to hit Enter here.
You can see that's a zero.
So let's take this all the way out here,
see if it works.
Okay, I like how it's looking.
So double click that the way down.
Okay, so we're getting somewhere.
So next thing I want to do
is I'm going to go to Conditional Formatting here
and I'm going to apply a new rule.
So I'm going to click on Format only cells that contain,
and the cell value should be equal to a one.
That, we'll go to format and we'll do a border.
I like to do a solid border.
I'll do this blue fill like that.
Hit OK, hit OK.
All right, so now we're getting somewhere.
Now, the thing is, maybe we don't want to see
all these ones and zeros.
So with all of this highlighted,
you're going to go on the Home tab,
pop out that number, hit Custom,
and then you can get rid of the numbers here
just by typing one semicolon like that.
All right, I like where we're going, I like how this looks.
All right, so how do we build
that last part on the bottom here?
What I'm going to do is I'm going to do an =
and I'll do SUM.
So note that even though these are zeros
and ones that have been hidden,
there's still zeros and ones behind the scenes.
So we're going to make that a SUM.
And you can see that here it's showing the Custom format.
So I'm going to switch that back to General
just so I can see it,
but we're going to turn that back to Custom in a second.
So now, that we've added that,
we'll apply a new conditional formatting.
I'm going to click New rule here.
And I like this Format all cells based on their value.
So it gives us this two-color scale.
The problem is the lowest value gives us
the most intense color and the highest value
gives us the least intense color.
So we want to switch that up.
I'm going to make this the least intense color,
and then I'm going to use that same column for reference
and make this the most intense color.
So I'll go ahead and hit Enter on that.
And then in this case, you can pop this back out,
go to Custom, drop in that semicolon,
and you can see that it has now disappeared,
or at least the numbers have now disappeared.
All right, so let's talk about
how to make this a little bit better
for presentation purposes.
I am going to make these all black like that,
and I'm going to highlight this entire thing like this
and just do a outside border.
And I like to just separate these as well.
So let's see.
I'll make that a border just to carve off our years.
And this one actually shouldn't have anything in it,
so I'm going to hit Delete on that.
I'm going to right click.
Just a quick way to delete it, okay.
And then I do want to make sure
that that has a nice right border.
And then I'm going to go to View and turn off grid lines.
All right, so one last thing you might consider
doing here is going to Start and let's sort it, right?
Maybe we should sort it by the Start Date.
So let's do oldest and newest, like that,
and that way, it looks sorted on here.
All right, so that was quite a bit of work,
but now that you know how to create a Gantt chart in Excel,
you can actually build one rather quickly.
And the way that we've built it formulaically
will allow you to add on things to it
so that you can keep growing to reflect
the changing nature of your projects.