Google Sheets Gantt Chart for Project Timelines

To make a project timeline Gantt chart in Google Sheets, put tasks and start dates in columns, calculate each task’s end date, add calendar dates across the top, then use Format > Conditional formatting with a custom formula to color the cells that fall within each task’s date range. Sheets does not have a dedicated Gantt chart type, but this date-grid approach creates a flexible, shareable schedule directly in a spreadsheet.

What a Gantt chart shows

A Gantt chart places project activities on a calendar. Each task appears on its own row, and a horizontal bar shows when the task starts and ends. This makes it easier to see overlaps, gaps, milestones, and the overall project span than a plain task list does. For example, a website project might show research during the first week, design overlapping with content planning, and development beginning after design approval.

In Google Sheets, the colored bars are usually formatted cells rather than chart objects. That distinction matters: you can edit task dates and see the bars move, but the schedule is still a spreadsheet grid. The approach works well for small and medium plans where people need to inspect dates, owners, and status alongside the timeline.

Choose columns for tasks and dates

Start with a simple table. Use one row per task and keep the schedule fields consistent. One practical layout is:

Column A: Task name
Column B: Start date
Column C: Duration in calendar days
Column D: End date
Column E: Owner
Column F: Status
Column G onward: One date per timeline column

Enter real date values in the start-date cells, not text that merely looks like a date. Select the date cells and use Format > Number > Date if Sheets does not display them as dates. Set the duration column to a positive number. Decide at the beginning whether duration means calendar days or working days; the two conventions produce different end dates when a task spans a weekend.

For a calendar-day schedule, with a task’s start date in B2 and its inclusive duration in C2, enter this in D2: =B2+C2-1. If B2 is May 4, 2026 and C2 is 5, the result is May 8, 2026: May 4 counts as day one. Fill the formula down the end-date column. The minus one prevents a five-day task from appearing to last six dates.

Build the timeline date row

Choose the first date that should appear in the schedule and enter it in G1. This may be the earliest task start date or a project-wide start date chosen for readability. With May 4, 2026 in G1, enter this formula in H1 to create the next day: =G1+1. It returns May 5, 2026. Drag the fill handle to the right to create as many date columns as the plan needs. Format the row as dates, and consider a narrow display such as day numbers if the full date labels make the sheet too wide.

Keep the timeline header on the same row for every task. The conditional-formatting rule will use that row to decide which dates belong inside each task’s span. Freeze the task-name columns or the header row when useful: in the browser, use View > Freeze and choose the rows or columns to keep visible while scrolling. A frozen label column helps readers identify a bar when the schedule extends far to the right.

Color task bars with conditional formatting

Select the timeline area where bars should appear, such as G2:AK20, adjusting the range to match the number of tasks and date columns. Open Format > Conditional formatting. Under Apply to range, confirm the selected cells; under Format cells if, choose Custom formula is. With start dates in column B, end dates in column D, timeline dates in row 1, and the first task on row 2, use: =AND(G$1>=$B2,G$1<=$D2). Pick a fill color and select Done.

The dollar signs are important. G$1 keeps the header row fixed as the rule moves down the task rows, while $B2 and $D2 keep the start and end columns fixed as the rule moves across the timeline. The row number remains relative so each task uses its own dates. The rule colors a cell only when its date is on or after the task start and on or before its end. If the selected range starts somewhere other than G2, adjust the references to match its first timeline cell and task row.

Test the setup with a short task whose dates you can verify. Change its start date or duration and check that the bar shifts or changes length. If the whole row colors, verify that the rule’s applied range and references are correct, and check that the start and end values are genuine dates. If nothing colors, inspect the conditional-formatting panel for a mistyped formula or an end date earlier than the start date.

Use working days, milestones, and dependencies

Calendar-day duration is simple, but many plans count weekdays only. For a weekday schedule, with the task start date in B2 and the number of working days in C2, enter this in D2: =WORKDAY(B2,C2-1). With a Monday, May 4, 2026 start and a five-working-day duration, it returns Friday, May 8, 2026. WORKDAY skips Saturday and Sunday. If your organization observes holidays, the function can also take a holiday-date range as an optional third argument; make sure those cells contain dates.

For working-day schedules, consider leaving weekend columns visible but uncolored, or visually marking them with a separate format. Be clear that the task bar spans dates while its duration counts workdays. A one-day milestone can be represented as a one-day task, but that still looks like a bar. A separate milestone symbol or a distinct color can make approval dates and launches easier to spot.

Dependencies require extra care. Conditional formatting shows dates, but it does not automatically enforce project logic. If development must wait until design is complete, changing the design end date will not necessarily move the development start date unless you add a dependency formula or update it manually. For a simple sheet, document the predecessor task and review dependent dates whenever the schedule changes. More complex dependencies, resource leveling, and critical-path calculations may call for dedicated project-management software.

Make the schedule easier to read

A useful Gantt chart is not just colored cells. Add an owner and a concise status field, and use consistent task names that describe deliverables or actions. Group related tasks with section labels or blank separator rows. Apply a distinct color to completed tasks or milestones only if readers can understand the meaning without guessing; a small legend near the table can explain the colors.

Keep detail at the right level. A project with hundreds of tiny tasks can become difficult to scan, while a chart with only broad phases may hide work that is already late. One option is to keep a summary timeline on the main tab and put detailed activities on another tab. Avoid merged cells inside the date grid because they interfere with individual date cells and make updates harder.

For a longer project, use weekly rather than daily columns. A weekly view reduces horizontal scrolling, but define what each column means: for example, the week beginning on Monday. If you use a single date per week as the header, the conditional-formatting rule must account for the whole week rather than treating that date as every day in the column. A daily grid is easier to interpret for short schedules; a weekly grid is usually more compact for longer horizons.

Limits and collaboration in Google Sheets

Sheets is useful when a team wants a familiar, accessible schedule that can be edited collaboratively in a browser. People with the appropriate sharing permission can work in the same file, and version history can help review earlier edits. The owner should still set sharing access deliberately, especially if the sheet contains internal dates, customer information, or other sensitive project details.

A spreadsheet Gantt chart does not automatically provide every project-management feature. Conditional formatting will draw bars, but it will not notify an owner when a task slips, resolve competing resource assignments, or guarantee that dependencies remain valid. It is also possible for a user to overwrite a formula or change a date format. Protecting formula ranges, adding data validation for status choices, and explaining how the sheet is maintained can reduce accidental changes, but they do not replace schedule ownership.

Google Sheets in a web browser is the focus here. Excel can also create a timeline grid with conditional formatting, but its menus and some collaboration behavior differ; instructions for one application should not be assumed to match the other exactly. If the file will move between Sheets and Excel, test the formulas and formatting after conversion rather than assuming every feature will transfer identically.

When to choose a different approach

A formula-driven grid is a good choice when you need a low-cost, editable overview and the project has a manageable number of tasks. It is especially convenient if the team already keeps project information in Sheets. A stacked bar chart is another possible visualization: it typically uses a hidden start-offset series and a duration series. However, setting up dates and labels can be less direct than coloring a date grid, and the result may be less convenient for editing individual tasks.

Consider a dedicated project-management tool when the plan relies on many linked dependencies, workload calculations, automated reminders, multiple baselines, or detailed reporting. The right choice depends on how the team works, not simply on the size of the chart. A modest project with clear ownership may be easier to maintain in Sheets than in a more elaborate system; a changing, interdependent program may outgrow a manually maintained spreadsheet.

Frequently Asked Questions

Does Google Sheets have a built-in Gantt chart?

Sheets does not offer a dedicated Gantt chart type. You can create a Gantt-style schedule using date columns and conditional formatting, or make a stacked bar chart with helper data. The date-grid method is usually the more straightforward option for an editable task-by-task calendar.

How do I make a Gantt chart in Google Sheets automatically update?

Use date values, formulas for end dates, and a conditional-formatting rule tied to each row’s start and end dates. When you edit a task date or duration, the end-date formula and colored bar update. Dependencies do not automatically move other tasks unless you build formulas for them or revise the dates yourself.

Can I exclude weekends from a Google Sheets Gantt chart?

Yes. Calculate end dates with WORKDAY when duration means working days, and optionally provide a holiday range. You can also leave weekend date columns visible but style them differently. Be explicit about whether the displayed bar represents elapsed dates or only working time.

Why is my Gantt chart conditional formatting not working?

Check that the start, end, and header cells contain date values; confirm that the applied range begins where the formula expects; and verify the fixed and relative references. Also check that each end date is not earlier than its start date and that the cells are not covered by a conflicting formatting rule.

Can multiple people edit a Google Sheets Gantt chart?

Yes, collaborators can edit a shared sheet according to the access they have been granted. Decide who may edit task dates and formulas, protect sensitive formula ranges if appropriate, and use clear ownership conventions. Collaborative editing makes updates easier, but the team still needs a process for approving schedule changes.

Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.

More in Charts & pivot tables

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.