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

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

### 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) ) ```

### Finally, change the first bar set color to “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.

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!

• Jay, glad you liked it!

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

• Spandana Nakka