How To Create a Simple Gantt Chart with Google Sheets

How To Create a Simple Gantt Chart with Google Sheets

How To Create a Simple Gantt Chart with Google Sheets

Here’s a little trick to make a super-basic Gantt Chart / timeline graph using Google Sheets.

Quick start: Basic Gantt Chart template.

Disclaimer: This is not a powerful management tool nor a replacement to timeline project software. This simply displays a spreadsheet chart in a Gantt-like style.

Prerequisites: Google account with access to Google Drive (AKA Google Docs) and a working knowledge of spreadsheets.


Create a new spreadsheet with three (3) columns — Add tasks with respective start and end dates.

Create Tasks and Dates


Copy & paste headers below your data — Add formula =A2 to copy first row/column of tasks.

Copy Headers and Tasks


Convert dates to days with int() function — Subtract the constant Start Date days from self (and other days) to convert all dates into project days and task days.


=int(B2) - int($B$2)

NOTE: Using $B$2 will make the value static and always represent that cell, so when we paste into other columns, it will remain the start date cell value.

Formula for start DAY


Find the number of days the task is projected to take by subtracting converted Start Date days from converted Complete Date days.


=( int(C2) - int($B$2) ) - ( int(B2) - int($B$2) )

Task Days


Copy the row by selecting the first three columns of data, then dragging the bottom right corner down 6 rows.

copy-range


Select the data range then click “insert chart” icon or select menu item.

Data Rangeinsert-chart


Select “Stacked Bar Chart” type by clicking “more.”

more-chartsstacked-chart


Finally, change the first bar set color to “none.”

bar1-none


The chart now shows only the days a task will take. Edit title and axes as needed. Since we used formulas to create chart data, simply change dates next to tasks and the chart will update automatically.

simple gantt chart


Let me know if this tutorial was helpful. I’d love to hear how you’ve implemented or improved upon it. Try it: Basic Gantt Chart template.

  • BarryG

    Great! Wish Google would focus a bit more on their docs to build these basics in.

    • agreed. sure wish this was easier to implement w/o spending time troubleshooting functions and data ranges

  • BarryG

    For tracking, you can add a 3rd column “Now”. Add a first row “Today” which starts at 0 and goes out the number of days until the present. Any project that has completed or not started has a third column value of 0. Any project that is late has int(today) – int(complete date). First column has no color, 2nd column has green and third column (lateness) is in red.

    • Great idea Barry. Thanks for the comments.

    • mordiano

      Could you provide a screenshot?

  • CronFizzle

    How do you use dates for the horizontal axis? excel lets you do it but sheets doesn’t

    • CronFizzle, using dates in horizontal axis in Google Sheets is supported, however not in this tutorial. The purpose of this tutorial is to show how you can hack Sheets to simulate a Gantt chart by converting dates to days (int) and charting those values out. That said, you could include dates on chart as an annotation instead of horizontal axis. The annotation would show up on the bar at appropriate spot. Change the chart’s data range to include the column to right of last number, then copy enddate column values to the last column. Be sure to use =to_text([cell]) so that the date field is cast as text so that it is interpreted as annotation and not value. you can learn more about annotation in Sheets’ charts at https://support.google.com/docs/answer/2382813?hl=en

  • Thanks. I run a web design server and was looking for a solution like this to track projects and share with clients

    • Right on Aksam! — It’s not a full-blown, feature-rich solution, but gets the job done in a pinch.

  • Pingback: Gantt Chart Template Roundup()

  • dude

    My end dates returned negative values, but the duration was accurate. So I pre-pended the formula with absolute value like this ABS(value). Then it worked great!

  • Anupam

    This is a great idea! Thanks for sharing it. I was anyways looking for online tools to make gantt charts quickly but it seems google spreadsheets can just do it using this method.

    • Anupam, The sky’s the limit if you’re willing to hack a little 😉

  • Martin

    Thanks for the detailed step by step!
    I was able to get the basics done. Now, is there a way to individually change the color of each bar?

    Also, is it possible to show real dates in the Horizontal axis in stay of amount of days? (do I make sense?)
    Thanks a bunch!

    • Hey Martin, Thanks for the feedback.

      Since this is a hack, we’re not able to change colors of individual bars. Each dataset can only be assigned one color — we’re using a trick with two datasets one with color and the other as transparent.

      The best we can do with real dates is via annotations. (See the comment and screenshot I left for CronFizzle about a year ago).

      Good luck!

  • Jay Garcia

    This is just wonderful. Gets the job done without too much hoopla. Thanks!

  • Stuart

    Dear Doli,

    If you dont mind, please also teach us tutorial on how to create these kind of gantt chart:

    http://excelhawk.com/gantt+chart+excel+template.html

    Thanks,

    Stuart

  • Eunice Wu

    are you able to do a tutorial on how to make the horizontal axis a date instead of using annotation? Thanks!