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. 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 are going to 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 clearly visualize the breakdown of a project schedule. These tasks are presented in a Gantt chart in the form of a sort of timeline. Each activity is presented as a bar. The position and length of the bar represents the start date, duration, and ending date of the activity / task.

This helps anyone see at a glance:

  • The activities in the project
  • The relative duration of each activity
  • At what 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 essentially need to be completed in time so that 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 changes in schedules 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 by 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 create a Gantt chart!

Here’s how:

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

  • Make three columns with the following headers:
    • Task Name
    • Starting Date
    • Ending date
  • Fill in the cells of each of these columns with the appropriate task details. If you want to just practice, you can use the imaginary tasks shown in the screenshot below:
  • 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
  • Copy the same tasks to this new table.
how to make a gantt chart in google sheets
  • In the first cell under the Start Day column (cell B8 in our example), type the formula: =INT(B2)-INT($B$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.
  • In the first cell under the Duration column (cell C8 in our example), type the formula: =C2-B2.
  • 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).

Now we are ready to create the Gantt chart. Note that we will only be using the second table to create the chart, since it has everything we need.

  • Select the second table from the worksheet (cells A7:C11 in our example).
  • From the Insert menu, select the Chart option.
create gantt chart in google sheets
  • 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’.
  • 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
  • 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
  • From the Chart editor, select the Customize tab.
  • Under the ‘Series’ section, make sure that ‘Start day’ is selected from the dropdown menu, just under the word, ‘Series’.
  • 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 rather 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 status of task, percentage of task completed, etc. then an in-built Gantt chart template might be a good option for you. Smartsheet provides a number of free Google Sheets templates, including one for a Gantt chart.

To use this template, follow the steps below:

  • From the File Menu, select New->From template.
create gantt chart in google sheets
  • This will open the Google Sheets Template gallery.
  • Under the Project management section, select ‘Gantt chart’.
create gantt chart in google sheets
  • 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 own 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 just build a new template from scratch than to attempt to correct an existing one.

Creating a Gantt Chart Template from Scratch in Google Sheets

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 are now going to make a really simple Gantt chart template that will help you track your tasks easily and efficiently.

To create a Gantt chart template, we 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.

We then add in the formulas to calculate duration, days and days of the week. Following this, we use conditional formattingto 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 color scale.

After the main skeleton is ready, we can start filling in the basic details, and then begin entering each Task title, Start date, End date and Task owner entry. Once this is done, the sheet will automatically calculate the Duration and display the Gantt chart by filling in the relevant cells in dark blue.

As soon as you make a change to the Start or End date of a project task, the Gantt chart will get automatically updated 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 outline 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. This 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 date 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 within which the task needs to be completed. For this, enter the following formula in cell F10:

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

Then, use the fill handle to copy the formula down to the rest of the cells of column F.

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 cell of the column shows a darker color as the percentage completed increases, with 0% being completely white and 100% being completely dark cyan.

To achieve this, follow the steps listed below:

  • Select the cells of column G, starting from cell G10 all the way down.
  • From the Format menu, select Conditional formatting.
  • This will open the Conditional format rules sidebar on the right.
  • Select the Color scale tab.
  • Click on the color scale bar under ‘Preview’ and select the ‘white to green’ option, as shown in the screenshot below:
  • 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
  • Click Done.

Now each cell in this column will be filled with the relevant color on the color 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.

This means that if the date in cell D10 is 3/12/2021, then 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 displaying days 1 to 30, since April has 30 days.

We want to start from 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 the month 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.

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 navigating to Format->Number->More Formats->Custom number format and then typing “d” in the input box, before pressing the Apply button.

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

Now we want to display the rest of the days of the month by simply adding one day to the previous cell. This is quite easy. Simply type the following formula in cell I8:

=H8+1

Then drag the fill handle of this cell to the right, so that 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, then you will have days of the week starting from Monday, since March 1st , 2021 is a Monday.

Here’s the formula we use in cell H9 to display the day of the week, sequenced according to the corresponding date in row 8:

=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”

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:

  • Select the Gantt Chart area. In our example, we selected the cells in the range H10 to BR22.  
  • Navigate to Format->Conditional Formatting.
  • This opens the Conditional format rule sidebar on the right.
  • Click on the dropdown menu under ‘Format cells if…’ and select ‘Custom formula is’.
  • A new input box will appear just below the dropdown. Enter the formula =H$9=”S” in this input box.
  • Under ‘Formatting style’, select the formatting you want to apply to the weekend cells. We set the fill color to light blue.
  • 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.

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 Gantt chart bars based on the Start and End dates of each task.

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

Here are the steps that you need to follow:

  • Select the Gantt Chart area. In our example, we selected the cells in the range H10 to BR22.  
  • Navigate to Format->Conditional Formatting.
  • This opens the Conditional format rule sidebar on the right.
  • Click on ‘Add another rule
how to make a gantt chart in google sheets
  • Click on the dropdown menu under ‘Format cells if…’ and select ‘Custom formula is’.
  • A new input box will appear just below the dropdown. Enter the formula =AND(H$8>=$D10,H$8<=$E10) in this input box.
  • Under ‘Formatting style’, select the formatting you want to apply to the weekend cells. We set the fill color to dark blue.
  • 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

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.

 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 also 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), the cell H10 does not get formatted in dark blue.

Similarly, if we are in cell T10, then we check if the date in T8 is greater than or equal to the date in cell D10 and also 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), the cell T10 gets formatted in dark blue.

That’s it! We are now done with our own Gantt chart template built from scratch! You can also choose to further customize this template to suit your requirements.

If you don’t really want to go though the whole process of 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 in which you can create a Gantt chart in Google Sheets. We hope this was useful and fun!

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.