Google Sheets

Gantt Chart in Google Sheets: Auto Timeline, Progress Bars & Today Line (Free Template)

The client asks the same question every week: will we open on December 18? In this tutorial you build a free Gantt chart in Google Sheets for a real office renovation, with an automatic timeline, progress bars, a red today line and a status for every task. You use SEQUENCE, WEEKDAY, SPARKLINE, IF and conditional formatting. Formulas only: no Apps Script, no add-ons.

Gantt chart in Google Sheets with auto timeline, progress bars and today line
The Gantt chart you build: 19 tasks, an automatic timeline, progress bars and a red today line.

⬇️  Join Free on Skool & Download the Practice File
Free membership · takes 1 minute · the file is in Classroom → Free Downloads from YouTube
Google Sheets · opens a Make a copy page · Practice + Solution tabs · no add-ons

Watch the Video Tutorial

Video Overview

The 15-minute video builds the Gantt chart on the free practice file for the fictional contractor Summit Ridge Builders in Denver, which is turning an empty office into a dental clinic. There are 19 tasks in 7 stages, from October 26 to the grand opening on December 18. Step 1 counts the days of every task. Step 2 finds the Monday where the timeline starts. Step 3 builds 56 dates with one SEQUENCE formula. Step 4 adds SPARKLINE progress bars, step 5 a status formula and step 6 five KPI cards. Step 7 draws the chart with five conditional formatting rules. The payoff changes a percent done and moves a task by a week, and the chart updates by itself.

Gantt Chart in Google Sheets: What You Will Build

  • An automatic timeline of 56 days (8 weeks) that starts on the Monday of the first task week.
  • Bars for every task from its start date to its end date, with the done part in green.
  • A red today line and a small arrow that move one column every day.
  • A progress bar in every row with SPARKLINE and a status: Done, On Track, Behind, Overdue or Not Started.
  • Five KPI cards: tasks, done, at risk, percent complete and days to go.
  • Weekend shading that only shows where there is no bar.
Finished Gantt chart in Google Sheets with today line and KPI cards
The finished chart: cards on top, task table on the left and the Gantt chart on the right.

The Practice File

The free file opens a Make a copy page, so you get your own editable Google Sheet. The Practice tab has the 19 tasks with start date, end date and percent done already typed in yellow, and everything else empty. The Solution tab has the finished chart to compare with. The business case: the project manager redraws a chart in slides every week, and by Friday it is out of date. A Gantt chart that draws itself fixes that.

Practice tab with the task list and an empty chart area
The Practice tab: only the yellow inputs are typed, the rest waits for formulas.

Step 1: Number of Days

A task that starts on Monday and ends on Wednesday takes three days, not two. In H13 type the end date minus the start date plus one, then double-click the fill handle to copy it down to all 19 tasks:

=G13-F13+1

The plus one matters: a task that starts and ends on the same day still takes one day.

Days formula G13-F13+1 in the Gantt task table
Days = end date minus start date plus one.

Step 2: Timeline Start with MIN and WEEKDAY

The chart should begin on the Monday of the week when the first task starts. In the yellow timeline start cell U8 type:

=MIN(F13:F31)-WEEKDAY(MIN(F13:F31),3)

WEEKDAY with type 3 returns 0 for Monday, 1 for Tuesday and so on, so the earliest start date minus its weekday number is always the Monday of that week. Here it gives 10/26/2026. If the first task moved to a Thursday, the cell would still show the Monday before it.

Timeline start cell showing the Monday before the first task
Timeline start: the Monday of the first task week.

Step 3: Automatic Timeline with SEQUENCE

You need 56 dates, one per day for eight weeks, in the dark row above the chart. In M12 type:

=SEQUENCE(1,56,$U$8)

Press F4 on the timeline start cell to lock it. One formula gives 56 dates, and the cells are formatted to show only the day number. Trap: SEQUENCE needs the cells to its right to be empty. If anything is typed there you get a #REF! error.

SEQUENCE formula creating 56 dates in the timeline row
One SEQUENCE formula fills 56 dates.

For the week labels in the light row above, type =M12 in M11 (formatted as month and day), then copy it into the first cell of every week. Each copy points to the date right below it:

M11  =M12
(copy M11 into T11, AA11, AH11, AO11, AV11, BC11 and BJ11)
Week labels Oct 26 to Dec 14 above the day numbers
Eight week labels, from October 26 to December 14.

One more header: a small red arrow above today. In M10 type:

=ARRAYFORMULA(IF(M12:BP12=TODAY(),"▼",""))

Tomorrow the arrow moves one column to the right on its own, because TODAY changes every day.

ARRAYFORMULA putting a red arrow above today's date
The arrow sits above today’s date.

Step 4: Progress Bars with SPARKLINE

SPARKLINE draws a tiny chart inside a cell. In J13 type the formula below, then double-click the fill handle. A full green bar means the task is 100 percent done, otherwise the bar is blue, and tasks that have not started have no bar at all:

=SPARKLINE(I13,{"charttype","bar";"max",1;"color1",IF(I13>=1,"#548235","#2E75B6")})
SPARKLINE bar chart showing percent done in each task row
SPARKLINE with max 1 draws a bar for the percent done.

Step 5: The Status Formula

One formula answers the client’s question for every task. It checks four things in this order: 100 percent done is Done, today after the end date is Overdue, today before the start date is Not Started, and for tasks in progress it compares the percent done with where the task should be today. In K13 type:

=IF(I13>=1,"Done",IF(TODAY()>G13,"Overdue",IF(TODAY()<F13,"Not Started",IF(I13+0.1<(TODAY()-F13+1)/H13,"Behind","On Track"))))

Where the task should be = (today minus start plus 1) divided by the number of days. If the percent done is more than ten points under that, the task is Behind, otherwise On Track. Fill the formula down to row 31.

Nested IF status formula in column K of the Gantt chart
Done, Overdue, Not Started, Behind or On Track in one formula.

Step 6: Five KPI Cards

The cards on top read the table below:

B9  Tasks         =COUNTA(C13:C31)
D9  Done          =COUNTIF(K13:K31,"Done")
F9  At risk       =COUNTIF(K13:K31,"Behind")+COUNTIF(K13:K31,"Overdue")
H9  % complete   =SUMPRODUCT(H13:H31,I13:I31)/SUM(H13:H31)
J9  Days to go    =MAX(G13:G31)-TODAY()

On the recording day (November 7, 2026) the cards show 19 tasks, 5 done, 0 at risk, 44% complete and 41 days to go. The percent complete is weighted by days, so a long task counts more than a short one.

Five KPI cards: tasks, done, at risk, percent complete and days to go
Five cards: tasks, done, at risk, percent complete and days to go.

Step 7: Draw the Chart with Conditional Formatting

Here is the one rule to remember: when two rules match the same cell, the first rule in the list wins. So add the rules from the most important to the least important: today line, done part, at risk color, normal bar, weekends. Select the chart area M13:BP31, open Format › Conditional formatting, choose Custom formula is and add each rule.

Rule 1: The today line

The formula is written for the top-left cell M13 and then works for every other cell. The dollar sign before 12 locks the date row: the column can change but the row cannot.

=M$12=TODAY()        red #C00000, range M13:BP31
Conditional formatting panel with the today line rule
Rule 1: =M$12=TODAY() colours today’s column.

Rule 2: The done part

A cell is green when its date is on or after the start date and before the start date plus the done days (days times percent done, rounded). Here the dollar is before the letter, because every cell must read the start date in column F.

=AND(M$12>=$F13,M$12<$F13+ROUND($H13*$I13))        green #548235
Conditional formatting rule for the done part of each bar
Rule 2: the done part in green.

Rules 3 to 5: At risk, normal bar and weekends

=AND(M$12>=$F13,M$12<=$G13,OR($K13="Behind",$K13="Overdue"))        orange #F4B183
=AND(M$12>=$F13,M$12<=$G13)        light blue #9DC3E6
=WEEKDAY(M$12,2)>5        light grey #F2F2F2

The at risk rule has nothing to colour until a task falls behind, but it is ready for that day. The weekend rule is last, so the grey only shows where no bar is drawn.

List of five conditional formatting rules in the right order
All five rules in the right order.

The Payoff: Change a Percent and Watch the Chart Update

The site manager calls: Finish and fixture selection is only 30 percent done. Type 30% and the status turns Behind, the bar turns orange with a short green part, the at risk card shows 1 and percent complete drops from 44% to 41%. Put it back to 80% and everything is blue and green again. Then move the plumbing work one week later by changing its start to November 23 and its end to November 30: the bar slides one week to the right on its own. No chart to redraw, no shapes to move.

Task set to 30 percent: status Behind, orange bar and at risk card showing 1
At 30 percent the task is Behind and the at risk card shows 1.
Plumbing rough-in bar sliding one week to the right after the dates change
Change the dates and the bar slides by itself.

Free Gantt Chart vs the Ready-Made Version

Need This free Gantt chart Project Gantt Chart in Google Sheets
Auto timeline, bars, progress, today line, status, cards Yes Yes
Ready-made layout with extra project features – Yes
Price Free Paid

Tips

  • Write every conditional formatting formula for the top-left cell of the range (M13) and let Google Sheets apply it to the rest.
  • Check the dollar signs: M$12 locks the date row, $F13 locks the start column.
  • If SEQUENCE shows #REF!, clear the cells to its right.
  • Drag the rules in the panel if a color hides another: the first rule in the list wins.
  • Each week the team only types the percent done, and everything else updates.
  • Google’s SEQUENCE function help page explains the arguments in detail.

Want It Ready-Made?

This free chart is perfect to learn the idea. For a ready-made version with more features see the Project Gantt Chart in Google Sheets on NextGenTemplates.com. To master spreadsheets step by step, the video course Google Sheets Course: Beginner to Advanced on Next Gen Templates Academy teaches it from the basics. More on this site: the project Gantt chart in Google Sheets, the weekly project plan Gantt chart and the marketing KPI dashboard in Google Sheets.

Frequently Asked Questions

How do I make a Gantt chart in Google Sheets?

List the tasks with a start date, an end date and a percent done. Build a row of dates with SEQUENCE, then colour the cells with conditional formatting rules that use a custom formula such as =AND(M$12>=$F13,M$12<=$G13). Google Sheets has no built-in Gantt chart for this, so formulas and rules draw the bars.

Is this Gantt chart template free?

Yes. The practice file opens a Make a copy page in Google Sheets. It has a Practice tab to build along with the video, a Solution tab with the finished chart and a README tab. It uses only formulas and conditional formatting, so no add-ons or Apps Script are needed.

How does the red today line work?

A conditional formatting rule with the custom formula =M$12=TODAY() colours the column whose date in row 12 equals today. TODAY() changes every day, so the line moves by itself. A small ARRAYFORMULA above the dates adds a red arrow.

Why does SEQUENCE show a #REF! error?

SEQUENCE spills its results into the cells to its right. If any of those cells contains something, you get #REF!. Clear the cells M12 to BP12 and enter the formula in M12 only.

Why does the order of the conditional formatting rules matter?

When two rules match the same cell, the first rule in the list wins. Put the rules in this order: today line, done part, at risk color, normal bar, weekends. Then the green done part sits on top of the blue bar and the weekend grey only shows where there is no bar.

What does WEEKDAY with type 3 do?

WEEKDAY(date,3) returns 0 for Monday, 1 for Tuesday and so on up to 6 for Sunday. Subtracting it from the earliest start date always gives the Monday of that week, which is where the timeline should start.

How is the Behind status decided?

A task is Behind when its percent done plus 10 points is still below where it should be today: (today minus start plus 1) divided by the number of days. Done, Overdue and Not Started are checked first, so only tasks in progress can be Behind or On Track.

Download the Practice File

Make your own copy of the practice file and build the Gantt chart along with the video.

⬇️  Join Free on Skool & Download the Practice File
Free membership · takes 1 minute · the file is in Classroom → Free Downloads from YouTube
Google Sheets · opens a Make a copy page · Practice + Solution tabs · no add-ons

About the Author

PK (Priyendra Kumar) runs NeoTech Navigators, PK: An Excel Expert and NextGenTemplates.com, and builds Google Sheets and Excel tools for small businesses. Summit Ridge Builders, Lakeview Dental Group and every name and date in the sample project are fictional.

Conclusion

A Gantt chart in Google Sheets needs no add-ons or scripts. WEEKDAY finds the Monday, SEQUENCE builds the timeline, SPARKLINE draws the progress bars, one IF formula gives the status and five conditional formatting rules in the right order draw the bars, the today line and the weekends. Then you only type the percent done and the whole chart updates by itself.

PK
Written by PK (Priyendra Kumar)
Microsoft Certified Professional with 15+ years in data analysis, dashboards and automation. Creator of PK: An Excel Expert and NeoTech Navigators on YouTube. About the author
Tested in the real tool · Last reviewed November 17, 2026
PK
Meet PK, the founder of NeotechNavigators.com! With over 15 years of experience in Data Visualization, Excel Automation, and dashboard creation. PK is a Microsoft Certified Professional who has a passion for all things in Excel. PK loves to explore new and innovative ways to use Excel and is always eager to share his knowledge with others. With an eye for detail and a commitment to excellence, PK has become a go-to expert in the world of Excel. Whether you're looking to create stunning visualizations or streamline your workflow with automation, PK has the skills and expertise to help you succeed. Join the many satisfied clients who have benefited from PK's services and see how he can take your data analysis skills to the next level!
https://neotechnavigators.com