How to Create a Gantt Chart Google Sheets Template

Creating a Gantt chart is a powerful way to visualize your project timeline, track dependencies, and keep everyone on the same page. While specialized project management software exists, you can build a surprisingly flexible and collaborative Gantt chart right inside Google Sheets. This tutorial will walk you through creating a dynamic Gantt chart template from scratch, step by step.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

What is a Gantt Chart (and Why Use One in Google Sheets)?

A Gantt chart is a type of bar chart that illustrates a project schedule. Each horizontal bar in the chart represents a task, and the length of the bar shows its duration, from start to finish. It lets you see at a glance what needs to get done, who is responsible, and when it's supposed to happen.

So, why build one in Google Sheets instead of another tool?

• Accessibility: It’s free and available to anyone with a Google account.
• Collaboration: Just like any other Google Sheet, you can easily share it with your team for real-time updates and comments.
• Customization: You have complete control over the layout, formatting, and functionality to match your project's specific needs.
• No Learning Curve: You're working in a familiar spreadsheet environment, not learning a whole new piece of software.

How to Build Your Gantt Chart Template (Step-by-Step)

Let's build a Gantt chart for a common project: launching a new marketing campaign. We'll start with the data and then bring it to life visually.

Step 1: Set Up Your Project Data Table

First, we need to create a table that holds all the project information. Open a new Google Sheet and create columns for the essential project details. We'll use:

• Task: The main to-do item (e.g., "Create Ad Creatives").
• Owner: Who is responsible for the task.
• Start Date: The day the task begins.
• End Date: The day the task is completed.
• Duration (Days): How many business days the task will take.

Your initial setup should look something like this:

Populate it with your project's tasks. Make sure to format the Start Date and End Date columns by going to Format > Number > Date.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step 2: Automatically Calculate Task Duration

You can manually enter the duration, but it's much better to calculate it automatically based on your start and end dates. This way, if you adjust a date, the duration updates instantly. For this, we'll use the NETWORKDAYS formula, which cleverly calculates the number of working days between two dates, automatically skipping weekends.

In cell E2 (or your first duration cell), enter the following formula:

=NETWORKDAYS(C2, D2)

You might notice a negative result or error if your end date isn't filled in yet, that's okay. Once you have a start and end date, it will calculate correctly. After entering the formula in the first cell, click the small blue square (the fill handle) in the bottom-right corner of the cell and drag it down to apply the formula to the rest of your tasks.

Step 3: Build the Visual Timeline

Now, let's create the calendar part of the chart. To the right of your data table, create a horizontal timeline of dates.

1. Pick a starting cell, like G1. Enter the start date of your project (or just the first date you want to see in your timeline).
2. In the next cell to the right (H1), enter a formula that adds one day to the previous cell: =G1+1.
3. Click on cell H1 and drag the fill handle to the right across as many columns as you need to cover your entire project timeline.

Your top row is now populated with sequential dates. To make it more readable, you can format these cells to show just the day of the month or initials for the day of the week. For example, select the entire timeline row and go to Format > Number > Custom number format and enter d to show just the calendar day (e.g., 1, 2, 3) or ddd for the day of the week abbreviation (e.g., Mon, Tue, Wed).

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step 4: Use Conditional Formatting to Create the Bars

This is where the chart comes to life. We will use a powerful feature called Conditional Formatting to automatically 'light up' the cells that fall within a task's date range, creating our visual bars.

The logic we want to create for each cell is simple: "If the date in this cell's column is on or after the task's Start Date AND on or before its End Date, color this cell green."

Here’s how to set it up:

1. Select the area where your Gantt chart bars will appear. Continuing our example, this would be the range starting at G2, going all the way across your timeline and down to your last task.
2. Go to the menu and click Format > Conditional formatting.
3. Under the “Format rules” dropdown, select Custom formula is.
4. In the formula box, enter the following formula. Pay close attention to the dollar signs ($) — they are very important!

=AND($C2<=G$1, $D2>=G$1)

Let’s quickly break down this formula and the crucial $ signs:

• =AND(...): This function checks if all conditions inside it are true.
• $C2<=G$1: Checks if the Start Date for the task in this row ($C2) is earlier than or equal to the date in this column's header (G$1). The $C locks the column, so we always look at the start date column, while the 2 is relative, so it adjusts for each task row. The G$1 locks the row in the header, so it always refers to the timeline date.
• $D2>=G$1: Checks if the End Date for the task ($D2) is later than or equal to the timeline date.

Now, choose your desired formatting style. A solid background color works best. Pick a color, click "Done," and your Gantt chart bars will magically appear!

Now, whenever you update the start or end dates in your table, the visual bars will adjust automatically.

Making Your Gantt Chart Template Even Better

You now have a fully functional Gantt chart. But why stop there? Here are a few enhancements to make your template more powerful.

Add Task Progress Bars

Create a new column called "% Complete" and format it as a percentage. Now, for the cool part. We can use the SPARKLINE function to create an in-cell progress bar.

In the cell next to your first completion percentage (e.g., inside column F, next to your new percentage column), use this formula:

=SPARKLINE(F2, {"charttype","bar","max",1,"color1","#4caf50"})

This formula creates a simple bar chart. "Max,1" tells it that 100% (or 1) is a full bar. Drag this down, and you’ll have dynamic progress bars that update as you change the percentage values.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Visually Highlight Today’s Date

Help your team see where you stand on any given day. To highlight the current day, add another conditional formatting rule to your timeline area (your selection from G1 down to the last row).

• Select the range again (e.g., G1:Z10).
• Add a new conditional formatting rule.
• Choose "Custom formula is."
• Enter this simple formula: =G$1=TODAY()
• Set a different formatting style for this — a bright colored border or a slightly different background shade works well. Now, the column for today’s date will always be highlighted.

Highlight Weekends

Finally, to make your timeline even easier to read, you can grey out weekend columns. Add one more conditional formatting rule to the same range.

• Select the range (G1:Z10).
• Add a new rule with "Custom formula is."
• Use this formula: =OR(WEEKDAY(G$1)=1, WEEKDAY(G$1)=7)
• This checks if the day of the week for the date in row 1 is a Sunday (1) or a Saturday (7).
• Set a subtle formatting style, like a light grey background, and move this rule below your main taskbar rule so it doesn't override it.

Final Thoughts

By spending a few minutes setting up some formulas and rules, you can create a dynamic, highly-functional Gantt chart template inside Google Sheets. It's a fantastic, no-cost solution for managing project timelines collaboratively without forcing your team to learn or pay for a new tool.

While Google Sheets is exceptional for organizing project plans, reporting on marketing and sales performance often involves a messy process of downloading CSVs and spending hours wrangling data. We created Graphed to completely eliminate that friction. It connects to your data sources like Google Analytics, HubSpot, Shopify, and Facebook Ads, letting you build real-time dashboards and get answers instantly by just describing what you want to see in plain English — no formulas needed.