We can effortlessly create a Gantt Chart in Google Sheets by employing a few formulas: two for highlighting and additional formulas for timescale and duration.

I have previously published tutorials on chart preparation in Google Sheets. These tutorials encompassed various chart types, including combination charts, line graphs, bar charts, progress curves, and more. You can locate these tutorials in the CHARTS menu on the navigation bar above.

Now, let’s delve into a straightforward approach to creating a Gantt Chart using formulas and conditional formatting in Google Sheets.

Introduction

Gantt Charts, named after Henry L. Gantt, share visual similarities with bar charts, but their intended use differs.

These charts find extensive application in preparing project schedules, representing each activity with a designated work start and end date through visual bars on the Gantt Chart.

Various applications are available for Gantt Chart creation, including some options that may involve costs for individual use.

In my professional experience, I have utilized MS Project and Primavera, both known for their flexibility in calculating factors such as man-hours and costs. However, mastering these applications might necessitate some training.

Alternatively, you can create a functional Gantt Chart in Google Sheets without spending a single penny.

If you’re interested in crafting a straightforward Gantt Chart for your project, follow the tutorial below. We will guide you through the process of preparing a Gantt Chart using formulas in Google Sheets, Google’s spreadsheet application.

How to Create a Gantt Chart Using Formulas in Google Sheets

This involves mainly three parts: preparing the layout (formatting), applying formulas, and then implementing highlight rules.

We will begin with layout preparation. I have included the free template in the last part of this tutorial.

Layout Preparation

We need to list a few tasks along with their start and end dates, which are crucial for creating a Gantt Chart. Let’s get started by entering this information.

1. Enter the task names in cells A5:A.
2. Input the tasks’ start and end dates in cells B5:B and C5:C, respectively.
3. Remove the remaining rows at the bottom. To remove:
   • Click on the first row you want to delete.
   • Press the Shift + Down arrow key to select the rows you want to delete.
   • Right-click on any of the selected rows.
   • Choose “Delete rows” from the context menu.

We will add more rows, if necessary, after applying all the formulas and conditional format rules. Then only they will inherit the formatting from the rows above. For more details about this feature, refer to my tutorial here: How to Create Self-Formatting Tables in Google Sheets (With a Simple Initial Setup).

Formulas for Timescale, Days of the Week, Duration, and Label

Here are the formulas (4 nos.) that we will use within cells, not within conditional formatting rules. Although you can manually enter dates in the timescale and calculate the duration, these formulas simplify those tasks.

Here are the formulas and their roles in creating the Gantt Chart in Google Sheets.

Duration (Formula #1):

In cell D5, use the following formula to calculate the duration of each task in D5:D.

=ArrayFormula(IF(A5:A="", ,C5:C-B5:B+1))

This formula checks if A5:A is blank. If true, it returns an empty string; otherwise, it calculates the duration by subtracting the start date from the end date and adding 1.

Timescale (Formula #2):

To generate the timescale, enter the following formula in cell E3, covering the range E3:3.

=SEQUENCE(1, MAX(C5:C)-MIN(B5:B)+2, MIN(B5:B))

Then, select the range E3:3, navigate to Format > Number > Custom number format, and input dd to format the date.

What does this formula do?

The SEQUENCE function returns values in 1 row and MAX(C5:C)-MIN(B5:B)+2 (representing the project start date to the project end date, plus 2 additional columns) columns, starting the sequence from MIN(B5:B) (the project start date).

Days of the Week (Formula #3):

In cell E4, input the following formula to display the days of the week.

=ArrayFormula(TEXT(SEQUENCE(1, MAX(C5:C)-MIN(B5:B)+2, MIN(B5:B)), "DDD"))

This formula utilizes the SEQUENCE function within the TEXT function to return the days of the week.

Now, delete all blank columns, if any, at the end of the third row. Select the first blank column, press the Shift + Right arrow key, right-click on the selection, and choose “Delete columns” from the context menu.

Labelling Task Names at the End of the Bar (Formula #4):

If you wish to have task names appear at the end of the bar, insert the following formula in cell E5.

=ArrayFormula(if(E3:3=C5:C+1, A5:A,))

This formula checks if the timescale date is equal to the task end date + 1. If true, it returns the corresponding task name from A5:A.

We have covered all the formulas for creating the Gantt Chart in Google Sheets. The remaining components are the highlight rules.

Highlight Rules (Formulas) for the Gantt Chart

We will employ two highlight rules (formulas) for the Gantt Chart: one for the bars and the other for highlighting the weekends.

Gantt Chart Formula for Highlighting the Bars:

Use the following formula within conditional formatting to accurately create the bars:

=AND($B5<>"", E$3>=$B5, E$3<=$C5)

Please follow the below steps to insert this formula:

1. Select the entire bar area (e.g., E5:Z10).
2. Go to Format > Conditional Formatting.
3. Under Format rules, select Custom formula is.
4. Paste the formula: =AND($B5<>"", E$3>=$B5, E$3<=$C5)
5. Click Done.

Formula Explanation:

Conditional formatting operates based on the principles of absolute and relative references. We can make rows, columns, or both relative or absolute in the highlight rule formula by inserting dollar symbols. The above Gantt Chart bar highlight rule (formula) serves as an example of this concept and works as follows:

In the rule-applied range, i.e., E5:Z10, in each cell, the formula will adjust as follows:

• E$3 (timescale start date) will become F$3, G$3, H$3, and so on in columns, with no changes in rows.
• $B5 and $C5 (first task start and end dates) become $B6 and $C6, $B7 and $C7, $B8 and $C8, and so on in rows, with no changes in columns.

Condition:

The formula highlights a cell if the timescale date in the current column falls between the start and end dates for the task in the current row, and the task’s start date in the current row is not blank. Please see the screenshot below:

Highlighting Weekends Based on the Timescale:

Once again, click on Format > Conditional formatting. On the sidebar panel, click “Add another rule.”

Paste the following rule:

=AND(ISDATE(E$3), OR(WEEKDAY(E$3)=1, WEEKDAY(E$3)=7))

This formula checks if the value in the current cell corresponding to the timescale is a valid date, and if the weekday number of that date is 1 or 7 (Sunday or Saturday). If it evaluates to TRUE, the formula highlights that cell.

Free Gantt Chart Template and Additional Resources

Above, I’ve explained how to easily create a Gantt Chart using a few formulas in Google Sheets. This will possibly meet your basic scheduling requirements.

Here is the template that we used for the explanation purposes above.

Gantt Chart Free Template

Additional Resources:

1. Basic GANTT Chart in Google Sheets Using Stacked Bar Chart
2. Create a Gantt Chart Using Sparkline in Google Sheets
3. Split a Task in a Custom Gantt Chart in Google Sheets
4. Multi-Color Gantt Chart in Google Sheets
5. Days Remaining in Gantt Chart in Google Sheets
6. Date Filter in Gantt Chart in Google Sheets
7. GANTT_CHART Function in Google Sheets (Named Function)