OK in this session we're going to look at how to create a Gantt chart on Excel.
So I'm going to create something like this and I'm going to show you that it's really easy to do every
10 minutes is all it would take you.
You only need basic excel knowledge you might pick up a few tips from me.
But the main thing I want to just give you an impression of how easy it is to do.
And we're going to start with a post-it note diagram like this.
And then we're going to convert it into an Excel Gantt Chart.
It's really important to start with the post-it note diagram.
That's the heart of your planning the Gantt chart is just a better way to present it and see it.
So we're going to use the example that I've taken from a training course based on setting up a European
distribution center for something you don't need to worry too much about the details of that.
I'm just going to use it as a way to demonstrate the process of setting up the Gantt Chart.
OK so we start with a completely blank page.
Ok so I'm going to show you how to create a Gantt chart based on a network diagram or network diagrams
also known as Perche charts flow charts post-it note diagrams whatever you want to call them.
I've got one that we've done earlier here just using Post-It Notes on a white board that's my preferred
way of doing it it's really low tech really easy.
A group of people can do it.
It's verging on fun.
So we've planned out a project this is actually opening up a distribution center for a product in Europe
and we've planned it all out.
And as you can see we've estimated the weeks we've got the Post-It notes in the right running order.
And from that we can find the critical path which is the longest path through and you can see the longest
path goes kind of through the middle identify country find a site biocide etc. and I've got those in
red.
So the final bit of creating the post-it note diagram is to show the critical path in red.
And now we're going to make it into a gunshot.
Now one of the problems with the post-it note diagrams it's not very portable if it's on a white board
but that's OK because we're going to make it into a Gantt chart using Excel.
And then you can email that print it out do whatever you want with it.
Personally I like Excel because it's the easiest way to do it.
It's everybody's got Excel everyone knows how to use it.
If you start using things like Microsoft Project you're going to buy extra software.
People have got to learn it.
If you limit it to other people they might not be able to open it and all of that.
So I'm thinking keep it simple.
Use Excel.
So the first thing we do is type in the tasks starting with the critical path.
So I'm just going to do that.
So you can see that the critical tasks go from identify country down through to opening business that
they are.
And then underneath those you put the floating tasks.
So the floating tasks are all the ones that are non-critical and remember the definition of a critical
task is just something that's on the critical path.
That's all it is.
It doesn't mean it's expensive or quality is critical or anything it just means it's time critical that's
what a critical task is.
So we've got a floating task on there as well now.
I'm going to make this column a little bit wider so that all the tasks can fit in the first column and
the next thing and then just put the numbers of the weeks across the top.
So if we add up the length of the critical path you can see it's four and four is eight.
Another 10 for buying a site 18 and another eight is getting difficult now 26.
And two more weeks for installing the furniture's 28.
It's a 28 week project.
So I'm going to put the numbers across the top.
If you select the first two and then drag this little dot you can go all the way out to where you want
to go and you can see it's going off the end of my page but that's OK.
So it's going up to 28 there.
I'm going to make sure if it's on the page now so I'm going to select that column and then hold the
shift key down.
Go back and select column holding the shift key down just makes it select all of them and then when
I pull in that first one they all come in by the same amount.
So I can get it the right size.
And while I'm there I'm going to send to them as well just because I'm quite attentive.
So I've got everything visible on my page now which is good.
I think I'll just say that was well because you can never quite trust your computer.
So the next thing is I'm going to put in the Nickelodeon part of the Gantt chart and I've got a way
of doing that.
That saves quite a lot of time and effort which is to use conditional formatting.
This is the only bit of this whole thing that's remotely clever.
I'm hoping you'll get the idea from watching this that anyone can do a gunshot using Excel it's really
easy and really quick.
This whole DeMars only take five minutes.
Probably if I wasn't explaining it as I went so I'm going to select the whole area of my Gantt chart
and I'm going to go conditional formatting so you can see this is in the home menu.
Just select conditional formatting highlighted cells rules and I'm going to go greater than.
So I'm going to say if if the content of the cell is greater than zero.
In other words if there's anything in there at all Cullerton so I formatted that and you can't tell
it looks perfect in all that's now been formatted so you can see if I was to put ones in there it would
color it in and if I delete them the color goes.
So that's it's just quicker than having to color in all the ones that you want.
Now the other thing is I want the floating task's the non-critical ones to be a different color.
So I'm going to go back into this back into conditional formatting highlighted cells greater than and
this time I'm going to go for a different color I think I'll go for green.
OK so those floating tasks are going to come out green when I get to them.
So everything's formatted and we're ready to put our tasks in now.
So I'm going to do is I'm just going to put one in each box just to make it color it in.
So I identified a suitable countries for weeks.
So I'm just going to put a wall in the first four box it's like that.
And then finding a site is also for weeks I'm just following along the critical path I'm ignoring all
the other tasks for the moment.
Buying a site is 10 weeks so that's.
10 there to 18 which is right next is modify the buildings that's another 8.
This does form an opportunity to double check your estimates as well.
So when you're filling these in you sometimes think actually does that look right.
And I'm assuming they're right for the sake of this.
Then we install the furniture and fittings which is two more weeks and then we open our opening and
it's an event has no generation so only a gunshot.
You just put a vertical line and the best way to do that is to select the whole column and just put
the board.
Just do a left border like that.
So that's the end there.
So for business it's just that line.
Any other events you want to have on your Gantt chart you can just have vertical lines.
So that's the critical path.
In hope you'll agree that was really easy.
Don't worry about the fact we've got ones in there at the moment.
They're just a way to make it color in but I am actually going to use those numbers for something else
later.
So now we can start putting the floating tusks in the first floating task.
I'm going to put in is get the trade permit which as you can see comes after identifying a suitable
country.
So it must be after this block of four.
So I got to put a vertical line down here using that border again for the constraint.
So it must be after that point we can't do it until we've got a country that constrained at the other
end is that it has to be done before we buy the site.
You can see that by looking at the flow chart there.
So this block here is buying this site so it must be done before we get to here.
Otherwise it's going to hold up the critical path which is a big crime.
So it's between those two vertical lines and it's two.
So I'm just going to put a little block here.
Let's go to the length of two save it as well.
Now these these tasks float somewhere between these two lines and we'll decide later whether we want
to do this earlier or later.
I probably would apply for the permit straightaway actually in which case what I could probably do is
just move those to that like that.
But we all worry about the floaters later.
So first of all we're just going to bring them all in in the middle if they're sort of floating range
and I quite like to put in a little arrow just to show that they've got that flight now to show the
arrows.
I need to go to the drawing tab.
Where where is it.
And so if you just go in search shapes you can just select an area and I'm going to go for this.
It's under the lines actually that just put one in like that.
And similarly the other way as well is going to click on that when it can have it going that way.
And that's just a way of showing that we've got we've got freedom about when we do those two that's
they've got float actually to it because it's a task of length 2 and it's got a space of four.
So that's the Permethrin the key thing to putting in the floating tasks is that you must have these
vertical constraints one on each end.
When do you have to wait until you start it.
And why must it be finished by.
So you'll see I'm going to do that again for find local suppliers and purchase furniture.
So these ones come after buying the site.
The end of that block.
They're to go and they both have to be done before we install the furniture.
She's here.
So what we've got here is we've actually got two tasks sharing that float.
We both got to fit into that space.
So I'm going to put in one of them as a two there and then I'm going to have a space before we do the
second one like that.
Now in real life you might actually move those a little bit to the right because you don't want to buy
the furniture a long time before you're going to install it.
But you could look at them in the space and decide whereabouts you want to fit them into that space.
And similarly as before it's going to get in shape if I double click on this then.
That doesn't work right.
And again this new version of Office is quite different to the old one.
I think what you do actually is you go insert shapes and then you can right click and it gives the option
of lock drawing mode.
So then you don't have to pick up the arrow every time you can put one in there and you can put one
in there.
And I'm going to put just a line between the two just so that we can show that they are connected and
you have to do one and then the other.
So that's the concept of floaters sharing float.
Now I'm actually going to just change the order of these task.
I'm going to move advertized down and then I'm going to move all of these back up and you'll see why
in a minute there's no rule about what Or do you have to have the tasks in on a gunshot so this vertical
axis is just completely unstructured apart from the fact you must have the critical path at the top.
But ideally you'd make it look neat.
And so I think it will work out slightly neater if I put in the manager and staff next.
So looking at my post-it notes I can see the manager and staff come after we find a site.
So at the end to find a site I just have an arrow line coming down a border like that.
So it has to be after this point and anywhere up to the end when we open for business.
So I'm going to get the manager as soon as I can.
Four weeks is a bit quick for recruiting but I'll go with the numbers provided and I'm going to get
the stuff right at the end because I don't want them sitting around costing me money and I haven't even
done my building it.
And similarly they're going to be connected with a line.
So I just go back to shape's pick up the line just to show that there's one and then the other.
So that's my manager and my stuff floating tasks.
You can see I've got the manager in parallel with buying the site and I might decide that I don't need
the manager at that point.
So what I could do later for example is I could move those and the great thing about Excel is you can
just cut and paste so I might actually move the manager to their manager arrives when I'm ready to start
modifying the buildings.
Anyway those are decisions we can make later and the gunshot is great for thinking about that sort of
thing.
Now the final task I'm going to put in is advertising the product.
Now if you look at advertising the product that comes after getting the trade permit and the trade permit
is a floating task they're beauties.
So it's actually off to here.
So what I'm going to do just absolutely as normal is I'm going to have my time constraint after that
and the trade permit can be the advertising can be anywhere between that line and opening for business
which is at the end and advertising the product takes four weeks.
So I think I'll just put that in the middle and as usual I put on my arrows.
You can save time actually by cutting and pasting arrows and dragging them in.
I sometimes do that gets a little bit quicker.
Now there's one important thing to know about this advertising task which is that it doesn't hang off
the critical path it's hanging off a floating touch.
So what we've got here is a floater hanging off a floater and that is what project managers actually
call it.
They have no sense of humor.
And so the point about it is that it doesn't matter the fact that that constraint there is movable means
that the amount of float that we've got for this task is is variable.
If we got the permit earlier then advertising would have a bit more float but it's exactly the same
process.
So once you understand the concept of sharing float and floaters that hang off of the floaters you can
then create the biggest Gantt chart in the world.
You just build it up step by step until it's done.
So that's all the basics put in.
But I now want to do some things to the gun shop to make it a bit nicer.
The first thing I'm going to do is put proper dates on the top here rather than just weak numbers.
So I've got to do is just select that row and just go in a row like that and then I can put in the dates.
So let's suppose today is the 12th of February 2012.
And ooh.
What does it mean when you get those.
And the answer is it means my column isn't wide enough.
But don't worry about that.
I'm going to sort them out in a minute.
So that's sweet commencing the 12th.
Now a week after that it will be the 19th of February 2012.
And in order to make it as visible I'm going to select that row again I'm going to go right click and
I'm going to go format cells and then I'm going to go alignment and I'm going to pull this up to being
a diagonal like that and say OK.
So there they are that's much nicer.
And then similarly as I did with the numbers I can then select both of them and drag this little dot
and it puts all the rest.
When I'm doing a training course that's the bit that gets gasps of amazement from the audience if you
like that bit so I can now see my whole project laid out.
I can see the dates.
I can see what's critical and what isn't.
I can see which of the long tasks which are the short tasks I can see that I'm going to start modifying
the buildings in June you know will people be on holiday in August.
That could be tricky.
I can see I'm going to be recruiting the manager in April etc. and you know and open at the end of August.
So I've now got a real time scale on my Gantt chart and that's one of the main reasons Gantt chart so
good people can actually see the whole project and that part within it and they can commit at the start
to these dates in their diaries so that's one advantage of a gunshot.
Another advantage of a gunshot is that you can add up vertically and see when your busy times are.
So for example if I select this column here and click on auto's which is over there on the menu it automatically
adds up a column and then by pulling this little dot across again I can add them all up so I can now
immediately see that I've got you know two tasks here and two tasks here.
Now you may say well that doesn't tell me a lot because it's pretty obvious I've got to tell those points
but what I can then do is I can actually put in how much time I'm going to spend on these tasks.
Now for the sake of time what do all of this but let's suppose that recruiting a manager is going to
take me 12 hours a week and let's suppose that buying the site I just have to have a meeting at the
beginning of six hours is one halfway through one at the end.
Let's say that modifying the buildings is going to take me 21 hours a week going up to 24 hours for
the last two weeks finding the furniture suppliers going to take me for maybe buying the furniture is
going to take me let's say six.
And then nothing because I wait for it to arrive.
By the way if you put nothing in it doesn't color it.
But I've discovered that if you put in.
And then it does color 18 and it still ends up correctly.
So what I've got now if I filled in all of those with the hours is I got a profile now.
As a load profile or resource profile of the hours I need in any given week I can make sure that doesn't
go above 40.
In fact ideally it wouldn't go above 10 because if you've got a day job as well as doing the project
you won't be able to spend probably more than about 10 hours a week on your project and to make that
a bit more visual.
Quite a good thing to do is to insert a graph.
So to do that I go to my receipt insert insert column and I just I just pick the most basic one usually
which is one that the to the column and it looks really good.
If you pull it out to be the same length as your page like this.
So if you just it's a little bit fiddly but if you just lined up the left hand one and then pull that
one out that's the right hand one.
You can see now that I can immediately look and see where I'm busy and where I'm not just getting that
lined up.
I just pull that one out slightly off the yeah.
So I can now easily see there is this big peak of work around here in July which was exactly what I
was going to go on holiday.
That's really inconvenient.
And you can see it live as well.
So if I suddenly realized that that say getting the perm it's going to take me 10 hours and put 10 in
there it is on my graph to save.
Always keep saving.
So the graph is a really good way to look at resources and resource planning is the second reason why
gunshots are important.
OK the three reasons why we want to use a gun shot.
First one is communication of the project.
Second one is resource planning by looking vertically.
The third one is monitoring progress as we go along.
And that's where we color as we go.
So you have to now line you just color in the column as you go.
And then you color in the work as you've completed it which is easy to do.
Well that one little fact that's worth noting is that when you color it in.
Suppose I decided to color this in let's say yellow to say I've done it.
It doesn't show is yellow because it's formatted.
So the way to do that is to take a square somewhere down here and color it and then you can use the
format painter to show what you've done like that.
And that's the best way to do that.
Now the next thing I want to show you on this is what if there's say a shut down or a holiday.
What if the first week of August we don't do anything.
Well it's really easy to do that all you do is just insert a column.
So the best way actually I find it can't find anything on these ribbon menus.
It's just a right click and go insert.
And then maybe color that Kulemin to show that you've that you're not going to be working at that time
so I go back to home and just select that.
So that's my week on the beach there summer holiday shutdown or whatever and similarly for Christmas
or whatever you might well want to put a week or two of nothing happening on your project.
Now the only other thing is I just need to repair the dates because you can see Ivan says to call them
and I've got no we care.
So what I do is I just select those two and pull that across and instead of finishing on the 19th of
August it's now going to be the 26 of August and you can see instead of 28 weeks it's now 29.
So that is that save it again.
So now I can see all my hours and everything and that's great.
By the way you might want to insert one more column in here.
And then you can put the name of who's doing each task and the great thing about the gunshot because
every task is on a different line.
It means you can have as many columns as you'd like.
So you might have the name of who's doing it.
The department they in the cost of it whatever you like and you can also add up a cross here like this
select auto sum because that works horizontally as well.
And if you then pull that down you can see that I can also see how many hours I'm spending on each task
so mollifying the building is going to take me 174 hours for example and I've got my total hours spent
on the whole project there.
Now I'm going to call this sheet hours I'm going to click right click and go rename and I'm going to
call that hours.
And then the next thing I'm going to do is I'm going to copy that.
So I'm going to right click move or copy and then I select create a copy for sheet too.
It's not a user friendly this.
So what it's done is put another one so I've now got two identical sheets and I'm going to rename this
one money.
So what I could do.
Now I've got all my hours spent here but I could go back through this one and put money as well.
So.
So what I've gone on here is how much I'm going to spend.
So let's suppose for example buying the site I'm going to spend 90 pounds upfront and then pay half
way stage payment and £120.
It is a very cheap site.
And then let's suppose that advertising is going to cost me 77 pounds a week et cetera.
So the point is I can put my money in exactly the same way as I did the time just now.
And here I've got the total I'm spending on each task like that but I've also got the total of I'm spending
each week.
So you can see where my spend profile is and spend for it as a really useful thing to have for your
project because I want to know when am I going to spend my 876 pounds.
And let's suppose I'm looking at financial years.
So this is the end of my financial year there.
So all I do is I can just mouseover that and it gives me a total down at the bottom you see where it
says Some So I'm actually spending 18 pounds on this in this financial year and then the next financial
year mousing over that it's now going up to 858.
So that's how much I'm spending in the second half.
So I've got to spend profile and I've got an hours profile.
Now the final thought about all of this is what if you have more than one project.
So what you can do is you can then add up these lines.
And the easiest way is probably just to move them onto another sheet or down the bottom of this so you
just do cut and paste.
But when you get down to here you need to use special paste and in fact we've got paste options here
so you can see I want to put in just the values I don't want the formulas I just want the values.
So that's from that project if I just call that you know project or whatever.
And then I can have Project B and C coming down here as well.
And then whatever those values are coming from there different gunshots I can have all my various projects
like that and then I can add up all of my projects using auto sum again and that will tell me then whether
I've got enough money or enough time to do all those projects.
And if I haven't then clearly I'll have to slide some of these out into the future.
Cut and paste them into my totals that reasonable on the bottom and then we are that's a Gantt chart
in Excel.
I reckon it will probably take you 10 minutes to do has to be worth doing.
But the key point I want to leave you with is always do the post-it note diagram first because you can't
create a gunshot from nothing.
My whole Gantt chart is based on the Post-It notes particularly based on the critical path and then
everything else hangs off that.
So that's it by find out.