In this video I'm going to show you how you can create a fully featured gap chart and be fully featured.
I mean you can do two more things than the quick Gantt chart I showed you in a previous lecture.
One is that we're going to see the percentage completion so we're going to have the bars be conditionally
formatted depending on how much of that task we've completed.
And two is that as we're getting into the project we're going to end up changing things around for some
test we might need longer for some task we might end up using less time and for some task we might end
up shifting the data.
So we always want to keep an eye on the original time plan and be able to compare that to our actual
tape.
So it should allow us to select between the different two scenarios.
The way I'm going to create this is to use an actual Excel chart.
Now in a previous lecture I showed you how to create a quick Gantt Chart.
So that was really a case where you've just come up with your project plan and you quickly want to present
it in a meeting so you haven't actually started with a project you just want to have an idea of the
timeline of the number working days.
Each task takes.
And so on.
But once you start working on the project you actually want to show how you end up changing things around
and which of the tasks you've already completed.
To make the quick Gantt chart I use a scatterplot to do that and I use the airborne technique to show
the number of days that each of these tasks is going to take me.
And then because I use the scatterplot I actually put in the tasks themselves in Excel cells and then
they positioned this chart right in front of the tasks for the fully featured Gantt Chart.
I'm going to use a combination of two taking.
I'm going to use the Arab or technique to show the percentage completion but I'm going to use a stacked
bar chart to show the length of the actual tasks themselves.
So let's see how we can set that up here and have the list of my original tasks.
Now that's going to be the pond version just going to copy and paste this here.
And here I'm going to keep track of the actual times each of us is going to take me.
Originally I use the Workday function to calculate the end state based on the number of working days
which excluded weekends and holidays that I have listed right here and then leave that same logic for
actuals.
But what I want to add on is to be able to change the view of the chart so that if the user selects
Puan they see the planned Gannes chart and if they select actual They see the actual Gantt Chart.
So here I want to introduce a data validation where the user can select the scenario.
I'm going to use list here.
I'm just going to type in actual and plan
Okay.
So let's just format this accordingly just so that they know they can select from here.
This is plan this is actual.
I'm going to press control one to format this as well and use Center across selection while we're here
let's just add a bottom border to this as well because the plan is something that I'm not going to change.
It's going to be fixed.
Let's just make it green.
This is what I'm going to change.
So just to be able to see when we switch things around let's change the number of working days.
In actual 2:5 this change this one to 10.
And instead of starting under 13 Let's start on the 10th here.
So no because my chart needs to change its view.
I am going to add a data preparation table right here.
Just put these out of you here is going to be my data prep table.
I needs to show the start date and not the number of working days though I want to show the number of
full days I want to include weekends and include holidays because that's going to be the length of my
task.
But my start date is going to depend on what the user wants to see today when you see actual.
Or do they want to see Plan B if function can work great here.
If they said I'm going to fix it with four key he calls plan and it's not case sensitive then it should
show this.
Otherwise it should show this the
for the number of days we're going to do a similar thing.
So if this one let's fix equals plan then I don't want to show the number of working days.
I want to say the end date minus the start date otherwise it would be the actual end date minus the
actual start date.
OK so here I selected actual Even though the number of working days is five.
The actual full number of days is eight.
So let's start to create our Gantt Chart.
I'm going to highlight this.
I'm going to hold down the control key and I'm going to highlight this and this.
Now notice I haven't shifted this to date format yet.
That's for a good reason.
I'm going to show you quickly if I go to insert and select the stacked bar chart you can see it looks
right.
It has my tasks on one side and the dates on the other side but if I change this to date format
and do the same thing
you can see it gets a bit confused.
It's actually putting my dates on the y axis which is something I don't want.
So that's why I'm going to keep it as general as numbers because Excel takes the best guess of where
it thinks you want to put it.
Afridi's it's not a hundred percent sure because normally we do plot dates on the category side of things.
So I'm going to hold them control hold and control and highlight these go back to insert and insert
this fact bar chart here.
Now this part that's the number of days it's going to take me and Excel didn't start from zero it took
a good guess.
It automatically put the minimum at this number.
Now I'm going to adjust this minimum to start somewhere from here to so I don't have a big gap.
But one thing I'm going to do before is I want this the other way around.
I wouldn't design on top and sign off on the bottom.
I'm going to double click this and tick more categories in reverse order.
Now for this axes let's increase the minimum to 4 to school with seven thirty case that looks better.
Expand this.
I'm going to remove this now.
This stack here this bottom stack.
We don't want to see that.
So I'm going to hide it from view by taking away the fill color.
Do you really think I'm going to do is increase the gap with just so that I get the thicker bars and
change the color to my favorite which is a great color.
Remove these lines.
I know let's change these numbers to dates now because by default the formatting of the axes is linked
to source.
I can already change it here and it's going to be reflected in here.
OK so let's position the chart right here.
Let's remove the outline.
Let me expand it a little bit more.
And let's check if this works.
Yes that looks really nice.
So one thing I'm going to do is give some breathing space to the title.
I do want to make it obvious which of you I'm in right now or I'm looking at right now in the chart.
So let's make that dynamic by putting it up here so I'm going to move that up and type in Project time
line space quotation mark and
this now and space view.
I'm going to click on the chart title say equals and click on this.
So now that's the actual view.
I switch.
That's the plan you OK.
So I'm on the right track.
What I want to add right now is the percentage completion because it will be nice in presenting this
in the meetings to show which tasks are fully completed and which tasks are in the process of being
completed.
I want to add that here.
And let's just copy the formatting here.
But this needs to be a percentage.
So here let's say I've done 100 percent here is 80 here is 20 and so on.
So I want this to be reflected in here.
I'm going to show this using error bars.
Remember when I say that if you want to show horizontal error bars you need to use a scatterplot because
scatterplot has values has numbers on the X as well as the y axis.
Now the bar chart also has horizontal error bars because it has categories in the y axis and values
in the X-axis.
This means that we don't need to introduce new scatterplot series right here.
We can use the existing series that we have and activate the error bars for that series.
But we have two series we have a bottom stack and we have the top stack.
If we activated error bars for the bottom stack it puts a point right here at the end of the first stack
and the beginning of the second stack.
So that would actually be perfect in this case.
I'm going to highlight that stack from the plus here activate the error bars.
Now let's go to more options.
My point is right here I don't want these lines to go on both sides.
I don't own it or the minus.
I just want it to go this way so I just need to select the plus.
Now I need to specify the length of these lines.
Well that really depend on this percentage but not directly under percentage because that's going to
be the percentage of the bottom stack which I don't want.
I actually want to translate these two days.
So if it's 100 percent it's length should be identical.
It is.
It should be 8 then it should be 80 percent or 14 and 20 percent of 60.
So I need to add that in here that's going to be number of days completed.
And because I only want to show this for the actual series I need and if
it's Fix-It equals and need the quotation marks equals plan then it should be 0 otherwise it should
be this percentage multiplied by this number
OK so that looks good.
Now let's bring this in here.
So the length of these is not going to be controlled by this.
If we go to custom say specify value for a positive error bars.
I'm going to select this data case and the length of it looks good.
I don't want the cap for like no cap from here.
The colors should be in the screen and it can be much thicker.
So let's just go with AIDS.
That looks good.
OK so for actual It's showing it.
Let's switch to plan.
It doesn't show anything which is exactly what I want.
So now let's say we are going to our next meeting.
This is completed.
This we've done 50 percent this we've done that's 20 percent and this we've done 10 percent.
They all get reflected here.
Now as a last last thing what I want to do is to also add the number of working days to this chart but
I don't want to added insight here because it might be confusing about what that number is representing.
If it's the number of days remaining the full number of days originally planned or not.
So I'm actually going to add it here in the Xs and the axes can also be dynamic.
It doesn't have to be static so you can make it dynamic using formulas.
What I'm going to do is because we want to show different days if this is like Pearn an actual I'm going
to again use an IF
this.
Let's say it equals porn then it should show this task and I'm going to add a space and open brackets
inside the bracket.
I want to hold the number of working days so bracket open.
And that's the number of working days.
And let's put some text there.
So quotation workspace W.D. closed bracket quotation or and close the final bracket OK so what happens
now.
So I just wrote If this is and then this but I haven't given it and otherwise.
So we can continue.
Otherwise if this is actual.
Well I'm going to copy this part pasted here because A5 Well that's going to be the same task but I
don't want it should this number of working days and when it should be column.
So I'm just going to change that to G.
Strike this down and see if it looks nice here.
Great.
Last step is at this instead of this quick basically anywhere in your chart and go to select data that
these are the values in the chart.
I don't want to change them.
This is my x is my way X is categories I'm going to edit this.
And instead of my original one I'm going to select this one.
I can see them there.
It just needs some more breathing space because now we can see that's actual That's the number of working
days we need for each task.
How much percentage we've completed and we can switch to plan.
And that was the number of working days we had originally planned.
So if we do change anything in our data table everything should be updated as well because all of this
right here these are just formulas to make this chart dynamics.
I'm going to highlight them but the mini green color the only inputs I'm doing are these here.
So if I change now.
The number of working days let's say for this test that's seven.
Let's change the number of working days to 10 days Kate that shows up here and that pulls through here
as well.
So this is how you can create a fully featured and dynamic Gantt chart in Excel.