Creating a Gantt Chart in Google Docs Spreadsheet
Whatsa Gantt Chart??
Don’t you hate it when you need a Gantt chart and can’t figure out how to make one? Me neither! At least, not until I took a course on Software Engineering. A Gantt chart is essentially a horizontal bar graph that is used as a way to visualize a project schedule, typically used as part of software development. It usually goes hand-in-hand with a Work Breakdown Structure (WBS) chart, which is a type of tree diagram that helps to visualize and break down all the tasks involved in a project and what their dependencies are.
Now that we’ve got that out of the way, let’s get to the fun part: How to create a Gantt chart using a Google Docs Spreadsheet with conditional formatting. Oh boy!
Credit Where Credit is Due
First of all, I can’t take credit for figuring this out all on my own. I based this design off of Vertex42’s Free Gantt Chart Template for Excel. If you need a more substantial, powerful Gantt chart, I would highly recommend this chart. It’s free for personal use, and for a small fee you can unlock advanced features and open the licensing up for commercial use, if that’s what you’re looking for. (I should note, the Vertex42 spreadsheet does not import properly to Google Spreadsheet format, thus the reason I created this adaptation…)
Also, I should note, there is already built-in Gantt chart functionality in Google Docs Spreadsheets via a “Gadget” from Viewpath. There is a five minute tutorial over at Lifehacker if this solution will work for you. However, the trouble I found with this, is that when you try to save your spreadsheet to PDF, the Gadget goes invisible, so there’s no real way to export the chart (as an aside, my chart does not export well into other spreadsheet software, either, but exporting to PDF does work). This is the main reason (OK, well it’s pathetic reason, I really just wanted to see if I could make my own) I decided to try making my own Gantt chart utilizing Google Spreadsheet’s built-in conditional formatting. So, let’s get to it…
My Gantt Chart
Take a peek at my Gantt Chart Template:
Pretty snazzy, eh? That’s a plain HTML view of the spreadsheet. If you want to skip the tutorial and just grab the chart, follow the “Edit this page” link this link and you should be able to view a spreadsheet version and copy it into your own Google Docs folder (File => Make a copy…), though you can’t modify my version.
Here’s a brief rundown of the chart. At the top we have the standard “insert your info here” areas for Project Name, Company Name, and a Start Date. I arbitrarily started the chart dating at 1/20/2011, which was the beginning of my Software Engineering course. In retrospect, I should’ve just started with 1/1/2011 for illustrative purposes, but oh well…
Tasks and Subtasks, in this chart, attempt to follow a basic WBS structure, with “Task” headings taking a n.0 WBS number and Subtasks follow with an n.1, n.2, etc. The main task rows contain some formulas that work on the range of subtask rows listed beneath them to compute their own info. Don’t change anything but the Task Name and WBS number (essentially, the same field for simplicity’s sake). As you can see, the Start Date, Duration, and Percent Complete fields are highlighted in a nice, pleasant light green. These are the fields you should be changing (End Date is calculated automatically by adding the duration to the start date — it would be a simple feat to change this functionality to select start and end dates and have the duration calculated).
Google Spreadsheet would only allow me to freeze the first 5 columns, so there isn’t much room for expansion here unless you’re willing to make some compromises. To the right of the frozen panes is where the conditional formatting comes into play. In essence, there is a formula in each cell that compares the date for that column with the fields for that task row and either inserts a blank character ("") if it does not apply to that date range, inserts an X if it does apply to the date range, but a portion of the task is incomplete, or inserts a Y if it applies to the date range and a portion of the task is complete. From there, Google’s conditional formatting does the magic. By going to Format => Change colors with rules, we can set the text color and background color to be the same for each of our 3 conditions (“X”, “Y”, or ""), which hides the characters and gives us the look of a bar graph by filling in the cell. For main tasks, the X’s turn a cell to an almost black gray. For main tasks that a portion has been completed (Y has been inserted), the black gray turns to yellow to indicate progress is being made. Similarly, the blank cell turn a light gray just so you can follow the main task’s row with ease. Subtasks follow the same pattern with a lighter gray, blue, and no colorization for empty cells.
So, how’d we do it? With this formula:
if(AND((F$4>=$B5),(F$4<=$C5)),if((ROUNDDOWN($E5*$D5)<=(F$4-$B5)), "X", "Y"),"")
Looks a big mess, huh? Let’s add some whitespace and comments and see if it makes more sense:
if( // if is the conditional flag, how it evaluates (T or F) determines what goes in the cell
AND( // AND tells the spreadsheet to logically compare the following two (or more) items.
// If both (or all) of the conditions are true, the if statement evaluates to true
// and it places the first supplied value in the cell ("X", or "Y" - see below), otherwise
// it evaluates to false, and the second element goes in the cell ("")
(F$4>=$B5),
(F$4<=$C5)
),
if( // A nested if statement within the first if statement to determine the first value
(ROUNDDOWN($E5*$D5)<=(F$4-$B5)), // if this statement evaluates to true, this nested if
// inserts a "X" into the cell, if false, a "Y" goes in
"X", "Y"
), "" // If the encapsulating if statement evaluates to false (via the AND() function above),
// then no "X" or "Y" are inserted, instead the final value ("") is inserted, indicating
// this cell is inactive and no colorized by the conditional formatting we set up
) // end
Phew! It’s a bit of confusing logic, but it seems to work well (albeit, simplistically). By comparing the current column’s date field (F$4) with the current row task’s start and end dates ($B5, $C5), we determine if we need to do anything to the current cell (ie. it’s within the date range for the task). If not, we insert the null character ("") and move on (which leaves the cell blank). If it is in the date range, we must first determine, via the Percent Complete ($E5) field, whether to color the cell yellow or blue, indicating progress, or leave it dark, indicating no progress. We do this by multiply the Percent Complete ($E5) field by the number of days ($D5) to figure out how many “days complete” the project is across the range of dates for the task. For the sake of simplicity, the ROUNDDOWN. We then compare this to be less or equal than the date range (the current date (F$4, less the task’s Start Date ($B5).
Obviously, this won’t give us exact results much of the time on the visual side of the chart. I explained this in the template’s Help field: If you have 50% completed on an item that is 5 days duration, 50% of 5 days is 2.5 days; the chart will round this down to 2 days so only 2 out of 5 cells will be shown as “done”.
Conclusion
If you can live with this slight inaccuracy of the bar graph, I think you will find that this chart will work well for you. I’m sure there’s a lot of room for improvement, but hopefully it is enough to get your started. And since it is tailored to Google Docs Spreadsheet format, you can easily share it with your friends, colleagues, or teammates, granting them appropriate access to view or modify the graph at their leisure. Several people can even modify the chart simultaneously in real time! A great boon to keeping your project on schedule and keeping everyone informed, cutting down on wasteful progress meetings and sharing the progress instantaneously with everyone you choose, near or far.
I hope this primer on Google Docs Spreadsheet Gantt Charts with conditional formatting has been useful to you. Feel free to copy and modify the spreadsheet as you see fit, with no limitations other than keeping the credits intact somewhere on the document (inconspicuously in a Comment is fine with me — I just want to ensure that Vertex42 gets their due credit for the original Excel spreadsheet my Google chart is derived from). And of course, share this post with your friends and subscribe to my feed!
Til next time!