Making a Gantt Chart From Google Sheets: Where the Formulas Break

Building a Gantt chart in Google Sheets works. That is worth saying first, because most articles on the subject either oversell the result or dismiss it. A stacked bar chart driven by two columns of dates produces a readable schedule in about fifteen minutes, it is free, everybody already has access, and for a plan with a dozen tasks and one owner it is genuinely the right tool.

What is worth knowing before starting is where it stops working, because the failure is not gradual. The chart looks fine until a date moves, and then a specific set of things break in a specific order. Knowing that order in advance makes it possible to decide whether the sheet is the destination or a stepping stone.

The two routes, and which one you can use

Sheets offers two different things, and they are not alternatives so much as answers to different situations.

The stacked bar chart. Google's own list of chart types in Sheets covers line, combo, area, column, bar, pie, scatter, histogram, candlestick, organizational, tree map, geo, waterfall, radar and gauge charts. There is no Gantt chart type. The bar chart entry notes a related stacked bar variant, and that variant is the whole technique: the first series is made invisible so it acts as an offset from the project start, and the second series is the duration. Available to everyone, including free personal accounts.

Timeline view. Google describes this as an interactive visual layer in Sheets, inserted from Insert and then Timeline. It is not a chart. It is a separate tab that reads a range of rows and draws cards on a time axis. The catch is eligibility. The documented list of editions that can use timeline view is Essentials, Business Starter, Business Standard, Business Plus, Enterprise Essentials, Enterprise Starter, Enterprise Standard, Enterprise Plus, Education Fundamentals, Education Standard, Education Plus and Frontline. A free personal Google account is not on that list.

So the practical decision is made for you by the account. On a paid Workspace edition, timeline view is far less work and far more robust than a formula driven chart. On a personal account, the stacked bar is the only route.

Building the stacked bar version

The data layout that works has five columns, and the fifth is the one people leave out and then need.

Task name, start date, end date, duration, and a calculated offset. Duration comes from the two dates. The offset is the number of days between the project start and the task start, and it exists because the invisible first series of the stacked bar has to push each bar to the right by the correct amount.

Two functions do the real work. Subtracting dates gives calendar days, which counts weekends. NETWORKDAYS(start_date, end_date, [holidays]) returns net working days between two dates and accepts an optional range of holiday dates, which is what most plans actually want, since nobody works Saturday and a bar that implies they do is misleading.

Pick one and use it everywhere. Mixing calendar duration in one column and working duration in another is the most common cause of a chart that is wrong by a few days in a way nobody can find.

Then insert a stacked bar chart on the offset and duration columns, set the offset series to no fill so it disappears, and reverse the vertical axis order so the first task appears at the top rather than the bottom. Conditional formatting on the task rows can carry status color, which is useful for reasons that matter later.

Where it breaks, in the order it happens

Four failures arrive in a predictable sequence, and each one has a workaround that costs more than the last.

Dependencies do not exist. This is the first and largest. A Gantt chart's value is not the bars, it is that moving one bar moves the bars behind it. A sheet has no concept of a predecessor. It can be faked by making one task's start date a formula referring to the previous task's end date, and that works for a strictly linear plan. It stops working the moment two tasks depend on the same predecessor, or one task waits for two others, because the formula needs a MAX across an arbitrary set of rows and the references have to be maintained by hand as tasks are inserted. Inserting a row in the middle of a chain is where a carefully built sheet quietly becomes wrong.

Rows get inserted and ranges do not follow. The chart is bound to a range. Adding a task at the bottom of the list outside that range produces a chart missing a task, with no warning. Named ranges and whole column references help. They do not fully solve it, because a stacked bar chart with blank rows in its range draws blank bars.

Multiple editors overwrite the shape. Sheets handles concurrent editing of cell values well. It handles concurrent editing of structure badly, in the sense that one person sorting the task list while another is editing a formula that refers to row 14 produces a formula pointing at the wrong task. Nothing errors. The numbers are simply about a different task than they were.

Nobody outside the sheet can read it. A Gantt chart for a team of two is a shared document. A Gantt chart that a client or a manager has to read becomes a thing that gets exported to an image and pasted into an email every week, and that weekly export is a job. Sheets holds up to 20 million cells or 100 MB per spreadsheet, so the file size is never the constraint. The constraint is that a schedule is a live thing and a screenshot is not.

Timeline view, if the account allows it

For teams on an eligible Workspace edition, timeline view removes most of the above at the cost of some layout control.

The setup is short. At least one column has to be in a date format, and if formulas produce the dates, the output must be date values rather than text. Required settings are a card title, the data range, a start date and an end date. Optional settings are card color, card detail, card group and duration. Card group is the one worth knowing: it places multiple cards on the same timeline row based on a column, which is how one row per person or one row per workstream is produced.

Two documented behaviors save time later. A start date must be earlier than the end date, or the card draws with no length, and a card showing a single day range is caused either by dates in an unaccepted format or by identical start and end dates. Duration can be entered as a number of days or in hh:mm:ss, and when duration is used instead of an end date, an Include weekends option appears and can be unchecked.

One trap is worth flagging because it is counterintuitive. Google's documentation states that conditional formatting should not be applied to the source data if you want to change card color from within the timeline, while separately noting that card color can be inherited from a column that uses conditional formatting. In practice, pick one method, either manual cell background or conditional formatting, and do not mix them on the same column.

What timeline view still does not do is dependencies. The settings list has fields for title, dates, color, detail, group and duration. There is no predecessor field, and no cascade when a date changes. It is a better drawing of the plan, not a model of it.

The conditional formatting alternative

There is a third route that gets less attention than it deserves, and for some plans it beats both of the above.

Instead of a chart, the calendar becomes the sheet itself. Dates run across the top as column headers, tasks run down the left, and conditional formatting colors a cell when its column date falls between that row's start and end. The result is a grid of colored blocks that reads exactly like a Gantt chart, built entirely from cell formatting with no chart object at all.

What this buys is durability. There is no chart bound to a range, so inserting a task row does not break anything. Sorting the task list reorders the bars correctly, because the bars are the rows. Two people can edit it at once without the structural hazard described above, since nothing refers to a row number. And it prints and exports cleanly, because it is a table rather than an image.

What it costs is width. One column per day means about thirty columns for a month and roughly 260 for a working year, which is manageable but means horizontal scrolling. Weekly columns compress it at the cost of resolution, which is usually a fair trade for a plan measured in weeks. Freezing the task name column is not optional here; without it, scrolling right loses track of which row is which.

The formatting rule itself is a single condition per row range, comparing the column header date against the row's start and end dates. Adding a second rule for a different fill on completed tasks, and a third for the current date column, covers most of what people want from a schedule at a glance.

For plans under about three months with a handful of people, this version survives real use better than the stacked bar chart does, and it takes less time to build.

What the sheet is genuinely good at

Two things the spreadsheet does better than most project tools, and they are worth keeping in mind before migrating on principle.

Arbitrary calculation. A column that works out a cost from a duration and a rate, a row that sums effort by phase, a cell that flags any task longer than ten working days: all trivial in a sheet, and all either impossible or a paid tier feature elsewhere. Teams that need the schedule and the money in the same place often have a real reason to keep the sheet for the money even after the plan moves.

No adoption cost. Everybody can already open it, nobody needs an account created, and the client who refuses to log into anything will read it. That is not a small advantage, and it is the reason sheets outlive the point at which they should have been replaced.

The realistic end state for many small teams is not one or the other. It is the plan living where the work is tracked, and a sheet pulled from it when somebody needs arithmetic the tool does not do. That arrangement only works if the export is one step, which is worth testing before committing.

When to stop and move

The sheet is the right answer for longer than tool vendors admit, and there are three specific signals that it has stopped being one.

More than one person edits the schedule. The moment two people maintain it, the structural editing problem above becomes a weekly event rather than a theoretical one.

A date moving means editing more than three cells. That is the point at which the plan has dependencies and the sheet does not model them. Every future correction is now a manual cascade, performed under time pressure, by somebody who will miss one.

Somebody outside the sheet asks for status weekly. The export becomes a recurring task, and recurring manual tasks are what tools are for.

Note that none of the three is about the number of tasks. A 200 row sheet maintained by one person is fine. A 15 row sheet maintained by four people is not. What breaks is shared structure, not volume.

If those signals are present, the thing to look for is not a bigger spreadsheet but a tool where the schedule and the task list are the same object, so that changing one changes the other with no export step. The features list of a candidate is where to check whether the Gantt view is included at the tier the team would actually be on, since timeline views are a paid tier in several products.

What to change first

If the account is on an eligible Workspace edition, use timeline view rather than building a stacked bar chart, and set card group to whatever the plan is organized by. If it is a personal account, build the stacked bar with a separate offset column and one consistent duration method. When a date moving means editing more than three cells, stop maintaining the sheet and move the plan somewhere the dependencies hold, which for a team of five or fewer can be a free tier such as Pinateca.

Q1. Does Google Sheets have a built-in Gantt chart?

Not as a chart type. Google's list of chart types in Sheets includes bar, column, line, area, pie, scatter, histogram, candlestick, organizational, tree map, geo, waterfall, radar and gauge charts, with no Gantt entry. The standard technique is a stacked bar chart with an invisible first series acting as an offset. Separately, timeline view is available on eligible Google Workspace editions and is closer to a Gantt view than any chart type is.

Q2. Why is timeline view missing from my Insert menu?

Almost certainly the account edition. Google documents timeline view as available on Essentials, Business Starter, Business Standard, Business Plus, Enterprise Essentials, Enterprise Starter, Enterprise Standard, Enterprise Plus, Education Fundamentals, Education Standard, Education Plus and Frontline. A free personal Google account is not on that list, so the stacked bar chart route is the available one.

Q3. Can a Google Sheets Gantt chart handle task dependencies?

Not natively. A strictly linear chain can be faked by setting each task's start date to a formula referring to the previous task's end date. That breaks as soon as two tasks share a predecessor or one task waits for several, because the references have to be maintained by hand and inserting a row in the middle silently points a formula at the wrong task. Timeline view has no predecessor field either.

Q4. Should the duration column count weekends?

Decide once and apply it to every row. Subtracting one date from another gives calendar days including weekends. NETWORKDAYS returns net working days between two dates and takes an optional range of holidays, which usually matches how a plan is actually meant to be read. Mixing the two methods across different rows is a common source of a chart that is wrong by a few days with no visible cause.

Q5. Why does my timeline card show only a single day?

Google's troubleshooting guidance names two causes: the data is not in an accepted date format, or the start and end dates are the same. A related rule is that the start date has to be earlier than the end date, or the card draws with no length at all. If formulas generate the dates, confirm the output is a date value rather than text that looks like a date.