How to Make a Gantt Chart in Google Sheets

There is no Gantt chart in the Google Sheets chart list, but there are three reliable ways to make a Gantt chart in Google Sheets. Timeline view is Google’s own answer, if your account has it. A stacked bar chart with a hidden first series works in every account, and it is the same trick Microsoft documents for Excel. A grid of colored cells, driven by conditional formatting, is the third and the most flexible. This guide covers all three, with a template table you can paste in.

Start With a Clean Task Table

All three methods read the same kind of table. Build it first, in a new sheet, starting in cell A1.

A Template to Paste

Copy the table from this page and paste it into Sheets. The Start day and Duration values are typed here so you can see them, but in your own plan they should be formulas:

• Start day in D2: =B2-$B$2, days since the project began.
• Duration in E2: =DAYS(C2,B2), or simply =C2-B2.

Google’s documentation for DAYS says it returns the number of days between two dates and factors in leap days. Its DATEVALUE page adds that dates work as whole numbers in formulas, which is why plain subtraction works too.

Dates Must Be Real Dates

Type dates the way your locale expects, or they arrive as text. Google’s help for DATEVALUE notes that the formats it understands depend on region and language settings, and suggests a quick test: type a sample date into an empty cell and see whether Sheets recognizes it. Timeline view in particular refuses to work without at least one proper date column.

Working Days

If your team does not work weekends, count working days with =NETWORKDAYS(B2,C2). Google describes NETWORKDAYS as returning the number of net working days between two dates, with an optional list of holidays. Keep the Start day column in calendar days, so the bars still sit at the right place on the calendar.

Method 1: Timeline View

Timeline view is the closest thing Sheets has to a built-in Gantt chart. Google’s help page describes it as an interactive visual layer for project tasks, marketing campaigns, schedules and similar plans.

Who Can Use It

Google publishes a list of eligible Workspace editions: Essentials, Business Starter, Business Standard, Business Plus, Enterprise Essentials, Enterprise Starter, Enterprise Standard, Enterprise Plus, Education Fundamentals, Education Standard, Education Plus and Frontline. A personal account is not on the list, so if you use Sheets with a regular free account, expect not to see the option and use method 2 or 3 instead.

How to Build It

1. Make sure the table has a task name column and at least one column in date format. Google recommends a start date and an end date, or a duration instead of the end date.
2. At the top of Sheets, open the Insert menu and choose Timeline.
3. Select your data range and click OK. Sheets adds a new “timeline view” tab.
4. In that tab, open Settings to choose which column is the card title, start date and end date. Optional fields are card color, card detail and card group.

Use Card group to put all tasks of one owner or one phase on the same row. That is what makes the view feel like a Gantt chart.

Things That Go Wrong

• A card shows a single day. Google lists two causes: the dates are not in an accepted date format, or the start and end dates are the same.
• A card has no length. Each start date must be earlier than its end date.
• You cannot change a card’s color. Google warns that conditional formatting on the source data blocks changing the card color from the card itself.

Method 2: A Stacked Bar Chart

This works in every Google account. It is the same method Microsoft documents for Excel: stack the start and the duration, then make the start invisible.

Step 1: Select Three Columns

Select the Task column, then the Start day and Duration columns, with Start day first. Hold Ctrl (Command on a Mac) to add D1:E7 to A1:A7. Google’s bar chart help says labels in the first column appear on the vertical axis and each row becomes a bar, which is exactly the layout a Gantt chart needs.

Step 2: Insert a Stacked Bar Chart

Choose Insert, then Chart. In the chart editor, open Setup, and under Chart type pick Stacked bar chart. Google notes that a stacked bar chart needs at least two data series; you have two, Start day and Duration.

Step 3: Hide the Start Series

Open Customize, then Series, and choose the Start day series from the list. Google’s help lists color and opacity among the things you can change on a chart. Set the fill opacity of that series to 0, or give it the same color as the chart background. The duration bars now float at the day each task starts.

Step 4: Clean Up

• Legend: double-click it on the chart and press Delete, which Google documents for removing chart items. One of its two entries is invisible anyway.
• Task order: if the first task sits at the bottom, look for the reverse axis order setting under the axis options in Customize.
• Axis range: if you charted real dates instead of day numbers, set the minimum of the value axis to your first start date; Google’s bar chart page lists min and max values among the axis options.
• Gridlines: add them from Customize, then Gridlines, to make the weeks easier to read.

Method 3: A Colored Grid With Conditional Formatting

The grid method does not use a chart at all. You put one column per day or per week to the right of the task table and let conditional formatting color the cells that fall inside each task. It is how many printed project plans look, and it copies cleanly into Docs.

Build the Date Row

In F1, type the first date of the project. In G1, type =F1+1 for days or =F1+7 for weeks, and fill it right for as many columns as the project needs. Narrow the columns so the grid looks like a calendar.

Add the Rule

Select the grid, for example F2:AZ7, then choose Format, then Conditional formatting. Under “Format cells if”, pick Custom formula is, and enter:

=AND(F$1>=$B2, F$1<$C2)

Pick a fill color and click Done. Google’s help explains the two rules this formula relies on: you write the rule for the first cell of the range, and the dollar signs keep a reference fixed while the rest moves. Here, F$1 always looks at the date row, and $B2 and $C2 always look at the start and end of the task on the same row.

With weekly columns the formula needs a small change, since a task can start or end mid-week. Use =AND(F$1+6>=$B2, F$1<$C2), which colors any week that overlaps the task. Here is the result for the whole plan, one column per week starting on Monday:

Testing starts on Friday, November 13, so it touches two weekly columns even though it lasts only 7 days. Daily columns avoid that blur.

Add a Today Column and Status Colors

Add a second rule, =F$1=TODAY(), with a light fill, to mark the current day in a daily grid. For status, keep a Status column in the task table and add a rule that tests it on the same row, following the pattern of Google’s own example for coloring a row by a value, =$B1="Yes". Google notes that rules are evaluated in the order listed and the first true rule decides the format, so put the most important rule at the top.

Keep the Plan Up to Date

A Gantt chart is only as good as its last update. All three methods redraw themselves from the table, so the work is in keeping the table honest.

Link Tasks With Formulas

When one task cannot start until another ends, make its start date a formula instead of a typed date. In the template, =C2 in B3 makes Design start the day Research ends. Change Research’s end date and Design, its bar and its colored cells all move with it. Chain the rest the same way and a single delay ripples through the whole plan, which is exactly what you want to see.

Add Progress Without a New Chart

Add a Percent done column in F, next to Duration. For the stacked bar chart, split Duration into Done in G, =E2*F2, and Remaining in H, =E2-G2, then chart Task, Start day, Done and Remaining, with the start still hidden. In the grid, add a rule with a darker fill for the cells before the done point. Timeline view can color cards from a column, so a Status column with a color per status does the same job there.

Share It

Everyone with access sees the same live chart, which is the main reason to keep a plan in Sheets rather than in a picture. If people outside the team need it, download a picture of the chart for a slide or an email, and put the date in the chart title so nobody mistakes an old copy for the current plan.

Which Method Should You Use?

Make the Chart Without Sheets

If you only need the picture, copy the Task, Start date and End date columns from Sheets and paste them into our Gantt chart maker. It reads the dates as dates, takes a number of days in place of an end date, and has optional columns for the task each one waits for and the percent done. Nothing is uploaded: it runs in your browser.

Open this plan in the Gantt chart maker

The charts on this page were drawn by our bar graph maker, with method 2 exactly: horizontal, stacked, Start day colored transparent, legend hidden. Open one to see the settings.

For the Excel version of method 2, with the date axis fix and Microsoft’s templates, read how to make a Gantt chart in Excel. For what the chart is and where it came from, see what is a Gantt chart. And if you only need key dates in order, not durations, a timeline is the lighter choice: our timeline templates are ready to edit.