how to create a simple gantt chart with google sheets _ hello techo

8/17/2019 How to Create a Simple Gantt Chart With Google Sheets _ Hello Techo

1/12

How To Create a Simple Gantt Chart with Google Sheets

Doli Stepniewski May 9, 2014 17 Comments

DESIGN, INTERNET, REPORTING & ANALYTICS

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.

FELLOW TECHOS

Anton Visser (9)

Brett Garland (1)

Charlie Palmer (2)

Dale Liszka (3)

Doli Stepniewski (10)

Paul Shoemaker (1)

Ted Parton (4)

HOME CODING DATABASE SYS ADMIN STRATEGY DESIGN ECHO

http://www.hellotecho.com/author/victionhttp://www.hellotecho.com/how-to-create-a-simple-gantt-chart-with-google-sheetshttp://www.hellotecho.com/http://www.hellotecho.com/category/codinghttp://www.hellotecho.com/category/databasehttp://www.hellotecho.com/category/sys-adminhttp://www.hellotecho.com/http://www.hellotecho.com/category/codinghttp://www.hellotecho.com/category/databasehttp://www.hellotecho.com/category/sys-adminhttp://www.hellotecho.com/http://www.hellotecho.com/http://www.hellotecho.com/http://www.hellotecho.com/author/victionhttp://www.hellotecho.com/how-to-create-a-simple-gantt-chart-with-google-sheetshttp://www.hellotecho.com/category/echohttp://www.hellotecho.com/category/designhttp://www.hellotecho.com/category/strategyhttp://www.hellotecho.com/category/sys-adminhttp://www.hellotecho.com/category/databasehttp://www.hellotecho.com/category/codinghttp://www.hellotecho.com/http://www.hellotecho.com/author/tpartonhttp://www.hellotecho.com/author/apshoemakerhttp://www.hellotecho.com/author/victionhttp://www.hellotecho.com/author/dliszkahttp://www.hellotecho.com/author/charliehttp://www.hellotecho.com/author/brett-garlandhttp://www.hellotecho.com/author/toneplexhttps://drive.google.com/previewtemplate?id=1XLh-KOEKIq8o-Zv8ynk-C11T04faq922ePX4YPaKL0A&mode=publichttps://drive.google.com/http://en.wikipedia.org/wiki/Gantt_charthttp://www.hellotecho.com/category/reporting-analyticshttp://www.hellotecho.com/category/internethttp://www.hellotecho.com/category/designhttp://www.hellotecho.com/how-to-create-a-simple-gantt-chart-with-google-sheetshttp://www.hellotecho.com/author/victionhttp://www.hellotecho.com/


2/12

Create a new spreadsheet with three (3)

columns — Add tasks with respective start

and end dates.

Copy & paste headers below your data —

Add formula =A2 to copy first row/column

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


3/12

=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 convertedStart Date days from converted Complete

Date days.

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

)


4/12

Copy the row by selecting the first three

columns of data, then dragging the bottom

right corner down 6 rows.

Select the data range then click “insert

chart” icon or select menu item.


5/12

Select “Stacked Bar Chart” type by clicking

“more.”


6/12

http://www.hellotecho.com/wp-content/uploads/2014/05/more-charts.png


7/12

Finally, change the first bar set color to

“none.”


8/12

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.

https://drive.google.com/previewtemplate?id=1XLh-KOEKIq8o-Zv8ynk-C11T04faq922ePX4YPaKL0A&mode=public


9/12

← Magento Send Email Via SendGrid

Happy Holidays from Hello Techo – Fun with CSS →

Comments 1

• •

BarryG •

Great! Wish Google would focus a bit more on their docs to

build these basics in.

• •

Jamie Gray •

agreed. sure wish this was easier to implement w/o

spending time troubleshooting functions and data

ranges

• •

Ba rryG •

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 hasint(today) - int(complete date). First column has no color, 2nd

column has green and third column (lateness) is in red.

• •

hellotecho • Mod

Great idea Barry. Thanks for the comments.

• •

mordiano •

Could you provide a screenshot?

charts gantt google spreadsheet

https://disqus.com/by/grbradsk/https://disqus.com/by/SirJamesGray/https://disqus.com/by/SirJamesGray/https://disqus.com/by/grbradsk/https://disqus.com/home/forums/hellotecho/https://disqus.com/home/inbox/http://www.linkedin.com/shareArticle?mini=true&url=http%3A%2F%2Fwww.hellotecho.com%2Fhow-to-create-a-simple-gantt-chart-with-google-sheets&title=How+To+Create+a+Simple+Gantt+Chart+with+Google+Sheetshttp://www.hellotecho.com/tag/spreadsheethttp://www.hellotecho.com/tag/googlehttp://www.hellotecho.com/tag/gantthttp://www.hellotecho.com/tag/chartshttps://disqus.com/by/mordiano/https://disqus.com/by/hellotecho/https://disqus.com/by/grbradsk/https://disqus.com/by/SirJamesGray/https://disqus.com/by/grbradsk/http://www.hellotecho.com/how-to-create-a-simple-gantt-chart-with-google-sheets#comment-1694474216http://www.hellotecho.com/how-to-create-a-simple-gantt-chart-with-google-sheets#comment-1544270206https://disqus.com/by/mordiano/http://www.hellotecho.com/how-to-create-a-simple-gantt-chart-with-google-sheets#comment-1636776488http://www.hellotecho.com/how-to-create-a-simple-gantt-chart-with-google-sheets#comment-1544270206https://disqus.com/by/hellotecho/http://www.hellotecho.com/how-to-create-a-simple-gantt-chart-with-google-sheets#comment-1544311919https://disqus.com/by/grbradsk/http://www.hellotecho.com/how-to-create-a-simple-gantt-chart-with-google-sheets#comment-1544270206http://www.hellotecho.com/how-to-create-a-simple-gantt-chart-with-google-sheets#comment-1544203653https://disqus.com/by/SirJamesGray/http://www.hellotecho.com/how-to-create-a-simple-gantt-chart-with-google-sheets#comment-2293672198https://disqus.com/by/grbradsk/http://www.hellotecho.com/how-to-create-a-simple-gantt-chart-with-google-sheets#comment-1544203653https://disqus.com/home/inbox/https://disqus.com/home/forums/hellotecho/http://www.linkedin.com/shareArticle?mini=true&url=http%3A%2F%2Fwww.hellotecho.com%2Fhow-to-create-a-simple-gantt-chart-with-google-sheets&title=How+To+Create+a+Simple+Gantt+Chart+with+Google+Sheetshttps://plus.google.com/share?url=http%3A%2F%2Fwww.hellotecho.com%2Fhow-to-create-a-simple-gantt-chart-with-google-sheetshttp://twitter.com/intent/tweet/?text=How+To+Create+a+Simple+Gantt+Chart+with+Google+Sheets&url=http%3A%2F%2Fwww.hellotecho.com%2Fhow-to-create-a-simple-gantt-chart-with-google-sheetshttp://www.facebook.com/sharer/sharer.php?u=http%3A%2F%2Fwww.hellotecho.com%2Fhow-to-create-a-simple-gantt-chart-with-google-sheetshttp://www.hellotecho.com/happy-holidays-from-hello-techo-fun-with-csshttp://www.hellotecho.com/magento-send-email-via-sendgrid


10/12

• •

ron zz e •

How do you use dates for the horizontal axis? excel lets you do

it but sheets doesn't

• •

hellotecho • Mod

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/doc...

• •

Aksam Zarook •

Thanks. I run a web design server and was looking for a

solution like this to track projects and share with clients

• •

hellotecho • Mod

Right on Aksam! -- It's not a full-blown, feature-richsolution, but gets the job done in a pinch.

• •

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

https://disqus.com/by/disqus_PrVW54RYOm/https://disqus.com/by/hellotecho/https://disqus.com/by/aksamzarook/https://disqus.com/by/hellotecho/http://www.hellotecho.com/how-to-create-a-simple-gantt-chart-with-google-sheets#comment-2388382335https://disqus.com/by/disqus_PrVW54RYOm/http://www.hellotecho.com/how-to-create-a-simple-gantt-chart-with-google-sheets#comment-2375077105http://www.hellotecho.com/how-to-create-a-simple-gantt-chart-with-google-sheets#comment-2039259344https://disqus.com/by/hellotecho/http://www.hellotecho.com/how-to-create-a-simple-gantt-chart-with-google-sheets#comment-2039974671https://disqus.com/by/aksamzarook/http://www.hellotecho.com/how-to-create-a-simple-gantt-chart-with-google-sheets#comment-2039259344https://support.google.com/docs/answer/2382813?hl=enhttp://www.hellotecho.com/how-to-create-a-simple-gantt-chart-with-google-sheets#comment-1694474216https://disqus.com/by/hellotecho/http://www.hellotecho.com/how-to-create-a-simple-gantt-chart-with-google-sheets#comment-1695490069http://www.hellotecho.com/how-to-create-a-simple-gantt-chart-with-google-sheets#comment-1694474216


11/12

• •

.

• •

hellotecho • Mod

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!

• •

hellotecho • Mod

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!

• •

hellotecho • Mod

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:

https://disqus.com/by/stuarttaylor2016/https://disqus.com/by/hellotecho/https://disqus.com/by/disqus_WZ9k2X2nDG/https://disqus.com/by/hellotecho/https://disqus.com/by/hellotecho/http://excelhawk.com/gantt+chart+excel+template.htmlhttps://disqus.com/by/stuarttaylor2016/http://www.hellotecho.com/how-to-create-a-simple-gantt-chart-with-google-sheets#comment-2608816581http://www.hellotecho.com/how-to-create-a-simple-gantt-chart-with-google-sheets#comment-2585996349https://disqus.com/by/hellotecho/http://www.hellotecho.com/how-to-create-a-simple-gantt-chart-with-google-sheets#comment-2593325352https://disqus.com/by/disqus_WZ9k2X2nDG/http://www.hellotecho.com/how-to-create-a-simple-gantt-chart-with-google-sheets#comment-2585996349http://www.hellotecho.com/how-to-create-a-simple-gantt-chart-with-google-sheets#comment-2552125557https://disqus.com/by/hellotecho/http://www.hellotecho.com/how-to-create-a-simple-gantt-chart-with-google-sheets#comment-2552537606http://www.hellotecho.com/how-to-create-a-simple-gantt-chart-with-google-sheets#comment-2552125557http://www.hellotecho.com/how-to-create-a-simple-gantt-chart-with-google-sheets#comment-2388382335https://disqus.com/by/hellotecho/http://www.hellotecho.com/how-to-create-a-simple-gantt-chart-with-google-sheets#comment-2388907958


12/12

Magento Send Email Via

SendGrid

•

— sandy I ran

into the same issue in one of

my stores, but haven't had

Ubuntu extracting SFX “No

such file or directory”

•

— type: $

unrar e

IE10.Win7.For.LinuxVirtualB

HELLO TECHO

• •

ttp: exce aw .com gantt+c a...

Thanks,

Stuart

Copyright © 2016 hello techo | Theme by: Theme Horse | Powered by:WordPress
http://wordpress.org/http://themehorse.com/http://www.hellotecho.com/https://help.disqus.com/customer/portal/articles/1657951?utm_source=disqus&utm_medium=embed-footer&utm_content=privacy-btnhttps://publishers.disqus.com/engage?utm_source=hellotecho&utm_medium=Disqus-Footerhttps://disqus.com/http://excelhawk.com/gantt+chart+excel+template.htmlhttp://disq.us/url?url=http%3A%2F%2Fwww.hellotecho.com%2Fubuntu-extracting-sfx-no-such-file-or-directory%3AXAWEUu-CGyM2eiKTQNbuQByWgJU&imp=sgk5sb2burltj&prev_imp=sgje3128g70ci&forum_id=486784&forum=hellotecho&thread_id=2674040444&thread=1484482041&zone=thread&area=bottom&object_type=thread&object_id=1484482041http://disq.us/url?url=http%3A%2F%2Fwww.hellotecho.com%2Fubuntu-extracting-sfx-no-such-file-or-directory%3AXAWEUu-CGyM2eiKTQNbuQByWgJU&imp=sgk5sb2burltj&prev_imp=sgje3128g70ci&forum_id=486784&forum=hellotecho&thread_id=2674040444&thread=1484482041&zone=thread&area=bottom&object_type=thread&object_id=1484482041http://disq.us/url?url=http%3A%2F%2Fwww.hellotecho.com%2Fmagento-send-email-via-sendgrid%3A19L3Oi7_Llxyzuhrb7Ho3_BomFo&imp=sgk5sb2burltj&prev_imp=sgje3128g70ci&forum_id=486784&forum=hellotecho&thread_id=2674040444&thread=1939421999&zone=thread&area=bottom&object_type=thread&object_id=1939421999http://disq.us/url?url=http%3A%2F%2Fwww.hellotecho.com%2Fmagento-send-email-via-sendgrid%3A19L3Oi7_Llxyzuhrb7Ho3_BomFo&imp=sgk5sb2burltj&prev_imp=sgje3128g70ci&forum_id=486784&forum=hellotecho&thread_id=2674040444&thread=1939421999&zone=thread&area=bottom&object_type=thread&object_id=1939421999

how to create a simple gantt chart with google sheets _ hello techo

Documents