How to Make a Gant Chart in Google Sheets

A Gantt chart is a series of horizontal bars that visually represent a project schedule (tasks or activities) over time. It is one of the most commonly used chart types for project management. Gantt chart illustrates the start and end date/period of each task in a project timetable as well as the series and interdependencies between project tasks. It helps you to organize, plan, and execute projects of all sizes.

Google Sheets is a great alternative to create Gantt charts because of its real-time collaboration and auto-saving abilities, plus it’s free and easy to use. However, just like in Excel, Google Sheets doesn’t have an in-built Gantt chart type as an option, so you have to create one by customizing the bar chart.

In this tutorial, we will show you the steps to make a Gantt chart in Google Sheets using a Stacked Bar chart.

Creating Google Gantt Chart Using a Stacked Bar Chart

You can create a Gantt chart in Google Sheets with just a few simple steps. All you have to do is create your project schedule, calculate durations, generate a bar chart with that data, and then customize it to look like a Gantt Chart.

Setup Your Project Data

First, open a new Google spreadsheet and enter your project schedule (data) into three columns, namely, Task, Start Date, and End Date as shown in the screenshot below.

The name of the tasks/activities should be in the Task column and their corresponding dates should be in the start date and the End date columns.

Calculate Task Durations

Now you need to calculate durations for each task. For that, you need to create a second table a few rows below. Copy the Task column (First Column) of the original table and insert it into the second table as shown below. Then label the second and third columns of this new table as “Start Day” and “Duration”.

Now you need to calculate start day and duration of each task.

To find the start day, you need to determine the difference between each task’s start date and that of the first task. Type this formula in the first cell (B15) of the Start day column:

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

Since it’s the first day of the project, you get ‘0’.

Now drag the fill handle (small blue square at the lower right corner of the cell) over other cells up to the final task to copy the formula. Now you got the Start day of each task in the project timeline.

Next, you we need to determine the Duration of each task.

To do that, type the following formula in the first cell (C15) of Duration column:

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

Then copy the formula to the rest of the cell just like you did before. Now you got the duration for each task, which is how many days each task is going to take to complete.

Insert a Stacked Bar Graph

Now you can generate a stacked bar chart. To do that, select the whole second table (calculation table), click ‘Insert’ in the menu bar, and select ‘Chart’.

A chart will be generated titled ‘Start Date and Duration’ and a Chart Editor window will also open up on the right side of the Google Sheets, where you can customize the chart.

If this Chart editor doesn’t open automatically beside your chart, you can open it manually. To do that, select the chart, then click the ‘three-dotted menu (vertical ellipsis)’ at the upper right corner of the chart window, and select the ‘Edit the chart’ option.

Google Sheets will try to a generate chart that most fits your data. If it generates any other chart type, you need to change it. To change the chart, click on the ‘Chart type’ drop-down menu and select the ‘Stacked bar chart’ option.

Turn the Bar Chart into a Gantt Chart

You have created a bar chart, now it’s time to turn it into a Gantt chart. To do that, you need to customize your bar chart a little bit.

First, go to the ‘Customize’ tab in the chart editor and expand the ‘Series’ group.

Under Series section, choose ‘Start Day’ in the Apply to All series drop-down menu.

After that, change the ‘Fill opacity’ to ‘0%’.

This will render the blue bars transparent and thus making your bar chart look like a Gantt chart.

Now you can make other modifications to your Gath chart to suit your needs. For example, you can change the chart’s title, legend, color scheme, and more.