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

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 burndown 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.

10 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.

Leave a Reply

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