Detailed Gantt Chart Google Sheets Guide + 2 Free Templates

A Gantt chart can be a powerful tool to help project managers share and distribute the project tasks among team members, and it also helps them control progress and keep projects on track. Gantt charts in Google Sheets are now becoming increasingly popular, especially with more and more businesses going online and remote.

In this Gantt Chart Google Sheets Guide. We will also show you how to make a simple Gantt chart template from scratch that you can easily share or download.

Download a Free Google Sheets Gantt Chart Template

Gantt Chart Template

To use this template, click File->Make a copy after you open the link. Doing so will let you edit and save the sheet as your own.

Notice that the outline already has a few dummy values for start and end dates filled in. You can go ahead and change these according to your requirement.

Here is another simple Gantt chart template Google Sheets that you can use for free:

Free Gantt Chart Template

The first row is filled in to demonstrate the Google Sheets Gantt chart conditional formatting. The duration columns also have a formula in them that will automatically update when you fill in the start and end dates.

What Is a Gantt Chart, and What Are They Used For?

Gantt charts are used to visualize the breakdown of a project schedule. Tasks are presented in a Gantt chart in the form of a timeline. Each activity is presented as a bar, and the position and length of the bar represent the start date, duration, and ending date of the activity/task.

This visual representation helps anyone see at a glance:

• The activities in the project
• The relative duration of each activity
• At which points the activities overlap, and by how much
• Minimum time to deliver the project

Most importantly, a Gantt chart helps you identify the critical path. This is the sequence of tasks that needs to be completed so the project can be delivered on time.

You can use a Gantt chart in a Google spreadsheet to keep teams and stakeholders informed of progress. All you need to do is update the chart to reflect schedule changes and how these changes affect other tasks in the project.

Related reading: 5 Useful Google Sheets Project Management Templates [Free]

Creating a Gantt Chart in Google Sheets Using a Stacked Bar Chart

Google Sheets does not yet have the option to create a Gantt chart, but there’s a workaround. There are three ways in which you can create a Gantt Chart:

• Using a stacked bar chart
• Using an in-built template
• Creating a template from scratch.

You can use the Stacked bar chart that Google Sheets offers and customize it to make a Gantt chart. Note that you can’t use a line graph with a line of best fit or any other kind of chart except for a stacked bar chart.

Here’s how it works:

1. Before You Build the Chart

Before we create the Gantt chart, we need to enter the project schedule.

1. Make three columns with the following headers:
   • Task Name
   • Starting Date
   • Ending date

2. Fill in the cells of each of these columns with the appropriate task details. If you want just to practice, you can use the imaginary project tasks shown in the screenshot below:

3. Next, create a new table with three column headers right after your last row or task. Let these columns have the following headers:
   • Task Name
   • Start Day
   • Duration

4. Copy the same tasks to this new table.

5. In the first cell under the Start Day column (cell B8 in our example), type the formula:

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

6. Drag down the fill handle to copy the above formula to all other cells in column B of the second table. This will help figure out how many days from the starting date each task should begin.

7. In the first cell under the Duration column (cell C8 in our example), type the formula:
   
   =C2-B2
8. Drag down the fill handle to copy the above formula to all other cells in column C of the second table. This will help figure out the duration for each task (by calculating the difference between the day the task ends and the day it begins).

2. Building the Google Spreadsheets Gantt Chart

Now we are ready to create the Gantt chart. Note that we will only use the second table to build the chart since it has everything we need. You can build the chart using the following steps:

1. Select the second table from the worksheet (cells A7:C11 in our example).
2. From the Insert menu, select the Chart option.

3. Google Sheets usually tries to predict the best chart that goes with your data, so you should automatically see a stacked bar chart displayed. If you don’t, then click on the created chart, and from the Chart editor on the right sidebar, select the Stacked bar chart from the dropdown menu under ‘Chart Type’.

4. You will notice that your stacked bar chart has two sections – one for the Start day (in blue), and another for Duration (in red). We want to make the ‘Start day’ part of the bars transparent so that it starts to resemble an actual Gantt chart.

5. For this, double-click on any ‘Start day’ bar (the blue part of the bar in this case). You should see all the Start day bars of the chart highlighted.

6. From the Chart editor, select the Customize tab.

7. Under the ‘Series’ section, make sure that ‘Start day’ is selected from the dropdown menu, just under the word, ‘Series’.

8. Click on the dropdown under Fill opacity, and select 0%. This will make the start day parts of the bar chart completely transparent (or invisible).

Your chart should now resemble a Gantt chart. You can change the chart axis titles in the chart editor.

This method helps create a relatively simple Gantt chart with just the basics. You can, of course, expand it further by adding more tasks, newer projects, or more features.

Related Reading: Google Sheets Charts Guide

How to Remove the Chart Legend From a Gantt Chart

To remove the legend from the Gantt Chart:

1. In the Chart editor menu, navigate to Chart > Legend
2. Change the Position drop-down box to None

Building and Customizing a Google Sheet Gantt Chart Template from Scratch

You may find the template provided by Google Sheets hard to work with and prefer making your own so that you can customize it to your requirements. In this section of the tutorial, we are going to show you how.

We will make a straightforward Gantt chart Google Sheets template that will help you track your tasks easily and efficiently. Here’s how:

How to Create a Gantt Chart In Google Sheets

1. Start by creating a basic skeleton or outline of the sheet, in which we create slots for days, task titles, start date, end date, duration, etc. We also need to add slots for basic details such as Company name, Project manager, and Project title.

2. Add in the formulas to calculate the duration, days, and date of the week. Following this, we use conditional formatting to highlight the columns corresponding to weekends. We also use conditional formatting to display the actual Gantt chart. To help the user visualize the percentage of each task completed, we can use conditional formatting with a color scale.
3. After the main skeleton is ready, we can start filling in the basic details and then enter each task title, start date, end date, and task owner entry. Once we complete this, the sheet will automatically calculate the Duration and display the Google Sheets Gantt chart by filling in the relevant cells in dark blue.

As soon as you change the Start or End date of a project task, the Gantt chart will automatically update to reflect the change.

Let us go over each process step by step.

Creating the Basic Outline of the Gantt Chart Template

Let us start with the main outline or skeleton of the template. Here’s how it is supposed to look:

You can choose to either create this outline by yourself or just duplicate the design that we have created for you:

Adding Formula for Task Duration

Let us start by filling in column F with a formula that will automatically display the number of days the task needs to be completed in. For this:

1. Enter the following formula in cell F10:

         =INT(E10)-INT(D10)+1

2. Use the fill handle to copy the formula down to the rest of the cells of column F.

2. How To Customize A Gantt Chart Template and Update With Automation

Using Color Scale Conditional Formatting for Percent Complete Column

The percent complete column (column G) will allow you to enter how much of the given task has been completed. We use Color scale conditional formatting here so that each column cell shows a darker color as the percentage completed increases, with 0% being completely white and 100% being dark cyan.

To achieve this, follow the steps listed below:

1. Select the cells of column G, starting from cell G10 all the way down.
2. From the Format menu, select Conditional Formatting.

3. This will open the Conditional format rules sidebar on the right.

4. Select the Color scale tab.

5. Click on the color scale bar under ‘Preview’ and select the ‘white to green’ option, as shown in the screenshot below:

6. You should also see three dropdown lists under Minpoint, Midpoint and Maxpoint. From each dropdown list, select the ‘Percent’ option.

7. Click Done.

Google Sheets will now fill each column with the relevant color on the scale depending on the percentage you enter. Try entering a few percentage values to see how the fill colors of the cells change.

Filling in the Sequence of Days

Now let us automate the sheet to display the sequence of days, depending on the starting date of the first task in the project.

If the date in cell D10 is 3/12/2021, you will have cells H8 to AL8 displaying days 1 to 31, since March has 31 days. Similarly, if ED10 is 4/12/2021, then you will have cells H8 to AK8 expressing days 1 to 30 since April has 30 days.

1. We want to start on the first day of the month. This means if the date in cell D10 is 3/12/2021, we want to start counting from the first day of March. For this, we use the following formula in cell H8:

=EOMONTH(D10,-1)+1

The above formula returns the first day of the month given in cell D10. Here, EOMONTH finds the last day of the previous month (since we gave -1 as the second parameter) and then adds a 1 to it to get the first day of the given month.

2. The EOMONTH function returns an actual DATE value, but we only want to see the day in the cell, not the entire date. So we can format the cell by:
   
   1. Navigating to Format->Number->More Formats->Custom number format 
   2. Typing “d” in the input box
   3. Pressing the Apply button.

Here’s the result that we see in cell H8:

3. Now we want to display the rest of the days of the month by simply adding one day to the previous cell.

Simply type the following formula in cell I8:

=H8+1

4. Drag the fill handle of this cell to the right, so the formula gets copied to the rest of the cells in row 8.

Now we can fill in the days of the week. We want the days of the week to change according to the corresponding dates in row 8 (depending on the start date entered in cell D10).

So if the Start date in D10 is 3/12/2021, you will have days of the week starting from Monday, since March 1st , 2021 is a Monday.

5. Use the same formula we used in cell H9 to display the day of the week, sequenced according to the corresponding date in row 8, it goes as follows:

=LEFT(TEXT(H8,"ddd"),1)

The above formula converts the date in cell H8 to the day of the week form using the TEXT function, and then extracts just the first letter from the day’s name by using the LEFT function and passing 1 as the second parameter.

So, if D10 contains the date 3/12/2021, then here’s a breakdown of what the above function returns:

=LEFT(TEXT(H8,"ddd"),1)

=LEFT(“Mon”,1)

=”M”

6. Now all you need to do is drag this formula right to copy it horizontally to the rest of the cells in row 9.

Using Conditional Formatting to Highlight Weekend Columns

Now it’s time to highlight the weekend day slots of the Gantt Chart area with a light blue background. For this, we use Conditional Formatting.

Follow the steps below:

1. Select the Gantt Chart area. In our example, we selected the cells in the range H10 to BR22.  
2. Navigate to Format->Conditional Formatting.
3. This opens the Conditional format rule sidebar on the right.
4. Click on the dropdown menu under ‘Format cells if…’ and select ‘Custom formula is’.

5. A new input box will appear just below the dropdown. Enter the formula =H$9=”S” in this input box.

6. Under ‘Formatting style’, select the formatting you want to apply to the weekend cells. We set the fill color to light blue.

7. Click Done.

You will notice all the columns corresponding to Saturdays and Sundays highlighted in light blue.

Explanation of the Formula

The formula we used in the ‘Custom formula is’ input box is quite simple:

=H$9="S"

It simply checks if its corresponding cell in row 9 (the day of the week) is an “S” (since both Saturday and Sunday start with an “S”). If the condition is true, the cell is highlighted in light blue. This formula works similarly to the SUMIF Function.

Automating the Creation of the Gantt Charts for Each Task (Based on Start and End Dates)

Now we can automate the actual Gantt chart area to display the Google Sheets Gantt chart bars based on each task’s Start and End dates.

We will again use Conditional Formatting for the purpose and simply add a new rule to the same set of cells.

Here are the steps that you need to follow:

1. Select the Gantt Chart area. In our example, we selected the cells in the range H10 to BR22.  
2. Navigate to Format->Conditional Formatting.
3. This opens the Conditional format rule sidebar on the right.
4. Click on ‘Add another rule’

5. Click on the dropdown menu under ‘Format cells if…’ and select ‘Custom formula is’.
6. A new input box will appear just below the dropdown. Enter the formula =AND(H$8>=$D10,H$8<=$E10) in this input box.

7. Under ‘Formatting style’, select the formatting you want to apply to the weekend cells. We set the fill color to dark blue.

8. Click Done.

You should now see Gantt charts for each task get automatically updated depending on the start and end dates in columns D and E, respectively.

We want to layer the Gantt chart formatting (dark blue) over the weekend days formatting (light blue).

9. For this, click anywhere in your Gantt chart area. In the Conditional format rules sidebar, you will see the weekend days formatting rule followed by the Gantt chart formatting rules below it.

10. Move your mouse over the second rule until you see three vertical dots on the left side of the rule, and your mouse turns into a four-headed arrow. At this point, drag the second rule up so that it comes before the first rule:

Now your Gantt chart should come on top, as shown in the image below:

Explanation of the Formula

The formula we used for conditional formatting is:

=AND(H$8>=$D10,H$8<=$E10)

This formula checks if the date in each column’s 8th row is within the task’s corresponding Start and End dates. So if we are in cell H10, then we check if the date in H8 is greater than or equal to the date in cell D10 and if it is less than or equal to the date in cell E10. Since one of these two conditions is not true (the date 3/1/15 is less than the value in cell D10), cell H10 does not get formatted in dark blue.

Similarly, if we are in cell T10, we check if the date in T8 is greater than or equal to the date in cell D10 and if it is less than or equal to the date in cell E10. Since both these conditions are true (the date 3/13/21 is between 3/12/21 and 3/15/21), cell T10 gets formatted in dark blue.

3. How to Export a Gantt Chart in Google Sheets to Excel

That’s it! We’ve finished our own Gantt chart template in Google Sheets built from scratch! You can also choose to customize this template further to suit your requirements. You can follow these simple steps to export your Google Sheets Gantt chart for later use.

Exporting a Google Sheets Gantt chart works the same as any other Sheet or Google Doc.

1. Click on File>Download
2. Select the format you wish to export the file as (.xls for Excel)
3. Choose where to save the file

How to Handle Gantt Charts With Dependencies in Google Sheets

Sometimes, in more complicated Gantt Charts you’ll want to set up tasks that are dependent on one another. You may need some tasks to be completed before you start the next or some may need to occur simultaneously. Here’s how you create dependencies in Google Sheets with dates.

Make Sure Tasks Start After Another Is Completed

Let’s say we have two tasks but you can’t start Task 2 until Task 1 is finished. Here’s how you’d set up a basic dependency for the dates:

1. Set up a start and end dates column in your data
2. Click the cell with the start date for the task that shouldn’t start until the other is completed, B3 in our example
3. Type =MAX( into the cell
4. Click the cell with the end date for the dependent task, C2 in the example
5. Press Enter

The start date cell should automatically update to be the same date as the end date for the other task, even if you change the end date.

Set up Dependent Tasks to Occur Concurrently

Now let’s pretend that Task 3 needs to start at the same time as Task 2. We can set up this dependency in a similar way, like so:

1. Click the cell with the start date for Task 3
2. Type =MAX( into the cell
3. Click the cell with the start date for the dependent task, B3 in the example
4. Press Enter

Note: Since B3 is dependent on C2 and B4 is dependent on B3, B4 is also dependent on C2.

Create Gantt Chart in Google Sheets FAQ

How Do I Create a Gantt Chart in Google Sheets?

You can build a simplified Gantt chart in Google Sheets by whiting out a section of a stacked bar chart. Yet, the simplest and best way is to use a template in Google Sheets. There is one in the Sheets library, or you could download ours at the bottom of this page.

Does Google Have a Gantt Chart?

There is no native Gantt chart for Google Sheets, but you can build a makeshift one using a stacked bar chart and whiting out one set of data. Or you can build one with Gantt chart Google Sheets conditional formatting. The simplest way to do this is to simply download the template at the bottom of this page or use the one in the Google template library.

How Do I Use Gantt Templates in Google Sheets?

You just have to download our Google spreadsheet Gantt chart template and enter the dates for your project timelines. The Gantt chart section of the sheet should update automatically from there.

Where Can I Make a Gantt Chart for Free?

You can download our Google Sheets Gantt chart template for free.

Drawbacks Of a Google Sheet Gantt Chart.

Although Gantt sheets are very convenient to use, they have a number of drawbacks:

• It takes time to create a Gantt chart, especially if you have a large project
• There are some details about the tasks that may be missing. This is because they are mainly used to show project schedules, the start and end date of the tasks as well as the person responsible for the project tasks. They don’t show details about the amount of work each task requires or things they depend on to get done.
• They are difficult to create. It is especially difficult to create for a large project since  having many tasks and subtasks makes it harder to use the Gantt chart.
• It also becomes difficult to update or edit the more tasks there are.

Conclusion

In this tutorial, we went over a few ways to create a Gantt chart in Google Sheets. You should have gained an understanding of how to build and edit a Gantt chart Google Sheets spreadsheet. If it seemed a little confusing, you might need to brush up on some other Google Sheets skills first. Check out our tips to help you become a more competent spreadsheet user.

Related:

• Save Chart as Image in Google Sheets
• How to Make a Line Graph in Google Sheets
• How to Make a Histogram in Google Sheets
• How to Make a Scatter Plot In Google Sheets
• How to Create a Pie Chart in Google Sheets