Easy Gantt Charts in Google Sheets + Project Timeline Template

Google Docs and Gantt charts are a perfect match. Google Spreadsheets offers the ability to share and update spreadsheets in real-time which is a major benefit for any project team- especial those who work in different locations or time zones. On top of that, you can’t beat the free price!

There are many projects that are complex enough to demand a formal task planning and management hub but do not justify a full-featured, premium application. This tutorial will show you how to take your ordinary task list and turn it into a dynamic visual timeline — a Google Spreadsheet Gantt chart.

Google Spreadsheet Gantt Chart

View the Sample Chart

View an even better sample chart from a reader

There are other Google Spreadsheet Gantt chart examples that use the Chart feature as the visualization. I like to use the SPARKLINE() function. This keeps the project task visualization in the same place as all the important details about each task such as the RACI assignments or progress updates.

Sparklines Work Better Than Charts

Sparklines are essentially just little data visualizations in spreadsheet cells. To learn more about how the sparkline feature works, check out these sparkline examples. To create the visualization, we are going to use “bar” for the value of “charttype.” Then we get a little bit clever with colors to show the start and end dates of each task. The SPARKLINE formula for each task visual looks like this:

=SPARKLINE({INT(taskStart)-INT(projectStart), INT(taskFinish)-INT(projectFinish)},{"charttype","bar";"color1","white";"empty","zero"; "max",INT(projectFinish)-INT(projectStart)})

The projectStart and projectFinish values are the start and end date of the project, and the taskStart, and taskFinish values are the start and end dates for the task that is being shown in the timeline visualization.

SPARKLINE Gantt Chart

The reason everything is being wrapped in the INT() function is so that the dates can be subtracted from each other to provide the difference in days. The first argument to SPARKLINE puts two values in the array literal that are essentially:

{daysSinceProjectStartUntilTaskStart, daysSinceProjectStartUntilTaskFinish}

The SPARKLINE function then makes two bars, one which is colored "white", as to be invisible and the other which is colored blue (by default) or any color you choose by setting "color2". The value for "max" is the difference between the start and end of the project in days.

On the example template, there are a couple other features: a week-by-week ruler and the burndown visualization.

The week-by-week visualization uses a clever little formula to make an array of number incrementing by seven as the first argument to SPARKLINE to display alternating colored bars for each week of the project’s duration.

split(rept("7,",round((int(projectEnd)-int(projectStart))/7)),",")

The burn down visualization shows the days that have been burned through the project. This gives you a visual display of how well the project is keeping on track to its timeline. The first argument to SPARKLINE  is a dynamic value, calculated by subtracting the project’s start date from the current date:

int(today())-int(projectStart)

Customizing your Timelines

Each SPARKLINE function takes arguments for color1 and color2. These values set the color of the alternating bars in the bar visualization. For each task, color1 is set to white so to be invisible. But color2 can be set to anything that may be useful for managing your project. Colors could be specified by task owner or type, or even by dynamically set based on if they are ahead of schedule, in progress, late, etc…

Keep this in your Google Docs project folder with all of your other important project documentation for a neat project hub.

33 Comments

  1. Luis

    Hi Trevor,

    I really like your guide, it’s extremely useful for me at the moment.
    I have a question for you:
    How would can I make it possible for the bars to also to display the percentage of the work that has been completed so that the bar not only shows the date range, but it fills up with another color as it gets as higher completion percentage?

    I would be very grateful for any help here!

    Thank you,

    Luis

  2. Luis

    Hi Trevor,

    Sorry, I had also just one more question. Is it possible at all to have a color3?
    I’m have my sheet set up so that a specific task has a checklist with 3 options:
    Not Started – I would like the color of the bar to be red if this is the status
    In Progress – yellow
    Completed – green

    Is it only possible to use color1 and color2 or is it possible to add further color options? How would I go about this?

    Sorry for pestering so much 🙂 I just find this incredibly useful. Thanks a ton in advance.

    Thanks,

    1. Trevor Fox

      Hi Luis,

      Unfortunately, Google Spreadsheets does not give the option for a color3 or the option to superimpose text on top of a sparkline. I think the best option for this would be to use a separate column that calculates the completion percentage and uses conditional formating to express the level of completion with either discrete colors or a color spectrum.

      Alternatively, in my other post about sparklines, I show how to use conditional logic to color the sparklines. This example might also be helpful to you.

      I hope that helps!

      1. Luis

        Hi Trevor,

        Thanks for responding so quickly, and for the advice. Your example helped actually and I got it working! I have a last question regarding the Gantt chart. The chart is amazing and I hope to get it working right. My only issues is that I’m using start date end date for certain tasks…and if I put in a task that started and ended on the same date, it doesn’t show at all on the Gantt chart, it has to be at minimum one day in between each action, because of course the equation cancels itself out.

        Is there any way that I can circumvent this so that it still shows even if a sliver of a block on the Gantt chart?

        Thanks again for all of your help. I’m very grateful for your post and have already shared it with some friends.

        Cheers,

        Luis

  3. Ilya Mikhelson

    Hi Trevor,
    Thank you for this excellent guide! I have used it as a template for my own work, and have made some modifications that are documented in a YouTube video I made on the topic (https://youtu.be/-crraoSMxc0). I have referenced your blog there, since this is what got me started.

    Thanks again,
    Ilya

  4. Terry

    Hi Trevor, I was wondering if the week-by-week visualization formula can be altered to show increments based on intervals of 5 days. This would help me see things in the perspective of a normal work week.

    Thanks,

    1. Trevor Fox

      No problem Terri, just change the week-by-week cell from
      =SPARKLINE(split(rept("7,",round((int(C2)-int(B2))/7)),","),{"charttype","bar";"color1","white";"color2","#d6e8f0"})
      to…
      =SPARKLINE(split(rept("5,",round((int(C2)-int(B2))/5)),","),{"charttype","bar";"color1","white";"color2","#d6e8f0"})

      Changing the 7 to a 5 change the size of an interval- representing a 5-day increment rather than a 7-day (week) increment.

  5. Kai van Benthem

    Hi Trevor, Thanks ever so much for the lovely template. It’s very handy.
    …I just have one question:
    In column ‘I’ of your chart, you have the following formula:
    ‘{“charttype”,”bar”;”color1″,”white”;”color2″,if(F3=”Complete”,”lightgrey”,if(today()>C3,”orange”,E3));”empty”,”zero”; “max”,int($C$2)-int($B$2)}’ – this is row 3
    and then row 4 is:
    ‘{“charttype”,”bar”;”color1″,”white”;”color2″,if(F4=”Complete”,”lightgrey”,if(today()>C3,”orange”,E4));”empty”,”zero”; “max”,int($C$2)-int($B$2)}’
    then row 5 is:
    {“charttype”,”bar”;”color1″,”white”;”color2″,if(F5=”Complete”,”lightgrey”,if(today()>C3,”orange”,E5));”empty”,”zero”; “max”,int($C$2)-int($B$2)}
    My question is about the ‘if(today()>C3’ part. Shouldn’t it be that the C3 changes to the corresponding line? Like for row 4, change to C4 and for row 5, change to C5? I’m not the best with formulas but I’m trying to understand yours because a hopeful future client uses g suite to manage projects. I would be very grateful if you could let me know. Thank you kindly.
    Kai

    1. Trevor Fox

      Hi Kai, that is a good question with a non-obvious answer. The nested if statement can be a bit nasty. The reason that the reference to C3 does not change based on the row (or anything else) is that C3 is where the project end date is. So that if statement compares the end date of the task to the end date of the project and assigns the color of that bar to orange accordingly. Good luck!

  6. Terry

    Hi Trevor,

    I tried to make your chart work for me, but it can’t seem to sync the current date line and the items. I’m confused by the syntax and it seems to show the date past the visual curent date line.

    I tried to change the date parameters to match which works, but in the line items like the projects, they show for example the end of the date going past the date.

    For example if you set your Highlight Current Day Dec-01 to Dec-31 to show the month for example, and if today’s date is Dec 22 and you set one of the tasks to Dec 5-Dec18, for example, it will show the end bar going past the current date.

    So I’m not sure if the syntax is correct that displays it correctly.

    This is a great starting point for me, but I can’t seem to figure out how to fix it to make it work the way I’d like it to.

    1. Trevor Fox

      Hi Terry, my guess is that the dates’ years are mismatched so the dates would appear to be out of sync- the dates are correct but the year changes their order. I can’t say if it is this for sure but it is my best guess.

      1. Terry

        Hi again and thanks for the reply. I believe I isolated it to the way you add that clever padding in the front of the bar, however what it does is it throws the scale off, so the dates don’t line up properly to what is the current date line.

        I’m trying to figure a different way of doing it, but if you have any other insight, using the sparkline doesn’t work the way I had hoped it would.

  7. Terry

    Hi again Trevor,

    I found the spot that was causing the error.
    Using your example on row 3 you have:
    =SPARKLINE({int(B3)-int($B$2),int(C3)-int($B$2)},{“charttype”,”bar”;”color1″,”white”;”color2″,if(F3=”Complete”,”lightgrey”,if(today()>C3,”orange”,E3));”empty”,”zero”; “max”,int($C$2)-int($B$2)})

    It should actually be:
    =SPARKLINE({int(B3)-int($B$2),int(C3)-int(B3)},{“charttype”,”bar”;”color1″,”white”;”color2″,if(F3=”Complete”,”lightgrey”,if(today()>C3,”orange”,E3));”empty”,”zero”; “max”,int($C$2)-int($B$2)})

    Your second task value was calculating the ‘task end date’ – ‘the project start date’ giving an incorrect value which should have been ‘task end date’ – ‘task start date’ giving correct amount of days stacked on the invisible date and the maximum date defined by the start and end date of the project.

    Do you agree?

    Terry

    1. Trevor Fox

      Thanks, Terry! Great catch. You are right. I’ve updated the Task bars- including the Highlight today’s date bar. Everything looks good considering the issues you found. Please let me know if I missed anything. Thanks again!

      1. Terry

        Hey, you’re welcome! Your blog got me started on what I have built for myself on this test sheet:

        https://docs.google.com/spreadsheets/d/1fNQf_yi7nbO1l71vUOIXCHCS609rwT7A3vc9iqh491M/edit?usp=sharing

        I built on your principals and customized it for my needs. My purposes are a little different in that I only need Task, and no individual projects with assigned tasks (although I could build evolve mine to do that I suppose). I made the timeline based on live time (30 days before today and 60 days after). It has a control panel on top, with some info like task of the day etc….

        My sheet has some junk scripting in it with lots of If statements all over it. I did discover one detail that might be worth of note for you. If you task date is set for two days, the current formula returns a difference of 1 day. I adjusted this by adding an +1 to my formulas to account for that because it doesnt take into account for the present day in the formula. Not sure if I’m making sense or not (on my sheet on column N, you’ll see the formula and it might make some sense).

        Anyhow, thanks a bunch for getting me started!

  8. Paul Macey

    Hi Trevor,

    Thank you for the demo of such a cool feature. Should really help speed up my spreadhseet.

    I coped and pasted the formula into my google sheet and came across the same error as Terry found above. I have put the updated text below.

    =SPARKLINE({INT(taskStart)-INT(projectStart), INT(taskFinish)-INT(projectFinish)},{“charttype”,”bar”;”color1″,”white”;”empty”,”zero”; “max”,INT(projectFinish)-INT(projectStart)})

    updated to taskfinsih – taskstart

    =SPARKLINE({INT(taskStart)-INT(projectStart), INT(taskFinish)-INT(taskStart)},{“charttype”,”bar”;”color1″,”white”;”empty”,”zero”; “max”,INT(projectFinish)-INT(projectStart)})

    Thanks again for a good blog and helping me find a better way todo gant charts in google sheets. Hope the feedback helps.

    Thanks
    Paul

  9. Riyanka Daga

    Hi Trevor
    For the timelines that we create , by rept and split formula, is there a way to add Data labels to the Bar graph that shows the week number , or name of the month in the timeline. My requirement is more for program management where I want to show how many projects we have active and in progress in a given quarter or year so we can resource accordingly for the weeks that we are stacked very close.

    1. Trevor Fox

      Hi Riyanka, unfortunately, the Google Spreadsheet SPARKLINE formula does not all for any inline data labels. Perhaps you could use a different cell to hold this information?

  10. David

    Trevor,
    I just started using Sparklines and find them extremely useful, your blog is a great resource. I’ve run into a big roadblock though. My data is gathered and processed in Sheets, but it needs to end up in Docs (or Word) for a monthly report, and I can’t find a way to show Sparklines there. Any ideas how that could be done?
    Thanks,
    David

    1. Trevor Fox

      Hi David, that is a tough one. And although you can copy tables into sheets, sparklines do not work the same. Unfortunately, the best thing I can think of is to take a screen shot… good luck!

  11. lucian

    Hi Trevor,
    Great job with the tutorial. I have tried to replicate it but it seems the sparklines dissapear when using another date format e.g. dd.mm.yyyy. Can you help me figure out how to adapt it?
    Thank you in advance,
    Lucian

  12. Mary

    Hi Trevor,
    This is a great tutorial: I was able to very easily get a Gantt chart up and running so easily and elegantly. Thank you!

    I have one question: I incorporated your formula to highlight the current day. This works great, except for when the start date for the task is happening in the future (i.e., today is 4/11/18, and the task is starting 9/1/18). For some reason, it is still marking the current day within these bars, even though the bar takes place in the future.

    My workaround at the moment is to just take out that part of the formula that highlights the current day for things that are queued.

    Thank you again,
    Mary

  13. Gabriele Simeone

    Hi Trevor – thanks for this, it helped me a lot! I ended up creating a gantt where the status of the activity (changed by the user) would trigger the colour change of the bar, but initially, I wanted to apply the logic of a nested if so that if today()>end_date, complete colour, if start_date<today()< end_date, active colour, if today()< start_date & end_date, upcoming colour. But it didn't work!
    See here 🙁

    https://docs.google.com/spreadsheets/d/1Q_b8fGnxVd9LS-IOKUrbL-vYWSOw27j-yaNt7Kn3JYU/edit?usp=sharing

    Another thing that bugs me is that in the spreadsheet you linked up, in the option where you provide the thin white today line, if you take the whole "data" argument of SPARKLINE() and you use it to replace the "data" argument of the functions above (where there was no thin today line) – once you adjust the cells of reference, it messes up the end date.

    https://docs.google.com/spreadsheets/d/1crisKYOl8-wpw0Y3vj3spbA9kwS2nDepQerClgzmpkY/edit#gid=0

    You must have gotten what I was trying to do: have an automated colour coding based on the IF today() etc. formula and a thin today line – Any help would be very appreciated, and I think the GANTT like that would be really useful!

    Thanks!

    Gabriele

  14. Laercio A A Avelino

    Hi @TrevorFox, I loved your video demo and what you did with sparklines, that is sick! I tried myself to follow as up, but I found a problem with syntax. Apparently, Google updated their language and they’re you “\” instead of “,”. I understood your workflow, but because of this update, I can’t copy + paste to fit my needs. Are you alreay aware of such update?

    Thanks a lot _o/

    1. Trevor Fox

      Hi Laercia, I’m glad it’s helpful. I don’t see anything in Google’s documentation to say anything about backslashes (\). Please share where you got this information. I think it would be useful to everyone!

Leave a Reply

Your email address will not be published. Required fields are marked *