馃攳
Project Plan in Excel with Gantt Chart (Plan, Actual & Progress in ONE VIEW) - YouTube
Channel: Leila Gharani
[0]
Today, we're going to take a
look at creating a Gantt chart
[3]
that shows actual and plan in one view.
[7]
We also get to see percentage completed,
[10]
you can see it as the green
bars inside the actual bar.
[14]
Now I have two videos on the Gantt chart.
[17]
The first one is called
"The Quick Gantt Chart."
[19]
It just shows one view, right?
[21]
It's very easy to make,
but you just have one view.
[24]
You can see when each task is starting
[26]
and how long the task takes.
[28]
Then came the second video that was called
[30]
"The Fully Featured Gantt Chart."
[32]
This shows you actual,
but it also shows you
[34]
the percentage completed.
[37]
But what you also get is the
ability to switch to plan.
[41]
So you can see two views in one chart.
[45]
Then I got the request
of creating a Gantt chart
[49]
that is basically based on this,
[51]
but it shows plan and actual as one view.
[54]
(hip hop drum beat)
[60]
So here I have the tasks I need
to complete for my project.
[63]
This part is the planning of my project,
[66]
so I've put a start date for each task,
[68]
and the number of working days
[70]
I think the task is going to take me.
[72]
The end date is automatically calculated
[74]
using the workday function,
[76]
and if you're curious to find
out more about how this works,
[79]
make sure you check out
the first two videos
[82]
on the Gantt chart where
I go in more detail
[85]
on the setup of this part.
[87]
Here is the actual data, so once we come
[90]
to actually do the task, we're
going to update the start date,
[92]
we're going to update the
number of working days.
[95]
End date is going to be
automatically calculated,
[97]
and we're also going
to manually update the
[99]
percentage completed for that task.
[102]
So for example, the second
task here is 80% complete.
[105]
Once we finalize the task, we're
going to update this to 100%.
[109]
Now we want to create a Gantt
chart that's going to show
[111]
all of this in one view.
[114]
And in addition to that,
we want to get a line
[116]
that shows today's date.
[119]
On this side right here, I've created
[120]
a data preparation table.
[122]
This is something I like to do
[124]
when I make nonstandard charts.
[126]
This way, I can manipulate
the data for the chart,
[130]
because it's not always the
case that I can take the data
[133]
as shown in here in the chart,
[136]
and especially for our Gantt chart,
[138]
we do need to make some adjustments.
[140]
For the task here, I've
just added it so I have
[143]
everything in one place.
[144]
That's just a direct
reference to this cell.
[147]
Let's just drag this down.
[148]
For the Start actual date, that's again,
[151]
a direct reference to my
start date under Actual.
[155]
So that's this one.
[156]
Let's push this down and
fill without formatting.
[161]
Number of days, that's the difference
[163]
between my end date and the start date.
[168]
And let's also send this down.
[170]
The number of days completed.
[173]
For this, if I have 100%, I
want to show the full number
[176]
of days here, so that's
just a basic calculation.
[180]
I'm going to take the number of days
[182]
and multiply it with this
percentage and send this down.
[187]
Start plan date, that's simple.
[190]
That's the start date for my plan.
[193]
Let's just highlight, F2, control enter.
[198]
The number of days, we have
a similar formula to before.
[202]
It's the end date minus
start date for plan,
[207]
and let's send this down.
[208]
Now let's start creating the chart.
[210]
But here's the thing: whenever you come
[212]
to creating nonstandard
charts, it's not always easy
[216]
to get them look like the
way you want them to look.
[218]
So if I highlight this and I go to Insert,
[221]
and I start off by inserting
a stacked bar chart,
[225]
it looks like this,
because Excel tries to take
[228]
a good guess at what you want where.
[230]
So it's plotting the dates
on the vertical axis,
[234]
but I actually want the dates in my chart.
[237]
You have different options here.
[239]
You can start off with an empty chart
[241]
and bring each series one-by-one,
[243]
or you can fool Excel a little bit
[246]
by telling it that this is not a date.
[248]
Instead, these are just numbers.
[250]
I'm going to change the formatting of this
[252]
to the general format,
and now I'm going to do
[255]
the same thing I did before.
[257]
Go to Insert, and insert
a stacked bar chart.
[262]
Now I've plotted the
dates inside the chart.
[265]
So now I'm going to go back
and turn these back to dates.
[270]
I have a stacked bar chart.
[271]
First stack is this date,
[273]
the second stack is the number of days.
[276]
This is actually the important
piece of the puzzle here.
[279]
This part is something I'm
going to hide from view,
[283]
so I'm going to make this invisible.
[285]
But first let's make some
other adjustments to this.
[287]
Let's switch the order
of this so that Sign-off
[291]
is on the bottom of the chart.
[293]
So let's bring up the properties.
[296]
Press control one or double-click
[298]
to bring up the chart options here.
[300]
To turn this around, put a tick mark
[302]
for Categories in reverse order,
[305]
so I'm right in the Axis Options here,
[307]
so like, Categories in reverse order.
[310]
Now the second thing I'm
going to do is to hide these.
[314]
So let's go to the options
here under Fill options
[317]
and select No Fill.
[319]
Now in addition to No Fill,
[320]
I want my data set to start from here.
[323]
There's no reason to have
this huge empty space here,
[326]
so let's go and change our minimum value
[331]
under the Axis Options, and let's fix that
[334]
to a later date.
[336]
So here we don't see them as dates,
[338]
we see the dates as numbers.
[340]
I'm just going to change
this to 43450 instead of 350,
[346]
so that's going to
bring this a bit closer,
[349]
so you can adjust this
depending on your data set.
[352]
So let's make this wider as well.
[354]
Now I don't need the legend
right here, let's delete.
[356]
I don't need the vertical
grid lines, let's delete.
[360]
And let's update the color of
the bars here to a gray color.
[365]
Okay, so that's the actual number
[369]
of days I need for each task.
[371]
Let's add in the number of days completed.
[374]
Now the method I used
in the previous video
[377]
is to use error bars for this.
[379]
That's the same method I'm
going to use right now.
[381]
I explained that a bit
more in the previous video,
[384]
so if you're interested to learn
[386]
a bit more on how they work, make sure you
[388]
check out the previous Gantt chart video.
[390]
So activate the error lines,
[392]
highlight the invisible series first,
[395]
go to the plus, and put a
tick mark for Error Bars here.
[399]
Now let's go to more options
[401]
and check out which one we're at.
[403]
It's the horizontal Error Bars,
[405]
and these are the ones we need.
[407]
We need the plus side only.
[410]
Our marker is right in the
middle, but we only want to show
[414]
the plus error bars.
[416]
We don't want to cap, and
we're going to change the value
[420]
to our number of days.
[423]
So for the Positive Error
Value, that's going to be
[426]
the days completed.
[428]
Click on OK.
[430]
Now let's go and make them more visible.
[432]
Change the line color to a green color,
[436]
and make it a lot thicker.
[438]
So I'll just increase this to six.
[441]
That looks good.
[442]
Okay, so that's the percentage completed.
[445]
Now let's just make sure that this works,
[448]
so this one is 20%.
[450]
If I change it to 50%, 50% completed,
[454]
80, 80% completed.
[457]
Okay, so now comes the part
where we're going to add
[461]
the plan data to this chart.
[463]
In order not to make it so confusing,
[466]
I'm going to add a thinner
line below the actual bars
[470]
to reflect the plan.
[471]
Now here comes the tricky part.
[474]
I need to plot a scatter plot here.
[477]
I'm not going to use a stacked bar chart.
[479]
A scatter plot, though,
needs values, numbers,
[483]
on the x and the y axis.
[485]
Now on the x axis, I have my dates,
[487]
but I need numbers on the y axis.
[490]
And the default for Excel is that,
[492]
you get the zero right here,
you get one right here,
[495]
two, three, four, and so on.
[497]
I want to make sure that
my scatter plot for plan
[502]
is just below this bar here.
[504]
So if this bar is plotted at around one,
[508]
and I want to plot a point below this bar,
[510]
I'm somewhere down here close to this zero
[513]
if we just look at the Sign-off task.
[516]
So if we assume that this point
would be something like 0.2,
[521]
and of course you can adjust it later
[523]
if it doesn't work properly,
but let's just try 0.2,
[527]
and then we're going to add a one to this,
[530]
and just pull this all the way up.
[532]
Okay, so we have our plan scatter plot
[536]
at one unit intervals.
[538]
The date should be the Start Plan date.
[541]
Next challenge is to bring this data
[544]
into the chart as a scatter plot.
[547]
But first, Excel doesn't know
that I want a scatter plot,
[549]
so I just have to bring the data in.
[551]
I'm going to go to Select Data.
[553]
Let's add, series name is Start Plan Date,
[558]
and series values, well,
to make my life easier,
[562]
I'm just going to add
the y values to these,
[565]
and the y values are the values
I just created right now.
[569]
Add these in, and click on OK, and okay.
[573]
Excel thinks that I want to add
[575]
another stack on top of this.
[577]
I don't, right?
[578]
I want to change this to a scatter plot.
[580]
So right mouse click, go to
Change Series Chart Type,
[584]
stay in the combo view and change
[586]
the plan date to a scatter plot.
[590]
Automatically, it's going to
activate the secondary axis,
[593]
and gives me automatic zero as
minimum and nine as maximum.
[598]
Where are our dots gone?
[600]
Well let's go and add in the x axis,
[604]
because we only gave it the y axis.
[607]
It doesn't know where to plot the x axis,
[610]
so let's go back to Select Data,
[612]
and go and edit our series,
[615]
add in our plan dates, and click on OK.
[619]
Now notice that I get the dot
right underneath that bar.
[624]
I don't want to see the dot itself,
[626]
I actually want to see the
number of days that I've planned.
[631]
The dot is the beginning of my line.
[635]
So again here, I'm going
to use the Error Bar trick,
[638]
and I'm going to activate
the Error Bars for the plan.
[641]
The bar that I don't need
are the vertical ones.
[644]
Just delete them.
[646]
I need the horizontal error
bars, so activate them.
[649]
Let's get rid of this box here.
[652]
Now let's update how we
want these lines to look.
[656]
Our dot is here, we just want
to show the plus side of it.
[659]
We don't want to cap, and
we want it to show a custom,
[664]
and that custom is the
number of days we've planned.
[669]
Click on OK, and that's the
correct length of the plan.
[673]
Let's just update the formatting of this.
[675]
I'm going to make it a
lighter blue, and let's change
[679]
the thickness to three, and Enter.
[682]
Now the one thing I don't
need are the original markers.
[685]
So let's just highlight
them, activate them,
[688]
go to Marker Options, and select None.
[691]
This way, I can see the
plan, and I can see actuals,
[695]
and I can see percentage
completed in one view.
[699]
So just quickly by looking at this,
[701]
I know that I completed the first task
[703]
much faster than I
planned, and it looks like
[706]
I'm going to complete the second task
[708]
faster than planned as well.
[710]
Now let's do our last task,
and that is to add a line
[715]
representing today's date.
[717]
So let's just remove this as well here.
[720]
The idea is that I'm going
to type today's date in.
[723]
You can use the today
formula, but let me just
[726]
type the date in this case manually in,
[728]
so let's say it's the 4th of April, 2019.
[733]
I want a line right where
the 4th of April is.
[737]
This way I can quickly tell
where I am on this chart,
[740]
and if I'm on track or not.
[742]
So in this case, what I'm going to do
[745]
is to also add a scatter plot.
[748]
Let's just repeat this date
for all these fields here,
[752]
and let's just add this information in.
[754]
So right mouse click, Select Data, add,
[759]
Series Name, let's go with today here,
[762]
the x value, so always look at your chart
[764]
to see what is plotted on the x side.
[766]
These are my dates, and that's going to be
[768]
these dates right here.
[769]
The series y values, I'm going to use
[772]
the y values I created here for budget,
[776]
and go with OK, and OK.
[779]
So I must have gotten
something added here.
[782]
I can't see it.
[784]
Whenever you run into something like this,
[786]
your points are invisible
because maybe they're white
[789]
or maybe they have no fill color,
[790]
go here under the drop-down,
and find your series from here.
[796]
So my series that I just
added is series today,
[798]
the dots are here,
they're just not visible,
[801]
and in this case, I don't necessarily
[803]
want to make them visible,
but what I want to do
[805]
is to make a line connecting these dots.
[809]
So I'm going to activate that solid line.
[811]
Let's just change the
color to this blue here.
[815]
Update the transparency, I'll go with 40%.
[819]
Thickness is fine.
[821]
Let's add an arrow type to
the top, and that's that.
[826]
That's the line reflecting today's date.
[828]
Let's just add a title to this.
[831]
Now, let's test a few things here.
[833]
Let's change the date to the 4th of March.
[837]
That shifts automatically.
[839]
What I'm going to do actually
is to add a label to this,
[842]
but only to the top parts.
[844]
I'm going to pick the top
data point, which means
[846]
click twice on that data
point, right mouse click,
[850]
add data label, then click and click again
[854]
to only select this data point,
[856]
press control one to bring up the options,
[858]
or double-click to bring up the options,
[861]
and add the series name to it.
[863]
I'm going to take away the y value
[865]
and this Leader Line options.
[868]
For Label Position, I'm
going to add that above.
[871]
I'll just tweak this a little bit.
[873]
I'm going to add a solid
border to this, the same color.
[877]
A solid fill that is
white, 50% transparent.
[883]
So just in case the
today's date is up here
[886]
that I can still see what's behind it.
[889]
You can also tweak this further.
[891]
If you don't want a
straight line like this,
[894]
you can update the dash type here.
[896]
You can do the same for
the data label here.
[899]
Just make sure that whenever
you're formatting it
[902]
that you double-click on that data label
[905]
so you only select that label,
[907]
and you don't activate all the
data labels for the series.
[911]
Okay, so let's update this again
[914]
just to make sure everything works out.
[916]
This moves up here.
[918]
Add a percentage completed for this one.
[921]
Change actuals, let's say
instead of 13 days here,
[925]
we realize that we actually need 15 days,
[929]
and these all update automatically.
[932]
So that's our fully-featured Gantt chart.
[934]
That shows the actual
and plan in one view.
[938]
If you like this video,
give it a thumbs up.
[941]
If you want to become
more advanced in Excel,
[944]
consider subscribing.
[946]
(acoustic guitar music)
Most Recent Videos:
You can go back to the homepage right here: Homepage