Build a Gantt Chart in Google Sheets: Template, Formulas and Formatting
A spreadsheet timeline works best when the underlying table is tidy and the chart is built from only the columns it needs. This guide explains the stacked-bar method, the two calculations that position each bar, and the formatting choices that make the result readable in a meeting or slide deck.
How the stacked-bar method works
A Gantt view shows each job as a horizontal bar against time, so overlaps and sequence are visible without reading a list of dates. Sheets has no dedicated Gantt builder, but a stacked bar chart can imitate one: the first segment acts as an invisible spacer, and the second segment is the visible task length. Some accounts also provide Timeline view, which creates an interactive timeline from sheet data with less setup. That option suits a quick, lightweight overview. The stacked-bar method is preferable when you need control over appearance, a reusable structure, or reliability where Timeline view is unavailable.
Prepare the data and helper columns
Start with three required columns: Task Name, Start Date and End Date, with dates stored as real dates. Then add two helper columns. Start on Day measures how many days after the project start a task begins; the first task returns 0 and a task one week later returns 7. Use =INT(B2)-INT($B$2) in the first helper cell, where B2 is the first start date and $B$2 locks the project start, then fill it down. INT strips hidden time so the offset stays in whole days. Duration sets bar length with =C2-B2+1, where C2 is the end date. The +1 counts both endpoints, so Monday to Friday is five days. Fill that formula down as well. Optional tracking columns can include Assigned to, Status, % Complete, Notes and a manual Predecessor note. For phases, insert a bold row before each group, name it in Task Name, and set its dates from the earliest start and latest finish among its tasks.
Build the chart and hide the offset
Select Task Name, Start on Day and Duration including headers, then choose Insert and Chart. In the Chart editor pick Stacked bar chart with Standard stacking. Next, click a Start on Day segment to select that series, open Customize and Series, and set Fill color to No fill or Fill opacity to 0%. That hides the spacer while keeping its positioning effect. For spacing, open Customize, Gridlines and ticks, choose Horizontal axis, and set Major step to 7 for weeks or 30 for an approximate month. A worked example is a Process Improvement Rollout running from April 1 to May 8, where the first task, Identify the process to improve, has an offset of 0 and a duration of 2.
Format for scanning, phases and milestones
Give the chart a project-specific title so it is identifiable when several timelines are in use. Remove the legend by setting its position to None, since the hidden offset series makes it uninformative. Clear the vertical axis title and label the horizontal axis Days from project start. Colour individual bars by clicking once for the series and again for a single bar, then changing Fill color; this can group work by phase or flag priority. Phase rows, if present, read better in grey as group headers rather than tasks. Milestones are one-day rows with identical start and end dates, coloured yellow or gold so key approvals, handoffs or launches stand out.
Share, present and keep one source of truth
Share one sheet rather than circulating copies. Use the Share button, add people, and set view, comment or edit access deliberately: stakeholders usually need view or comment, while only date owners need edit access. For presentations, insert the chart into Slides through Insert, Chart, From Sheets, select the spreadsheet and chart, and choose the linked option so later sheet changes can refresh the slide. Keep ownership visible with an Assigned to column, ask questions in comments instead of editing dates, and use Version history to review or roll back changes. Archive finished rows below the active range or on another tab so the plotted chart stays current and compact.
Fix common problems and choose the right tool
If two segments remain visible, the Start on Day series has not been hidden; set it to No fill or 0% opacity. If formulas do not extend, accept the autofill prompt or drag the fill handle down the column. If weekly spacing looks wrong, check that Major step is 7 on the Horizontal axis. If the axis shows unexpected calendar dates, it is formatting day offsets as dates; set the number format to a plain number and label the axis Days from project start. Sheets suits a straightforward, shareable plan with manual upkeep. Excel suits a spreadsheet-first planning or reporting workflow, often with limited collaboration. Dedicated Gantt software becomes more suitable when dates move frequently, dependencies must shift tasks automatically, several people edit the schedule, or workload forecasting is required.
Using a reusable template
A reusable starting point saves repeated setup: open the template, choose File and Make a copy, name it, and save it to Drive. Keep a pristine source copy in a shared Templates folder and have each project start from a fresh copy. The template offers three layouts on tabs at the bottom of the sheet: Gantt Chart with % Complete for built-in progress tracking, Basic Gantt Chart for a simpler version with fewer tracking fields, and Manual Chart for more control over the setup. In the % Complete layout, update the Percent Complete column from 0% to 100% and the progress bars fill visually. Progress colours can be changed through Format, Conditional formatting, Color scale, by adjusting the maximum-point colour for 100%.
| Column | Purpose | How it is calculated or entered |
|---|---|---|
| Task Name | Labels each bar | Type a clear task description |
| Start Date | Task start | Enter as a date |
| End Date | Task finish | Enter as a date |
| Start on Day | Invisible offset | =INT(B2)-INT($B$2) |
| Duration | Visible bar length | =C2-B2+1 |
Example layout assumes task rows begin in row 2, with Start Date in column B and End Date in column C.
| Symptom | Likely cause | Fix |
|---|---|---|
| Two visible segments per task | Start on Day series still visible | Set Fill color to No fill, or Fill opacity to 0% |
| Formulas stop after first row | Autofill not confirmed | Accept the prompt or drag the fill handle down |
| Timeline not weekly | Major step is not 7 | Set Major step to 7 for the Horizontal axis |
| Axis shows wrong dates | Day offsets formatted as dates | Set number format to plain number and label Days from project start |
All fixes are in Chart editor under Customize unless stated otherwise.
Frequently asked questions
Does Google Sheets have a built-in Gantt chart tool?
No dedicated Gantt feature is included. A timeline can be built from a stacked bar chart, and some accounts also offer Timeline view for a simpler visual.
Can you make a Gantt chart in Google Sheets?
Yes. The usual DIY approach combines a stacked bar chart with Start on Day and Duration helper columns so each bar lands in the right position.
How do I make a Gantt chart in Google Sheets?
Prepare task names with start and end dates, add the two helper calculations, insert a stacked bar chart, hide the offset series, then set weekly or monthly spacing and format titles, legend, axes and colours.
Is there a Gantt chart template in Google Sheets?
No true Gantt template is included by default. A prebuilt copyable sheet is the common workaround.
How do I make a Gantt chart template in Google Sheets?
There is no save-as-template workflow for this purpose. Keep a clean source copy and make a fresh copy for each project.
How do I format a weekly Google Sheets Gantt chart?
Open Gridlines and ticks, apply it to the Horizontal axis, and set Major step to 7.
How do I format a monthly Google Sheets Gantt chart?
Use the same Gridlines and ticks setting with Major step 30. That is approximate because month lengths differ.
How can I automate a Gantt chart in Google Sheets?
Formulas provide the automation: changing a date recalculates offset and duration, so the bars move. A template already has those formulas in place.
How do I track work progress in a Google Sheets Gantt chart?
Add a Percent Complete column from 0 to 100 and update it as work advances. Colour scales or data bars can make progress easier to scan.
How do I add milestones to a Google Sheets Gantt chart?
Use a normal task row with the same start and end date, giving a one-day bar, then colour that bar yellow or gold.
How do I share or present a Google Sheets Gantt chart?
Share the sheet with view, comment or edit permissions. For slides, use Insert, Chart, From Sheets, choose the spreadsheet and chart, and keep it linked if you want later updates to flow through.