How to Create a Dynamic Gantt Chart in Google Sheets (Step-by-Step)

Managing project timelines requires a clear visual breakdown of task schedules, start dates, and dependencies. While specialized project management software exists, building an interactive Gantt chart directly in Google Sheets provides a flexible, lightweight, and completely customizable solution for tracking project progress in real time.

Rather than relying on clunky third-party add-ons, this guide demonstrates how to create a dynamic Gantt chart using Google Sheets' built-in conditional formatting rules and simple date formulas. When task dates or durations change, your timeline chart updates instantly.

Step 1: Set Up the Project Task Table

Start by structuring your basic project data columns on a fresh sheet tab named Gantt_Chart.

1. Set up your table headers in row 4 across columns A through D:
   • Cell A4: Task Name
   • Cell B4: Start Date
   • Cell C4: Duration (Days)
   • Cell D4: End Date (Formula)
2. Enter your project tasks in A5:A12 and set the baseline project start dates in B5:B12.
3. Enter the estimated number of workdays required for each task in C5:C12.

In cell D5, enter the formula to calculate the task completion date automatically, then drag it down across your task rows:

=WORKDAY(B5, C5, Holidays!$A$2:$A$20)

Pro Tip: Using WORKDAY instead of standard addition (B5 + C5) ensures Saturdays, Sundays, and optional company holidays are automatically excluded from your task timelines.

Step 2: Build the Dynamic Calendar Timeline Header

Next, create the horizontal date axis across the top of your chart that serves as the calendar timeline grid.

1. In cell E4, link to your project's overall start date: =MIN(B5:B12).
2. In cell F4, enter the formula to increment the calendar by one day: =E4 + 1.
3. Copy cell F4 horizontally across columns to the right (e.g., through column Z4 or AH4) to cover your project's total duration.
4. Highlight E4:AH4 and format the dates cleanly via Format > Number > Custom date and time (choose short day/month formats like "MMM d" or "d").

Step 3: Create the Automated Progress Bars with Conditional Formatting

To turn the empty grid below your calendar header into visual progress bars, apply a custom conditional formatting formula that shades cells corresponding to active task dates.

1. Highlight the entire timeline grid body area where progress bars will draw (e.g., select range E5:AH12).
2. Navigate to the top menu and select Format > Conditional formatting.
3. In the sidebar panel under Format cells if..., select Custom formula is.
4. Enter the following dual-condition formula into the text field:

=AND(E$4>=$B5, E$4<=$D5)

Formula Logic Breakdown:

• E$4 locks row 4 so the formula constantly evaluates the current date in the timeline header.
• $B5 and $D5 lock columns B and D so the formula checks the specific row's Task Start Date and End Date.
• The AND function returns TRUE only when a timeline cell date falls on or between a task's start and completion dates, triggering the fill color automatically.

Step 4: Highlight Today's Date and Weekends

To make your Gantt chart look professional and easy to read during daily standups, add visual indicators for the current date and weekend days.

Highlight Today's Date (Vertical Cursor Line):

1. Keep range E5:AH12 selected and click + Add another rule in conditional formatting.
2. Choose Custom formula is and enter: =E$4=TODAY().
3. Choose a bright fill color (such as soft yellow or light blue) or a thick border style to highlight the active day.

Grey Out Weekends:

1. Add another rule using Custom formula is: =WEEKDAY(E$4, 2)>5.
2. Select a light grey background fill to clearly mark non-working days on the grid.

Summary of Master Gantt Formulas