Google Sheets Gantt Chart Formatting Guide

To format a Gantt chart in Google Sheets, arrange tasks in rows, put each task’s start and end dates in separate columns, and make a row of dates across the timeline. Then select the timeline cells and use Format > Conditional formatting with a custom formula to color cells whose dates fall between each task’s start and end dates.

What a Gantt chart in Sheets shows

A Gantt chart is a schedule displayed as horizontal bars against a calendar. Each row represents a task; the bar’s position shows when the task begins, and its length shows how long it lasts. Google Sheets does not require a special chart object for this common format: conditional formatting can turn an ordinary grid into a flexible Gantt chart.

This distinction matters. A bar created with conditional formatting is a set of colored spreadsheet cells, not a floating shape or a chart series. That makes it easy to revise dates, add rows, or apply status colors, but it also means the calendar needs enough columns to show the time span you want.

Set up the task table and timeline

Start with a simple structure. Put task names in column A, an optional owner or status in column B, start dates in column C, and finish dates in column D. Put the first date of the timeline in E1 and later dates in F1, G1, and the columns to the right. Begin task data in row 2. For example, a task starting May 7 and ending May 9 should have those dates in C2 and D2, while the corresponding dates appear in the header row.

Enter dates as actual spreadsheet dates, not text that merely looks like a date. Select the date cells and choose Format > Number > Date if they display as serial numbers or in an unwanted format. To show compact labels such as day and month, use Format > Number > Custom date and time. Keep the underlying values as full dates; changing the display does not remove the year or change the date used in calculations.

Choose a timeline scale that suits the project. A daily scale is useful for short work, while weekly or monthly columns make a long plan easier to scan. Avoid squeezing a year of daily columns onto one screen: the detail may be technically present but difficult to read. You can freeze the header row with View > Freeze > 1 row so dates remain visible while scrolling.

Color task bars with conditional formatting

To create the bars, select the timeline area, such as E2:Q20, and choose Format > Conditional formatting. Under Format rules, choose Custom formula is. With task names in A, start dates in C, end dates in D, timeline dates in row 1, and the selected range beginning at E2, enter this rule:

=AND($A2<>"",$C2<>"",$D2<>"",E$1>=$C2,E$1<=$D2)

Choose a fill color and select Done. The dollar signs are important: they keep the task name and date columns fixed as the rule evaluates each cell, while the row number and timeline column adjust across the selected range. The checks for blank task and date cells prevent empty rows from being colored. The comparison includes both the start and end dates, so a task scheduled from May 7 through May 9 colors three daily cells.

When you add more tasks or extend the calendar, update the rule’s Apply to range so it includes the new cells. You can also select a larger area at the start, such as E2:AZ100, if you expect the schedule to grow. That avoids having to edit the range repeatedly, though it can make a very large sheet less tidy.

Make the schedule easy to read

Formatting should make the dates and bars legible without competing for attention. A practical starting point is one main color for task bars, a contrasting header fill for the date row, and a restrained font size for the calendar. Adjust column widths to show short date labels and row heights to separate tasks. Use Format > Wrapping > Wrap for long task names, and consider widening column A rather than shrinking the text until it is hard to read.

Apply a consistent date format to the timeline headers so dates line up visually.
Use borders sparingly. A subtle boundary between weeks or months can help readers navigate a dense schedule.
Use a different fill for milestones only if they are clearly identified in the task table; a one-day task can otherwise look like a milestone by accident.
Keep status information in its own column, such as Not started, In progress, or Complete, rather than relying only on bar color.
Add a short key near the schedule if colors represent owners, phases, or status categories.

For a longer calendar, label the months in an extra header row and merge cells only when doing so will not interfere with sorting or editing. Another option is to use a border at the start of each month. A consistent visual grid helps people estimate timing; excessive borders and unrelated colors make it harder to identify the actual task bars.

Show progress, milestones, and nonworking days

A basic Gantt bar shows the planned date span, not how much work is complete. Add a progress column with a percentage, or add a separate completion bar if the team needs to compare planned time with progress. Keep planned dates and actual dates distinct: changing a planned finish date to reflect actual progress can erase the original commitment and make later review confusing.

Milestones are often represented by a single date rather than a duration. You can list them as separate rows and give them a distinct color or symbol in the task-name column. If you use the same bar rule, a milestone with identical start and end dates will color one day. Label it explicitly so readers do not mistake it for a one-day task.

Weekends can be visually muted, but do not assume that every project treats weekends as nonworking days. If you want weekend shading, add a separate conditional-formatting rule to the timeline range, using a custom formula based on the date in the header row. For example, when the range begins in E2 and its header date is in E1, this formula identifies Saturday and Sunday:

=WEEKDAY(E$1,2)>5

Choose a light fill for that rule. If task bars and weekend shading both apply to the same cell, check how the rules interact and arrange the rules so the task bar remains clear. A shaded weekend is only a visual cue; it does not automatically change task duration or move dates. For schedules that exclude holidays or weekends, define that working-day logic separately and explain it to the people using the sheet.

Common formatting problems and fixes

No bars appear: Check that the start and finish entries are real dates, that the header row contains dates, and that the conditional-formatting range includes the intended cells. Confirm that the rule references the first cell of the selected range; for a range starting at E2, the header reference should start with E$1.
Bars appear in the wrong columns: Verify that the dates in the header progress in the intended order and that the rule uses the correct start and end columns. A text date or a timeline using a different year can look plausible but fail the comparisons.
Every row has the same bar: Check the row references. The task dates should use row-relative references such as $C2 and $D2, not fixed references such as $C$2 and $D$2.
Blank rows are colored: Add checks for a nonblank task name and nonblank start and finish dates, as in the example rule. Also check whether the applied range extends into unused rows.
The final day is missing: The rule must use less than or equal to the finish date, not a strict less-than comparison. The inclusive rule colors the finish date as well as the start date.
Bars look too narrow: Use a shorter date scale, such as weeks instead of days, or widen the timeline columns. A daily grid cannot show a short task clearly if each date is compressed to a few pixels.

When troubleshooting, open Format > Conditional formatting and inspect both the rule and its Apply to range. Formatting can appear broken simply because a copied rule still points to an old row or because the selected cells begin in a different column than the formula expects.

When a spreadsheet Gantt chart is a good fit

Conditional formatting works well for a small or medium schedule that needs to be edited directly in a shared sheet. It is adaptable, familiar to many spreadsheet users, and can keep task details and the calendar together. It is especially useful when the schedule is part of a broader tracking sheet with owners, notes, and status fields.

It is less suitable when the plan depends on many linked tasks, automatic resource leveling, complex dependencies, or frequent baseline comparisons. A colored cell does not automatically shift when another task slips, and Sheets will not infer project logic from the bars. In those situations, a project-management application or a purpose-built planning tool may be more appropriate. You can still export or summarize dates in Sheets, but the chart itself does not replace dependency management.

Google Sheets and Excel both support conditional formatting, but their menus and some charting features differ. In Sheets, use Format > Conditional formatting and the Custom formula is rule. Excel has its own conditional-formatting dialog and formula-entry workflow, so instructions for one program should not be assumed to match the other. A Sheets Gantt chart built from cell fills is also different from inserting a conventional chart.

Frequently Asked Questions

Does Google Sheets have a built-in Gantt chart template?
Sheets has templates and third-party templates may include Gantt-style layouts, but a common approach is to build the schedule with a task table and conditional formatting. Check the template’s date logic and rules before relying on it; layouts and features can vary.

Can I make a Gantt chart without conditional formatting?
Yes. You can manually color cells, but those bars will not adjust automatically when dates change. Conditional formatting is usually the more maintainable option because the cell fills respond to the start and end dates.

Why does my Gantt chart show dates but no colored bars?
The most common causes are dates stored as text, a rule applied to the wrong range, or references that do not match the first cell in that range. Check the date values and the rule under Format > Conditional formatting.

Can one task have more than one color?
Yes. You can add additional conditional-formatting rules for categories such as phase, owner, or status. Make the conditions mutually clear where possible, and check overlapping rules so one fill does not obscure another.

How do I show a task that is still in progress?
Keep the planned start and finish dates in the schedule and add a separate status or progress field. If you need to show actual progress visually, create a separate progress treatment and label it; the ordinary date-span bar alone does not indicate completion.

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.