Gantt Chart Template Google Sheets

Building a Gantt Chart in Google Sheets

A Gantt chart is just a spreadsheet where you map tasks against dates and color the cells accordingly. That's literally all it is. People complicate it, but once you get the basic structure down, you can modify it for almost any project size. The template you want has columns for Task Name, Start Date, Duration (in days), End Date, Progress percentage, and then a date header row that spans across in daily increments. The visual bars come from conditional formatting tied to those date ranges. Here's how I built mine after burning through three different premade templates that broke as soon as I added more than twenty tasks.

Setting Up the Structure

Column A is your task list. Column B is the start date. Column C is duration. Column D is end date with this formula: =B2+C2-1. Subtract one because if something starts on Monday and takes one day, it ends on Monday, not Tuesday. Column E is progress, usually a percentage. Columns F onward are your date headers. Put individual dates across the top row starting in column F, one per cell, formatted as short dates.

The Conditional Formatting Piece

This is where most people give up. You need conditional formatting rules that paint a cell based on whether a given date falls between the start and end date of that task. Select your entire data grid, go to Format > Conditional formatting, and set the formula custom expression: =AND($F$1>=B2,$F$1

=D2)

Adjust the $F$1 reference to match whatever your first date column header is. Set the fill color to whatever you want your bars to be. Click Done and watch the bars appear.

Adding Progress Bars

You can layer a second conditional format rule on top for partial completion. Use the progress column and split the bar visually. The workaround I use is creating a secondary range using a helper column with this formula: =B2+INT((C2-1)*E2). This gives you an "end of progress" date that shifts as percentage changes. Then apply conditional formatting with =AND($F$1>=B2,$F$1

=HelperColumn)

What Goes Wrong in Practice

For one project, I had a team member whose start dates kept shifting because they were copy-pasting from Outlook without converting the format. Google Sheets would quietly accept the text string as a date but the conditional formatting would break because it treated it as text. Every bar disappeared and I spent twenty minutes debugging before I realized the cells had a subtle green triangle in the corner. The fix was selecting the range, going to Data > Text to numbers, and forcing them to real date values. After that everything snapped back into place. Another issue: weekends. If you're tracking calendar days, your bars stretch through Saturday and Sunday. That's technically correct for many projects but looks misleading. You can handle this by using WORKDAY and WORKDAY.INTL functions in your duration calculation instead of simple subtraction. It makes the end date formula =WORKDAY(B2,C2-1) instead of =B2+C2-1.

Limitations You Should Know

Google Sheets Gantt charts hit a wall around fifty to sixty tasks in my experience. Beyond that, the conditional formatting rules multiply and the sheet gets sluggish. Refreshing becomes noticeable. Scrolling left and right through a date range that stretches three months feels like wading through something thick. Another limitation: there's no native drag-and-drop editing. If you change a start date or duration, you update the cell and the bar moves. That's it. You cannot click and drag the bar itself like you can in Microsoft Project or dedicated tools like Asana or Monday. Dependencies between tasks are essentially nonexistent. There's no way to link a finish-to-start relationship where one task's end date automatically pushes another task's start date. You have to do that manually or build a complex circular-reference workaround that breaks every time someone touches the wrong cell.

When Sheets Works and When It Doesn't

Use this approach for small to medium projects where the team already lives in Google Workspace, where you need transparency and easy sharing, or where budget doesn't allow for project management software. For anything above sixty tasks, or where dependencies matter, you should look at something purpose-built. There are also third-party add-ons like Gantt Chart by ChartExpo that attempt to bridge the gap, but they add cost, create dependency on external providers, and introduce their own failure modes. I've seen a sheet go broken after an add-on update because the new version changed how formulas referenced the date columns.

Quick Reference Formulas

End date: =B2+C2-1 Workday end date: =WORKDAY(B2,C2-1) Progress end helper: =B2+INT((C2-1)*E2)

Weekend-aware duration adjustment: =C2-(NETWORKDAYS.INTL(B2,D2,1)-1) where 1 represents Saturday and Sunday as weekends The exact weekend code depends on your region and which days you consider non-working. Check the NETWORKDAYS.INTL documentation for the full list of codes before you commit to one.

Sharing and Maintenance

Set the sheet to "view only" for stakeholders who don't need to edit. Give the project manager edit access. Keep a separate sheet tab for raw data entry if you want to avoid accidental formula deletion. I once had someone accidentally overwrite a conditional formatting rule by clicking a cell while scrolling and it took me fifteen minutes to rebuild the rules from memory. Version history is your safety net. Right-click the sheet tab and select Version history > See version history. You can restore previous states if someone breaks the structure. The feature costs nothing and saves a lot of headaches.