How to Make a Gantt Chart in Google Sheets

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

In this tutorial, we will discuss two different ways to create Gantt charts in Google Sheets. We will also show you how to make a simple Gantt chart template from scratch that you can easily share or download.

What are Gantt Charts 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 sort of 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 y needs to be completed so the project can be delivered on time.

You can use a Gantt chart 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.

How to Make a Gantt Chart in Google Sheets

A Gantt chart mainly consists of bars that reflect the start, duration, and end of tasks in different projects. The left part of the chart (or template) usually consists of a list of activities in each project, while the top portion displays a suitable time scale.

In Google Sheets, 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

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

Google Sheets does not yet have an option to create a Gantt chart, but here’s a workaround. You can use the Stacked bar chart that Google Sheets offers and customize it to make a Gantt chart!

Here’s how:


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

  1. 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 tasks shown in the screenshot below:

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

  1. Copy the same tasks to this new table.

how to make a gantt chart in google sheets

  1. In the first cell under the Start Day column (cell B8 in our example), type the formula:
     =INT(B2)-INT($B$2).
  2. 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 after how many days from the starting date each task should begin.

  1. In the first cell under the Duration column (cell C8 in our example), type the formula:
     =C2-B2.
  2. 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.

create gantt chart in google sheets

  1. 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’.

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

 gantt chart google sheets

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

 gantt chart google sheets

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

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

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

 gantt chart google sheets

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.

Creating a Gantt Chart by Using an In-built Template

If you want a more advanced Gantt chart that displays the status of a task, percentage of a task completed, etc. then an in-built Gantt chart template might be a good option. Smartsheet provides several free Google Sheets templates, including one for a Gantt chart.

To use this template, follow the steps below:

  1. From the File menu, select New->From template.

create gantt chart in google sheets

  1. This will open the Google Sheets Template gallery.

  1. Under the Project management section, select ‘Gantt chart’.

create gantt chart in google sheets

  1. Click on the thumbnail to open the template.

 gantt chart google sheets

You can now start entering information about your projects and tasks. You can also further customize the template to suit your requirements.

You will notice that this template is not automated. In other words, the Gantt Chart area does not automatically reflect the start and due dates. You need to physically change the Fill color of individual cell blocks according to the Duration.

how to make a gantt chart in google sheets

Now, you could tweak the existing template to automate some things. However, sometimes it is easier to build a new template from scratch than to attempt to correct an existing one. But, you can also use some of the steps outlined below to customize the in-built template too.

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 template that will help you track your tasks easily and efficiently.

  1. How to Create a Gantt Chart In Google Sheets
  2. 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.

  1. 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.
  2. 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 to 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:

Click here to view the Gantt Chart template Outline.

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.

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

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

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

  1. Select the Color scale tab.

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

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

create gantt chart in google sheets

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

how to make a gantt chart in google sheets

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.

  1. 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:

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

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

 gantt chart google sheets

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.

  1. 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”

  1. 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’.

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

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

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

how to make a gantt chart in google sheets

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

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

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

 gantt chart google sheets

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

how to make a gantt chart in google sheets

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

  1. 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:

how to make a gantt chart in google sheets

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. Exporting Your Google Spreadsheets Gantt Chart

That’s it! We’ve finished our own Gantt chart template 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

Download the Google Sheets Gantt Template

If you don’t want to go through building it from scratch, you can just download our template from here and use it as needed.

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

Nahid

Nahid

Nahid Akhter is a writer, programmer, and online course content creator. She has an MS in Computer Science and has been in the education line for more than 14 years. Her specialization is in programming and Tech-writing, and her areas of interest include Office productivity, Artificial Intelligence, and Web design and development.

Best Online Google Sheets Courses

Become a Google Sheets expert today!
View Course