How to Make a Gantt Chart in Google Sheets (2 Methods)

Here is how to make a Gantt chart in Google Sheets: list each task with a start date and an end date, put one date per column across the top, and add a conditional formatting rule with the custom formula =AND(F$1>=$B2, F$1<=$C2). Every cell whose date falls inside a task’s dates fills with color, and those cells form the bars. If you would rather have a chart object you can move and paste elsewhere, a stacked bar chart with an invisible first series does the same job.

Below you will find both methods step by step on one example project, plus how to skip weekends and holidays, mark today and color bars by status.

Set Up the Task List First

Both methods read the same columns, so start there. Our example is a podcast launch that runs from Monday, May 17 to Friday, June 11, 2027, with Memorial Day (Monday, May 31) off. Name the sheet Plan, put headers in row 1 and tasks in rows 2 to 7:

Row	A: Task	B: Start	C: End	D: Working days	E: Status
2	Plan the format and first episodes	5/17/2027	5/19/2027	3	Done
3	Design cover art	5/20/2027	5/25/2027	4	Done
4	Record trailer and three episodes	5/20/2027	5/27/2027	6	In progress
5	Edit episodes	5/28/2027	6/4/2027	5	Not started
6	Set up hosting and submit to directories	6/7/2027	6/8/2027	2	Not started
7	Launch week promotion	6/9/2027	6/11/2027	3	Not started

Add a second sheet named Holidays and type your days off in A2:A12 (here, just 5/31/2027). Then column D counts working days:

=NETWORKDAYS(B2, C2, Holidays!A2:A12)

Editing spans 8 calendar days but only 5 working days, because it crosses a weekend and Memorial Day. Our NETWORKDAYS guide covers custom weekends and WORKDAY, which finds an end date from a duration. If your task list is still a vague to-do pile, build a work breakdown structure first.

One check saves a lot of confusion later: type =ISNUMBER(B2) in a spare cell. It should return TRUE. If it returns FALSE, the date is stored as text and the formulas below will not treat it as a date.

Method 1: Conditional Formatting Gantt Chart in Google Sheets

This method needs no chart at all, just a grid of dates and one rule, and it is the one we recommend for most projects.

1. Build the date row. In F1, enter =SEQUENCE(1, MAX(C2:C7)-MIN(B2:B7)+1, MIN(B2:B7)). It fills one date per column from the earliest start to the latest end: 26 columns, F1 to AE1, for the example. Keep the cells to its right empty, or SEQUENCE returns a #REF! error.

2. Format the dates. Select F1:AE1, choose Format > Number > Custom number format, type d and click Apply, so each header shows just the day of the month. Use m/d instead if the project crosses months and you want them visible, then turn the text with Format > Rotation > Rotate up.

3. Narrow the columns. Select columns F to AE, right-click a column letter, choose Resize columns and enter 24 pixels.

4. Add the bar rule. Select F2:AE7 and open Format > Conditional formatting. Under Format rules, choose “Custom formula is” and enter:

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

   Pick a fill color and click Done.

The formula is written for F2, the top-left cell of the range, and Sheets shifts it for every other cell. F$1 locks the row, so each cell reads the date at the top of its own column. $B2 and $C2 lock the columns, so each cell reads the start and end of its own row. In row 5, the cells from May 28 to June 4 fill: an 8-cell bar for Edit episodes.

Skip Weekends and Holidays

The bars above run straight through Saturdays, Sundays and Memorial Day. To show days off as gaps in the bars, add a second rule:

1. Select F1:AE7, so the header dates turn gray too.

2. Add another rule with “Custom formula is”:

   =OR(WEEKDAY(F$1,2)>5, COUNTIF(INDIRECT("Holidays!A2:A12"), F$1)>0)

3. Choose a light gray fill, click Done, then drag this rule above the bar rule in the list of rules.

WEEKDAY(F$1,2) numbers Monday as 1 through Sunday as 7, so anything above 5 is a weekend day. If your team works Saturdays, change >5 to =7. Conditional formatting in Google Sheets cannot point at another sheet directly, which is why the holiday range goes inside INDIRECT as text.

Order matters because Google Sheets applies the first rule in the list that is true for a cell. With the gray rule on top, the Edit episodes bar breaks at May 29 to 31 and shows 5 colored cells, which matches the 5 working days in column D.

Mark Today and Color Bars by Status

For a today marker, select F1:AE1 and add the rule =F$1=TODAY() with a bright fill. Today’s date lights up in the header row. Google Sheets conditional formatting can change text and fill but not borders, so the header is the cleanest place for this marker.

For status colors, add one rule per status and place them above the plain bar rule:

• Done, green: =AND(F$1>=$B2, F$1<=$C2, $E2="Done")
• In progress, amber: =AND(F$1>=$B2, F$1<=$C2, $E2="In progress")

Your final order, top to bottom: today, weekends and holidays, Done, In progress, then the plain bar rule for everything else. The today rule sits first so it still shows if today falls on a holiday. A dropdown in column E (Insert > Dropdown) keeps the spelling consistent, since "Done" will not match “Done “ with a stray trailing space.

Method 2: Stacked Bar Gantt Chart in Google Sheets

A chart object is easier to resize, paste into a slide deck or place on a dashboard. It uses a small helper table.

1. Add a sheet named Chart and type Task, Start day and Days in A1:C1.
2. In A2 enter =Plan!A2. In B2 enter =Plan!B2-MIN(Plan!$B$2:$B$7). In C2 enter =Plan!C2-Plan!B2+1. Fill all three down to row 7.
3. Select A1:C7 and choose Insert > Chart.
4. In the Chart editor, on the Setup tab, set Chart type to Stacked bar chart. Check that “Use row 1 as headers” and “Use column A as labels” are ticked.
5. On the Customize tab, open Series, pick Start day from the dropdown and set Fill opacity to 0%.
6. Under Legend, set Position to None. Under Chart & axis titles, give the horizontal axis a title such as “Days from May 17, 2027”.

Start day is how many days after the project start each task begins, and Days is its length in calendar days. Edit episodes gets a Start day of 11 and 8 Days, so once the first series is invisible, its visible bar floats from day 11 to day 19.

Two details make this simpler than in Excel. Google Sheets keeps your rows in order with the first task at the top, so there is nothing to reverse, which is a step you do need in our Excel Gantt chart guide. And because the helper uses day offsets instead of raw dates, the axis starts at the project start. With raw dates the axis would begin at zero, and since Sheets counts dates from December 30, 1899, every bar would be squeezed against the right edge.

The trade-off: chart bars cover calendar days, so weekends and holidays sit inside them with no gaps. Keep the working-day count in column D of the Plan sheet so the real effort is still visible.

One-Cell Bars With SPARKLINE

Google Sheets has a third option that Excel lacks: the SPARKLINE function draws a small chart inside a cell. On a copy of the Plan sheet without the date grid, enter this in F2 and fill it down to F7:

=SPARKLINE({B2-MIN($B$2:$B$7), C2-B2+1}, {"charttype","bar"; "max",MAX($C$2:$C$7)-MIN($B$2:$B$7)+1; "color1","white"; "color2","#4a86e8"})

Widen column F to about 300 pixels. Each cell draws a two-part bar: a white offset, then the blue task. The max option puts every row on the same 26-day scale, so the bars line up down the column. It is a compact view for a task list, though it shows no dates or days off.

Google Sheets Timeline View

Some Google Workspace accounts have Insert > Timeline, which turns a table with start and end dates into a scrolling timeline of cards on its own sheet, grouped by a column such as owner. If your account shows it, it makes a quick visual. If Insert > Timeline is missing, your account does not include it, and the methods above work in every account.

Which Google Sheets Gantt Method to Use

Method	Best for	Shows weekends and holidays	Same steps in Excel
Conditional formatting	Daily planning, printing, tracking status	Yes, with the gray rule	Yes, and Excel reads other sheets without INDIRECT
Stacked bar chart	Slides, reports, dashboards	No	Mostly, plus reversing the task order
SPARKLINE	A compact bar column beside a task list	No	No

For most small projects, start with conditional formatting: it lives next to your data, handles days off and takes about ten minutes to set up. If you are still deciding the dates themselves, work through how to create a project timeline before you draw anything.

If you would rather skip the setup entirely, our Gantt chart template already has phases, status colors, holidays and a today marker built in. Whichever route you take, review the chart once a week and look left of today: any bar there that is not marked Done needs a new end date.