Create GANTT charts in Google Sheets


Written by Sebastian 🧑

For a funding request for one of our projects it was necessary to provide a rough GANTT outline of its planning. We didn't want to download any fully fledged project management software to create that. Especially because I would never have to use it ever again. Therefore, we wanted to create such a GANTT chart using Google Sheets.


What is GANTT

A GANTT chart is a special type of bar chart that illustrates a project's schedule.

The bars represent a task and are noted on the Y axis. Time is noted on the X axis. It always takes some time to work on a task so the bar is a representation of that task and its length shows how long we think the team needs for its completion.

By reading the GANTT chart from top to bottom you can get an overview of the sequential order in which the tasks should be completed.


Create a GANTT chart in Google Sheets

For the Technobabble: A simple GANTT chart can be seen as a stacked bar chart with the first bar being the amount of days since the start of the first task and the second bar being the actual length of the task. To make our creation here look more like a "real GANTT chart" we're going to make the first bar invisible.


Create a task list

First we need a list of tasks with their corresponding start and end dates:


Task list with start and end dates

Make sure to set the correct locale for your spreadsheet so that Google Sheets recognizes dates really as dates and handles them correctly. That is important for later.

To change the locale go to `File > Spreadsheet settings` and select the locale to match your date format (in my case this would be `Germany`) and save the settings and save your changes by pressing `Save settings`.

Now you might need to set the date type of the start and end dates manually. You can do this by selecting them and then pressing the `123` drop-down in the toolbar and selecting `Date`.


Create a transfer table

I use the rows `Task`, `Start on day` and `Task duration` for this table (and styled it a bit):


Empty transfer table to prepare the GANTT chart

Now we will fill that table with content.

Click into the first cell in the column `Task` and type a `=` sign. With the formula editor still open, click into the first cell of the other table's `Task` column (in my example below this would be the cell with `Research` inside).

This should virtually copy the content (the `Research` text) over.

To apply that for all rows, select the just edited cell. Now it should be highlighted in with a blue border. In the bottom right corner you can find a small square (see picture below). Click and drag this selection downwards. Releasing the mouse will apply the copy effect for all the rows (I will later refer to this technique by simply calling it "apply for all the rows").

Now it should look something like this:


Transfer table with copied tasks

To populate the `Start on day` column click into the first cell and fill it with the following formula: `=INT(A2)-INT($A$2)`. In my case `A2` is the cell that includes the start date of the first task. You need to adjust that to your spreadsheet of course. The second input (`$A$2`) is a static copy of that same cell. Later when we apply this formula to all the rows, the first cell reference (`A2`) will effect change in (`A3, A4, A5, …`) while the second is always locked onto the cell (`A2`).

This formula calculates how many days have passed since the start of the first task to the start of the current task. It translates to: `= <start date of current task> - <start date of first task>` (for the first task this should always be 0).

Now apply that to all rows. After that it should look like this:


Transfer table with start on day filled

To calculate the `Task duration`, click into the first cell of that column and enter the formula: `=(INT(B2)-INT($A$2))-(INT(A2)-INT($A$2))+1`. It translates to: `= (<end date of the current task> - <start date of the first task>) - (<start date of the current task> - <start date of the first task>)`. Using this formula we can calculate the duration of a task dynamically.

Now apply that to all rows:


Transfer table with task duration filled

Create the GANTT chart

Now we are all set to generate a GANTT chart out of this transfer table *hooray*. To do that select the transfer table and got to `Insert > Chart`.

If it didn't automatically set its type to stacked bar chart, double-click on the chart and select `Stacked bar chart` in the `Chart type`'s drop-down menu. It should now look like this:


Stacked bar chart of transfer table

As a final step, we need to hide the `Start on day` bar. This is done by clicking the chart. In the `Chart editor` menu on the right go to `Customize > Series`. In this selection you find another drop-down where you select `Start on day`:



In the `Color` drop-down set the color to `none` and that's it! Now you can customize the chart as you like. For example my chart now looks like this:



Conclusion

You can see it's not very difficult to create a simple GANTT chart with Google Sheets. On the other hand this is not a good replacement if you are working as a project manager and do this on a day-to-day basis. Then you probably should not use this, as it can get very confusing with big data sets and is not very intuitive to maintain. But for a quick and simple small GANTT chart it serves its purpose!


β‡’ Up next:


↩ β€’ Table of contents