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 support@datanest.dev.

Resource Leveling: Reading and Rebalancing the Allocation Tab

"Resource leveling" is the unglamorous core of project management: making sure no one is asked to

do more than they can, and that the people on your critical path aren't accidentally idle or

accidentally drowning. This guide explains how to read the Resource Allocation tab

(sheets/03-resource-allocation.csv), how to spot trouble, and how to fix it — using the sample

Customer Portal Redesign data, where every number reconciles with the Task List.

The one-sentence version

Find the person at 100%, because they — not the calendar — decide your finish date. Everyone

else has slack you can move work into; the fully-loaded person is the constraint, and the whole

schedule bends around them.

What the tab shows

Each row rolls one person's tasks up from the Task List:

The Allocated_Days column sums to 110 — exactly the project's total duration — because

every task has one owner. If your version doesn't total your project duration, a task is

unowned or double-owned.

Reading utilization correctly

Utilization here is allocated working days ÷ working days in that person's active window

(first start → last end). It is *not* "% of the whole project" — it's "how packed is the stretch

of calendar where this person is actually engaged."

That distinction matters:

• Elena at 100% means her three tasks (T08 → T10 → T11) are back-to-back with zero gaps

across 02-24 → 03-28. She has no buffer. The moment T10 ran late, T11 had nowhere to absorb it

— which is exactly why milestone M3 slipped.

• Priya at 22% is *not* underworked — a project lead engages in short bursts (kickoff

interviews, a design sign-off, final UAT) spread across the whole project. Low utilization

over a wide window is normal and healthy for oversight roles.

• David at 43% has real, usable slack between his tasks (T09 ends 03-07; T12 doesn't start

until 03-13; T18 is a month later). That slack is where you can move work *to*.

The rule of thumb: sustained utilization above ~85% is a flashing yellow light — that person

has no room to absorb a slip. Below ~30% over a *narrow* window means genuine idle capacity;

below 30% over a *wide* window (like Priya) is just the shape of an oversight role.

Spotting overload (and true double-booking)

A person is overallocated when they're assigned more work than their window has days — i.e.

utilization would exceed 100%, or two of their tasks overlap in time.

The quick check: any Utilization_Pct > 100 is a guaranteed overlap — you've assigned more

working days than exist in the window. Conditional-format the column:

=$I2>100   → red fill
=$I2>=85   → amber fill

The precise check: two tasks owned by the same person overlap if each starts before the

other ends. To flag it on the Task List, add a helper column:

=SUMPRODUCT( ($D$2:$D$19=D2) * ($A$2:$A$19<>A2)
           * ($E$2:$E$19<=G2) * ($G$2:$G$19>=E2) ) > 0

For each task, this counts the *other* tasks with the same owner whose date range intersects this

one. TRUE = that person is double-booked across those dates. In the sample it returns FALSE

for every row — the plan is cleanly leveled — but the moment you reassign work it'll catch a

clash.

A leveling decision framework

When the check lights up red (or a slip needs absorbing), work through these in order — cheapest

first:

Step 1 — Resequence within the same person

Can the overlapping tasks simply run one after the other instead of at once? If there's room in

the window, stagger their start dates. This costs nothing and keeps the owner. (Elena's tasks are

already sequenced this way — that's why she's at exactly 100%, not over.)

Step 2 — Move work to someone with slack

Reassign a task to a teammate whose utilization is low *in the same window*. David (43%) and

Tomas (20%) have capacity in March; Priya has capacity for oversight-flavored work throughout.

Change the Owner cell — the budget reprices to the new owner's rate and the resource tab

rebalances instantly. Watch the cost: moving a task from a $500/day strategist to a $700/day lead

raises the budget.

Step 3 — Split the task

If one task is too big for one person's window, break it into two rows assigned to two people

(e.g. Page implementation — auth and Page implementation — dashboard). Two owners, two

half-duration rows, and the work parallelizes. This is the main lever for *pulling in* a date.

Step 4 — Extend the window

If no one has slack, the task's end date has to move — accept it on the schedule rather than

pretending. Push the start, let WORKDAY recompute the end, and check which milestones the new

end date threatens on the Milestone Log.

Step 5 — Add capacity

The last resort: add a person to the roster and reassign. It's the most expensive option and

rarely as fast as hoped (onboarding isn't free), so it sits at the bottom of the list.

Why the bottleneck is the only number that matters

In the sample, Elena is the critical resource. Her chain T08 → T10 → T11 has no internal

slack, and it feeds milestone M3 (Development complete). So:

• Asking Elena to take on *anything* else doesn't add work in parallel — it pushes her existing

chain later, and M3 with it.

• When T10 ran 90%-but-overdue, there was no buffer in Elena's lane to absorb it, so the slip

flowed straight through to M3 (+4 days, At Risk) and M4 (+2, Minor Slip).

• The team protected the M5 Go-live date not by pressuring Elena, but by compressing the

*downstream* QA phase (Sofia's lane) — which had been planned with a little breathing room.

That's resource leveling in one example: **you can only pull in a date by adding capacity to the

constraint, never by squeezing the people who already have slack.** Identifying who the

constraint *is* — the 100% row — is the whole game.

Smoothing vs leveling

Two related moves, worth distinguishing:

• Leveling changes *dates* to resolve overallocation — you accept a later finish to stop

anyone exceeding 100%. Use it when capacity is fixed (you can't add people).

• Smoothing keeps the finish date and shuffles work within existing slack so no one spikes —

you use float, not extra time. Use it when the deadline is fixed and you have slack to spend.

The allocation tab supports both: leveling shows up as changed Start_Date/End_Date; smoothing

shows up as reassigned Owners with the milestones unmoved.

How to use this with the workbook

1. After any reassignment, glance at the Utilization_Pct column — keep everyone ≤ 100%, and

treat anyone ≥ 85% as having no buffer.

2. Run the overlap helper (above) to catch true double-booking the utilization number alone can

hide on a wide window.

3. When you move an owner, check the Budget Tracker — the task reprices to the new owner's

day rate, so leveling can quietly change your cost.

4. After leveling that moves dates, check the Milestone Log — confirm which baselines the new

end dates put at risk, and decide what to compress downstream to protect go-live.

Anti-patterns to avoid

• Reading 100% as "good, fully utilized." 100% means zero buffer — the first slip has

nowhere to go. Plan your critical resource to ~85%, not 100%.

• Confusing low utilization with idleness. A lead at 20% across the whole project is doing

their job, not slacking. Judge utilization against the *role* and the *window*.

• Leveling by squeezing people who have slack. Slack on a non-constraint resource doesn't

shorten the project — only capacity on the constraint does.

• Reassigning without watching cost. Moving a task to a more expensive owner levels the

schedule and quietly inflates the budget. Check both tabs.

• Hidden double-booking. A person can read under 100% utilization over a wide window yet still

have two tasks colliding in one week. Use the overlap formula, not just the headline number.

Questions about applying this to your team? Email support@datanest.dev.