Building a Gantt Chart in a Spreadsheet

A Gantt chart is just horizontal bars on a date axis: each bar starts at a task's start date and

runs for its duration. Spreadsheets can't draw a "Gantt" natively — but there are two reliable

ways to fake one perfectly, and this guide walks through both using the Gantt Chart tab

(sheets/02-gantt-chart.csv). The worked numbers match the sample data.

The data you start with

The Gantt tab gives every task four numbers the chart needs:

Offset_Days = Start_Date − ProjStart and Span_Days = End_Date − Start_Date + 1. For the

sample, ProjStart is 2025-01-06, so T11 starts 66 days in and runs 16 calendar days. Those

two numbers — offset and span — are all a bar chart needs to float each bar in the right

place.

Method 1 — The stacked-bar trick (a real chart)

This is the professional approach: a stacked horizontal bar chart where the first series is made

invisible so the second series appears to float.

Step 1 — Lay out the chart source

You need three columns next to each other: the task name, the offset, and the span.

Task_Name            Offset_Days   Span_Days
Stakeholder interviews        0          5
Requirements gathering        7          5
...
Page implementation          66         16

(The Gantt tab already has these — columns B, E, F.)

Step 2 — Insert a stacked bar chart

• Google Sheets: select the three columns → Insert → Chart → Chart type **Stacked bar

chart**.

• Excel: select the three columns → Insert → Bar Chart → Stacked Bar.

You'll see two colored segments per row: the offset, then the span.

Step 3 — Make the offset invisible

Click the Offset_Days series (the first segment of every bar) and set its fill to **None /

No fill**. The offset becomes transparent, and each remaining Span bar now floats at its start

date. That's your Gantt.

Step 4 — Reverse the axis order

Charts plot the first row at the *bottom* by default, which puts your project upside-down. Turn

on "Reverse order" (Excel: Format Axis → Categories in reverse order; Sheets: chart editor →

Vertical axis → check the reverse option) so T01 sits at the top and time reads top-to-bottom.

Step 5 — Fix the date axis

The horizontal axis is currently "days since start" (0, 7, 14…). To show real dates, set the

axis minimum to your project start's serial number and format the axis as a date. Now the

bars line up under real calendar dates.

Step 6 (optional) — Show progress inside each bar

Split each bar's span into "done" and "remaining" so the chart shows completion, not just

schedule. Add two helper columns:

Done_Span    = Span_Days * Pct_Complete/100
Remain_Span  = Span_Days - Done_Span

Now the chart has three series: invisible Offset, solid Done_Span (dark), and lighter

Remain_Span. For T11 (16-day span, 20% done): Done = 3.2, Remain = 12.8 — a bar that's

one-fifth filled. Do this and a glance at the chart tells you both *when* and *how far along*.

Method 2 — The conditional-formatting grid (no chart)

If you'd rather keep everything in cells, paint the bars onto a calendar grid with conditional

formatting. This is lighter, prints cleanly, and never detaches from the data.

Step 1 — Build a date header

In a block to the right of your task rows, put a row of dates — one per column. Weekly columns

keep it compact: 2025-01-06, 2025-01-13, 2025-01-20, … Put the first date in, say, K1 and

fill across.

Step 2 — One rule paints every bar

Select the whole grid (e.g. K2:AZ19) and add a custom-formula conditional-format rule:

=AND(K$1>=$C2, K$1<=$D2)

It fills a cell when that column's date (K$1, row locked) falls between the task's start

($C2, column locked) and end ($D2). The mixed $ anchors are the whole trick — they let one

rule cover the entire grid. Choose a blue fill.

Step 3 — A second rule for progress

Add a higher-priority rule that paints the completed portion a darker shade. The done-bar

ends this many days into the span:

=AND(K$1>=$C2, K$1 <= WORKDAY($C2, ROUND($F2*0 + (G_pct)/100*Duration,0)))

Simpler in practice: add a Done_Until helper column = WORKDAY(Start, Duration*Pct/100 - 1)

and make the dark rule =AND(K$1>=$C2, K$1<=$Done_Until). The dark band shows how much of each

bar is finished; the light band shows what's left.

Step 4 — Mark "today"

Add one more rule to draw a vertical "today" line — highlight the column whose date matches your

status date:

=K$1=$AsOf

→ a red left border or a yellow fill on that column. Now you can see, at a glance, which bars the

status line has already passed (those should be complete) and which it cuts through mid-bar.

Which method should you use?

Most teams keep the grid for day-to-day work (it updates the instant a date changes) and

generate the chart when they need something to paste into a slide.

Reading the sample Gantt

With the bars drawn and the AsOf line at 2025-03-17, the sample tells its story visually:

• T01–T09 sit entirely left of the today line — all complete (Green).
• T10's bar ends *before* the line but isn't full — it's the overdue Red task.
• The line cuts through T11 and T12 mid-bar — work in progress (T11 ahead, T12 behind/Amber).
• Everything from T13 rightward is still in the future — Not Started.

A good Gantt makes "are we on track?" answerable in one glance — which is exactly what you want

on the wall during a status meeting.

In-cell bars when you don't even want a grid

For a quick text Gantt with zero setup, the Progress_Bar column (##########) already

ships on the Gantt tab via =REPT("#",done)&REPT("-",remain). Pair it with the Status column

and you have a readable mini-dashboard in two columns — no chart, no grid, works everywhere

including plain-text exports.

Questions about charting your own data? Email [email protected].