How to Quickly Make a Gantt Chart in Excel
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 net workday 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's cells and format them, 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 a Gantt chart. So let's think how we can do that. What I'm going
to do is to use the start date. I want the start date on my x-axis. I do 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, the 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 error bars
in chart types where I have numbers on the x-axis. So which chart type is that? It's a scatter plot
right? In a scatter plot I have values on the 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 scatter plot I need numbers
right? So I cannot put text on the y, 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. I'm 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 a 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 task 1 to 9. Now the number of days that's just 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 of 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 on the x, I want to have my task number on the y. So I'm going to hold
down ctrl 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 my chart is down here so I want to flip them over. I'm just going to double click this,
go to the axis options here and click values in reverse order. 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. I'm going to highlight this area, press ctrl 1
and from the border, that's the gray we want, let's just add it to here, here and here. Now I'm
going to take away these grid lines that we can see inside the chart. Okay so what I need to achieve
is that that task one is here and this one is here and the last one sits right on in front of
that last task here. Okay so 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.
I don't need a title. Now we can see everything jumped. 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 there. 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 based on this 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. I'm going to select the series
and 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 one. Now when I click
on it, let me just click away 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 to 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 positive error bars I'm going to highlight these. Okay so now I get the different
lengths here and I'm going to turn them into bars. Well first off let me just make it a lighter gray.
Let's go with this one and we're going to make it thicker. Okay so that looks fine and now that
they're thicker I can see, I can check the positioning of it, see that they exactly fit
between that line. It takes a bit of playing. Best is to click control and click on the edge
of your chart so that 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 on my 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 the starting date 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 the 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. Okay so 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 like 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.