Easy Gantt Chart Timelines in Google Sheets (with 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.

22 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!

Leave a Reply

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