in this video we will create a Gantt
chart in Google sheets a Gantt chart is
an excellent organizational tool that
allows a quick visual view of the
progress of a project making it on
Google sheets allows the project to be
easily collaborative since any of the
team members can edit it and view
changes in real time to make my own
Gantt chart I tried many approaches and
found many potential sources online
however none of them really achieved
what I wanted until I found a blog post
by Trevor Fox which was a great starting
point his blog is linked in the
description below
from there I made several modifications
to arrive at what I wanted to begin open
a new sheet in Google sheets the first
column is going to be our project name
and the various tasks and I'll start it
in row 2 to allow for headers unto other
columns the second and third columns are
going to contain the start and end date
of the various tasks
let's say this project will go from
January 1st to June 1st so let's start
by writing a 101 2017 and once I do that
this will then automatically become a
date field and if I double click it I
can actually select a different date if
I would like and it's going to change it
for me like that so you get a nice
calendar view so I'll enter all of my
dates in this format you can apply the
formatting of a particular cell to other
cells by highlighting the cells and then
dragging this little plus over here down
to the rest of the cells you want to
format and I'll copy that format and
electorally try to extrapolate for you
and so it's things that you want to
progress by one day at a time here which
might make sense but really what I
wanted to do was make this the date
format that I want and then I can change
them as I see appropriate so my project
starts in January first and then June
first and I want the various tasks to
take place in between so I will change
this care
okay so now I have all of my dates and
the next column is going to be our time
line which I'll go into detail later and
the last one is going to be the status
of the particular task to make things a
little more readable I'm going to just
highlight these and make them all bold
and the same thing with a project name
and the various tasks now the status can
be one of three things for my template
it can either be complete active or
upcoming so to make those my options I'm
going to right click on this cell I'll
click on data validation and then the
criteria that I want will be from a list
of items and those items that aren't
going to be complete active and upcoming
and I want to show these as a drop-down
so I'll say save and now i've got these
options so I can choose complete for
example over here and again to spread
this to the other cells I'm going to
drag this formatting down and so then I
have this drop down for all of these
tasks the timeline section is going to
be the most complicated one out of all
of these and I will of course link my
template sheet in the description below
so that you can use it when you're
starting off instead of writing these
formulas by hand but I want to go
through what the formulas actually do in
case you want to customize this sheet
for yourself so the first thing that I
will actually modify is going to be the
timeline for task two and our whole goal
here is to make some kind of a bar chart
representation that's going to show
where the task actually belongs in the
overall timeline of the project and
whether the task is complete or ongoing
or still upcoming and to represent that
with various colors to perform this
operation to make this chart I'm going
to use the sparkline functionality in
Google sheets which is going to allow me
to create a bar chart inside of a given
cell instead of making a separate graph
so to start it off I'll select the box
that I want and I'll start to type my
formula starting with an equal sign and
I'll say sparkline
and the arguments the sparkline are
going to be for is the data and then the
various specifications so the data will
be enclosed in curly braces and so will
the charge specification so then the
data that I want to represent here is
going to be two stacked bars the first
one is going to go to the start date of
the task and the second is going to go
from the start date to the complete date
so I'm going to reference things here
and in order to use dates as numbers and
be able to subtract one from another I'm
going to use the int function so I'll
say int of and here I'll say cell b4 so
that's going to be the start date - int
of and here I want to reference the
start of the project but since I'm going
to generalize this this format to the
other cells I want to fix which cell I'm
referencing to because I always want to
reference it to the same start date of
the project and therefore I'm going to
use dollar sign B dollar sign - in order
to fix that to that particular cell
instead of propagating it like I would
otherwise I'll show you what I mean a
little bit later on when I actually
spread the format with other cells so
this will be my first bar and then the
next piece of data here is going to be
int of c4 - end of b4 alright so I have
my two pieces of data and then I want to
specify what kind of a chart I want this
to be so here I'm going to say chart
type I want it to be a bar chart and
then I specify the colors of the various
segments that I have so I'll say color
one is going to be white this is going
to make the first segment invisible and
that's exactly what we want you'll see
the result in just a moment and the
second color so here I'll specify color
two and four now for the moment just for
illustration let's make this green and
that's it so this is what I would get if
I were to do that however this is not
exactly what I want so first of all let
me spread this out a bit to make it more
clear what I really want is this entire
space to take up the space of the entire
project all the way from January 1st to
June 1st and not just like this because
this currently is only going to January
17th so the first modification that I'm
going to make is after the color I'm
going to specify that I want the entire
length of that cell the maximum to be
end of dollar C dollar two so the entire
project - end of dollar B dollar two and
that's going to make it so that now the
entire length of this corresponds to the
length of the entire project and now
this is just a bar in that one location
so if I am to spread this to the other
cells then this already is starting to
look like a Gantt chart in the sense
that each bar represents the duration of
the given task nice and visually and I
can spread this to the first one as well
now the reason I started with a second
as opposed to the first is because this
calculation here to me makes a bit more
sense starting with a second one because
in the first one you'll be basically
subtracting the same date from the same
date and you're going to get a value of
zero whereas with this one it makes a
bit more sense but again you could have
equivalently started with the first one
now as far as the propagation that I was
talking about notice that I had typed an
int of b4 - int of dollar B dollar two
if I look at the next one it becomes
into b5 - and the key here is that the
whole dollar B dollar to the dollar
makes it stay constant regardless of
your propagation and so therefore while
those other values changed dynamically
the ones that were referenced with a
dollar stay fixed and that's important
because we always want to reference the
same start and end dates of the entire
project now this already looks like what
I want in effect however I want to make
this even nicer and to do
I'm going to introduce some more colors
so you can actually change the colors
based on if statements and logical
statements so the chart type is bar
color one is white so notice the first
part over here is always white so it's
invisible the second part is always
green but that's not really what I want
so instead I'm going to replace this
with an if statement I'm going to say if
and here I'm going to start referencing
my fifth column the status and I'm going
to say if efore is equal to the word
complete then I actually want the color
to be gray and then the way that things
work is I can provide a third argument
and that's going to be like the else and
so here I can say for example comma
green and close that out and now
everything that is marked as complete as
soon as I propagate this all is going to
be marked with gray and if I change any
of them to lets say active it's going to
become green so exactly what I specified
as long as the status is complete it's
going to turn gray but again that's not
fully what I want
so instead over here with the else
condition I'm going to change that to
another if statement an embedded if
statement and this is where things get
kind of ugly and complicated so try to
follow along and I'm doing this only so
that you can make modifications in the
future if you would so like otherwise
just look at the template that I'm going
to link below so here I'll say if the
date today where I can use this function
today is greater than c4 that is the end
date I'm going to mark this as red and
otherwise if and I'm going to use an end
statement to combine two conditions if
today is greater than B for and today is
less than C for so if we're currently
inside of the project window and
efore is still upcoming
which is not good it should already be
active then I'm going to mark this as
dark red and otherwise if and today is
greater than b4 and today is less than
c4 so again if we're inside of this
project window then I'm going to mark
this as yellow and if efore is equal to
active then I'm going to mark this as
yellow otherwise I'm going to mark it as
green and then I have to close out each
one of these parentheses so hopefully I
do this the right number of times
otherwise it will be a parse error and
so it looks like that potentially what
so let me test it so if I change this to
active it turns red because we are
currently past that date and it's still
active as we wanted if I make it
upcoming that's also no good it's
already past that end date so let me
mark that as complete and let's
propagate this property through all of
our cells and see if things work as we
expect so this one is active and we're
past that date so that's going to be red
let's make this one active as well and
currently today is March 29th so we are
within this window and so it turns
yellow and let's say this one is
upcoming well we're already within this
window it's March 29th as March 26th and
therefore it's going to be dark red
because this is still upcoming and
finally this one let's make it upcoming
and it's going to be green because we're
not within the window yet and that task
has yet to come so this does exactly
what I expected it to do again you can
modify those colors based on a
methodology that I just presented there
are a couple more features that I want
to go over that will make this a little
a bit nicer the first one is going to be
a burn down in the last row here and
that's just going to show you I'll make
it bold is there just going to show you
the progress of the overall project in
terms of the days that have passed and
so for this one I'll use another spark
line and this will be simpler here what
I want is the data just to be today -
the start of the project and once more
it's going to be a bar chart where color
one is going to be black and once more
the max is going to be int c2 - int B -
I don't bother with the dollar signs
here because I'm not going to be
generalizing this to any other cells
which is going to remain in this one and
that's it so this shows you then the
progress and it shows us that we are
currently over here which makes it much
easier to visualize where we are in the
scope of the overall project and one
more enhancement here is for this first
line here I wanted to represent kind of
the weeks of the project and so once
more I'm going to use spark line and I'm
going to say this is equal to spark line
of and here my data is going to be a
bunch of segments each one of link seven
for the duration of the entire project
the seven corresponding to the number of
weeks in the project so it'll split up
in two weeks and so in order to do this
I'm going to use a couple of functions
so first of all before I actually do
this I'll just close this for now and I
do anything I want to show you what I'm
going to use so just in a random cell
over here I'm going to start typing and
the first one I'm going to do is using a
repetition function I'm going to repeat
the number seven with a comma after it
and I'm going to repeat it basically the
number of times that seven divides the
span of the project but I want to make
that an integer so I'm going to floor it
floor will basically just cut off
whatever decimal you have after the
division operation is performed so I'm
going to say floor of int c2 - int of b2
divided by the whole quantity divided by
seven so then you can see that it's
going to repeat this that many times
because there's a number of weeks that
there are if in fact if I just take that
calculation on its own right here and I
put that over here I have an extra
parenthesis there if I do that I get
this number 21.57 and of course it can
repeat it that many times so instead I
want to floor that and make it
twenty-one repetitions because I have
twenty-one four weeks however I also
then have a little remainder and
basically what I would then want to do
is add another number to here
corresponding to the rest of the number
of days that there are and so for that I
can use the mod function I can say mod
the modulo function int c2 - int b2 and
then what I want to divide that by to
find the remainder by seven and you see
there are four days left over here so
that's what I want to tack on to the end
and so another function that we're going
to use because this is not the kind of
data that sparkline will accept it'll
need data in each cell separately in
order to create your chart I'm going to
use the split function and so here I'm
going to take this and I'm just going to
add split this thing that I created and
split it on commas that's what I'm
specifying right here and if I do that
then you can see that now I get each one
of those sevens in a separate cell of
its own and I'm going to tack on this
four onto the end so to combine it all
together then I'm going to take this
put it into sparkline in here again I'll
denote my first set of data and my bar
chart parameters and so I'll say split
here and I'll throw in also my mod ok so
now I have that data over here of course
it doesn't plot anything yet because
there is no specification here and so
then I'm just going to make this another
bar chart where the first color is going
to be white but again you can change
this and the second color is going to be
light blue and now if I hit enter you
see what I'm going to get this is going
to split that time line into these
blocks of length seven each one for a
week except for the last one which is
going to be the remainder for the
duration of days so now our chart is
complete and I just erased those three
entries over here just to make a cleaner
so you can use this as a template later
on and here I can change the name by the
way to Gantt chart template and if you
want to expand this if you want to add
more tasks one of the nice things is
that if you take this for example and
you hit something like control X to cut
it and you paste it somewhere else it's
still going to reference things properly
it's still going to reference those
things that you specified properly and
so you can directly do that and then if
I want to expand these things down as
well well I can do the same thing I've
done before I highlight all of them I
drag it down now each one of these days
of course is going to change
inappropriately but the tasks numbers
will actually increment as long as you
keep from task 1 task 2 in general you
should probably change the tax claims to
what you're actually doing and then
modify them appropriately but yeah this
is what's going to happen and you're
still going to have all the nice
properties over here with the drop-down
menus and whatnot and so you can expand
this
are to be any size that you want and
likewise you can also get rid of various
entries if you want so for example if I
don't want these things well I can
simply highlight them and hit delete and
they'll be gone this still leaves the
formatting right here
so here I'll hit ctrl Z to undo that
instead I can highlight all of these so
here I hit one of them then I help shift
and click on the other row in order to
highlight everything in its range and
then I can right-click and say delete
these rows and then all their formatting
is gone and this is brought up to that
level as well so that's another way to
do the same thing so altogether we have
seen how to create a Gantt chart in
Google sheets everything here is
customizable so feel free to use this as
a starting point or to use it as is the
chart we made in this video is available
the link in the description below