In this video, I'm going to show you how you can
create a very quick Gantt chart to visualize your project timetable. So, we're going to be taking
a look at the same project timetable that we've been looking at in the past few lectures, where
we took a look at the WORKDAY function as well as the NETWORKDAYS function. And now, let's say
your boss comes to you and says, "Could you quickly visualize this somehow and present this
in our meeting?" This is what you need to do. So, this is our data set, and based on this data set,
we're going to create our Gantt chart. There are different ways of doing this. You can use an
actual Excel chart, or you can use conditional formatting and use Excel cells and format them
to make them look like bars. You can use symbols and use the REPEAT function to get the bars for
the Gantt chart. My way of doing this for this lecture is to use an actual Excel chart for our
Gantt chart. So, let's think how we can do that. What I'm going to do is to use the start date for
my x-axis. I want my tasks on my y-axis and these number of days. I actually don't need the number
of working days, I need the number of full days including weekends and including holidays, so
basically the full difference between this and this because that way I can visually see how long
that task is going to take me. I can also include these working days as additional information
for my visualization for my chart. Okay, but I do need this, I do need this. Now, this
is going to define a difference between this and this is going to define the length of that task,
the length of these bars. Okay, so what type of chart should I use and how am I going to do this?
The first thing that occurs to me, because one of my favorite techniques is using the error bars.
That I can use error bars to get that difference, a number of working days, they can be my error
bars, and because they're going to be horizontal, right? They're going to go this way. I can only
have horizontal arrow bars in chart types where I have members on the x-axis. So, which chart type
is that? A scatterplot. Right, in a scatterplot, I have values under x as well as the y-axis, and
that way I can use error bars that are horizontal in that chart. Okay, so if I use a scatterplot, I
need numbers, right? So, I cannot put text on the y-axis. I need to put 1, 2, 3, 4. I actually need
the numbering of these tasks instead of the text itself. Okay, so that's something that I need.
And, I also need to calculate the full difference between the end date and the start date. So let's
just prepare this data for the chart. Just going to quickly add, just put number of days, and
I'm going to put task number on here. So, this, you can use the formula for this, you can use
the ROW function. If these are going to change, you might want to make it dynamic. For now,
I'm just going to fix it to test 1 to 9. Now, the number of days, that's going to be the
difference between my end date and my start date, and I need to format it as a number, just copying
the formatting of this over to this. Okay, so we can see, even though the number working days
is 8, the full number of days is 11 because that's how long my task is going to take. Okay, so now
for my chart, well, I want this one and the "eeks" I want to have my task number under Y, so I'm
going to hold down control because I have ranges that are not close together, highlight them,
let's go to insert and insert a scatter plot. Let's just make this more presentable. What I'm
going to do is copy these tasks and paste them down here. I'm going to be putting this one here
so that's the aim of putting this in front. Now we can see this is reversed because task number
one is up here and on my chart is down here, so I want to flip them over. Just going to
double-click this, go to the axis options here, and click values in reverse order, so that
switches them around. Now what I want to do is, ultimately, I want to place this nicely
on here. So let's take away the shape fill and let's take away that shape outline
as well. Okay, so these borders would look good if they go all the way through, so I'm
going to highlight this area, press Ctrl+1, and from the border, that's the gravy wand,
let's just add it to here, here and here. Now, I'm going to take away these gridlines
that we can see inside the chart. Okay, so what I need to achieve is that task one
is here and this one is here and the last one sits right on in front of that last task
here. Yes, oh, this looks about right. I can deal with the positioning later on as well. So
let me now remove the Y-axis for the X-axis, let's give it more breathing space. You don't
need a title now. We can see everything jumps here. I'm going to take away the line select no
line and from the axis options, I can take away the year. I don't need to see that it's just
getting too crowded by having that bear. So, I'm going to go to number. I don't want it
to link to the source formatting anymore. I'm going to go and overwrite that and just take away
the year, so I have month and the day only. Okay, so now they Sundays, I have the position of the
task for the start date but I need to find out how many days it should go so that's exactly
where the error line technique is going to be super useful. So I'm going to select the series
but for this series we're going to add the error bars to this. Now I don't need the vertical one
so I can quickly go there and press Delete. I only want the horizontal ones. Now when I click
on it, just click your way to get rid of that one. When I click on it, we can see the options
that I have for the horizontal error bars, which option here looks right? See, you this is my
point. Which one of these do I need the plus right because I have my point and I'm moving this way
from my point based on the number of days. I don't want a cap so the cap is that little line that you
can see at the end of the line. I don't want that. How much do I want my line to go across? So
how long should that line be? Well, that really depends on the number of days that I have between
the end date and the start date. So I'm going to go with custom, click on specify value and for
the Plus+ error bars, I'm going to highlight these ok. So now I get the different lengths here
and I'm going to turn them into bars. First off, let me just make it a lighter gray. Let's go
with this one and we're going to make it thicker. About looks fine, another, there is thicker. I
can see it. Can check the positioning of it. See that they exactly fit between that line. It takes
a bit of playing, but the best is to click ctrl and click on the edge of your charts. But you can
move it with the arrow keys. And the aim is to get the first one right. Once you do that, you have
control over the plot area as well as the chart area to make the rest fit. So I'm going to move
now that chart area, okay? And they look fine in this case. So I'm going to click on my original
dots and the only original scatter plot and I'm going to hide those points because no one needs
to see them. So I'm going to select no fill and no outline for that, okay? So now I have my tasks.
I have two starting days and I have how long each task takes, shown visually. Now what we can do
to make it more readable is to add our number of working days to this chart as well. So I'm going
to shift this a bit over and copy this and do a paste special. Just going to paste the links to
that so that they're linked to my original data source. Let's move this up, make it a bit smaller
and make it gray. And that looks good. Okay, so now let's just change something, make sure
everything works fine. So let's take a task, let's take the beginning task. We say, for
completion of design, we don't need eight working days. We need actually a lot more. We
need 15 working days for that in case of that expands that shows here. Our full number of
days gets calculated here and it's reflecting in our chart as well. Okay, so that's a quick
way that you can visualize your project time plan in Excel and present it in your meeting.
If you liked this video, don't forget to give it a thumbs up and if you like these type
of videos, don't forget to subscribe to my channel so that you can get notifications
when new videos like this one come out.
