How to Make a Gantt Chart in Excel Without Microsoft Project

Most Gantt tutorials teach the stacked bar chart trick: plot the start date as an invisible series, plot duration as a visible one, hide the first series, reverse the axis. It works, and then you add a task and it doesn’t, because the chart’s data range didn’t grow and the axis reversed itself back.

There is a better method that takes about ten minutes and never breaks: the bars are conditional formatting on ordinary cells. No chart object, no add-in, no Microsoft Project. Change a date and the bar moves in the same instant, because the bar was never a drawing — it was a rule.

Here is the whole build.

Step 1: Lay Out the Task Table

Left-hand side, one row per task. Six columns you type into:

Add a duration column if you want it — =IF(OR(D3="",E3=""),"",E3-D3+1) for calendar days, or =NETWORKDAYS(D3,E3) for working days. The +1 is not optional. A task that starts and finishes on Mar 14 lasted one day, not zero.

Format D and E as real dates, not text. If they are left-aligned in the cell by default, Excel thinks they are text and every formula below will silently fail.

Step 2: Build the Date Header

Put the timeline start date in a single cell well clear of the grid — B1, with a label in A1. Everything downstream reads it, so changing that one cell scrolls the whole chart. Keeping it out of the grid’s own columns matters, because row 1 above the grid is about to be used for month labels.

Then in the header row:

• H2: =$B$1
• I2: =H2+1

Drag I2 right as far as you want the timeline to run. Sixty columns covers two months.

Two formatting jobs make it readable:

1. Select H2 across to the end and set a custom number format of d — the cells still hold full dates, they just display the day number.
2. Set those column widths to about 2.5 characters. Select all of them, right-click a column header, Column Width, type 2.5.

For month labels, use row 1 above the grid. Put this in H1 and drag right:

=IF(OR(H2=$B$1,DAY(H2)=1),TEXT(H2,"mmm"),"")

Only the first column and the first of each month get a label, which is exactly as much month information as anyone needs.

One practical catch: every other cell in row 1 holds a formula returning "", and neither Excel nor Sheets lets text overflow into a neighbour that contains a formula result. At 2.5 characters wide, “Mar” will be clipped. Two fixes — merge the cells of each month across row 1 once the labels are in, or set row 1’s alignment to 90° rotated text so the month reads vertically down a narrow column. Rotation survives adding columns; merging does not.

Want the timeline to begin at the earliest task automatically? Make B1 a formula instead of a typed date: =MIN(D3:D200).

Step 3: The Four Rules That Draw the Chart

Select the whole grid — H3 to the last date column, down to your last task row — and open Conditional Formatting → New Rule → Use a formula to determine which cells to format. Add these in this order, because order is what makes the two-tone bar work.

Rule 1 — completed portion (dark fill)

=AND($D3<>"", H$2>=$D3, H$2<=$D3+ROUND(($E3-$D3+1)*$F3,0)-1)

Rule 2 — remaining portion (light fill)

=AND($D3<>"", H$2>=$D3, H$2<=$E3)

Rule 3 — today marker (a red left border, not a fill)

=H$2=TODAY()

Rule 4 — weekends (pale grey fill)

=WEEKDAY(H$2,2)>5

Drag them into that order in the Conditional Formatting Rules Manager. Excel applies rules top-down and a higher rule’s fill already beats a lower rule’s fill, so leave Stop If True unticked on all four. Ticking it on Rule 2 is the mistake worth avoiding: Rule 2 is true for every cell inside a bar, so stopping there means Rules 3 and 4 never evaluate on a bar — and the today marker vanishes exactly where you need it, crossing the work.

That is also why Rule 3 sets a border rather than a fill. Border and fill are separate format properties, so the red line draws on top of a bar instead of competing with it. Rule 4’s weekend fill loses to the bars above it and only shows in the empty stretches, which is what you want.

Google Sheets has no Stop If True checkbox at all — the first rule in the list that matches wins, so the same order gives the same result with nothing to tick.

Why the Anchoring Is the Whole Trick

$D3 and H$2 look like a typo. They are the reason it works.

• $D3 — the $ locks the column. Every date cell across a row reads the start date from column D of its own row.
• H$2 — the $ locks the row. Every task down a column reads the date from row 2 of its own column.

Between them, a single rule written once for the top-left cell is correct for every cell in the grid — 3,000 of them on a 60-column, 50-row timeline. Anchor both parts, and every row draws the same bar. Anchor neither, and the bars step diagonally down the page. If your chart comes out looking like a staircase, this is why.

One more requirement: the formula must be written as if you were sitting in the top-left cell of the applies-to range. If your grid starts at H3, the formula references row 3 and column H. If you select the range from the bottom up, Excel treats the active cell as the anchor and the whole thing shifts.

How the Percent-Complete Rule Behaves

Rule 1 is worth reading closely, because it degrades correctly at both ends.

Visual design runs Mar 14 to Mar 25 — twelve days — at 60%:

ROUND(12 × 0.6, 0) = 7
Mar 14 + 7 − 1 = Mar 20

So Mar 14 through Mar 20 takes the dark fill, and Mar 21 through Mar 25 keeps the light one. Seven of twelve days is 58%, which is as close as whole days get to 60%.

At 0%, the range becomes Mar 14 to Mar 13 — an empty range, so nothing shades. At 100%, it lands exactly on the due date and the whole bar goes dark. No special cases, no IFERROR.

Fitting a Long Project on One Screen

Daily columns run out of screen at about two months. For a six-month project, switch to weekly buckets — two changes:

Header: I2 becomes =H2+7 instead of =H2+1.

Bar rule (Rule 2): swap containment for overlap.

=AND($D3<>"", $D3<=H$2+6, $E3>=H$2)

Containment asks “is this day inside the task?” Overlap asks “does the task touch any day in this week?” — which is the right question once a column is worth seven days. Twenty-six columns then cover half a year on one screen.

Rule 1 has to change too, and this is the step most people miss. Left as a day-containment test it will only shade the weeks whose bucket date happens to land inside the completed window, so the two-tone bar quietly stops working. Give it the same overlap treatment, against the completed-through date rather than the due date:

=AND($D3<>"", $D3<=H$2+6, $D3+ROUND(($E3-$D3+1)*$F3,0)-1>=H$2)

Monthly buckets work the same way, with one trap: use =EOMONTH(H2,0)+1 for the header, not =EDATE(H2,1). If your timeline does not start on the 1st, EDATE chains Mar 14 → Apr 14 → May 14, which are not months, and every overlap test after that compares against the wrong window. With EOMONTH the overlap test becomes $D3<=EOMONTH(H$2,0) and $E3>=H$2.

What This Gantt Will Not Do

It draws honestly and it recalculates instantly, but it is a picture of the dates you typed. It does not know that Front-end build cannot begin until Visual design is signed off, so when Visual design slips four days, nothing downstream moves until you move it. A Depends On column documents the relationship; it does not enforce it.

That is the trade you are making, and for most projects it is a good one — the enforcement is the expensive part, and the picture is the part that changes decisions. The seven things worth checking on that picture every week, including the one a Gantt shows better than any dashboard tile: a task whose window has opened and whose bar is still entirely light.

For the full build — the task list this sits on, the dashboard formulas and the multi-project roll-up — see the complete project management spreadsheet guide. And if you would rather have the board view than the timeline, here is when each one is the right tool.

Featured on ReadySheetGo

Project Management & Gantt Chart Tracker — $14.99

Eight tabs with the Gantt already built. The timeline reads a single start-date cell, so one edit scrolls the whole chart. Bars draw themselves from each task’s start and due dates and shade to separate completed work from remaining. The same Task List also feeds a Kanban Board that re-sorts by status, a Milestones tab with a days-left countdown and at-risk highlighting, a Projects roll-up across every project, and a Dashboard returning total tasks, completed, in progress, overdue, overall % complete, budget versus actual and workload by owner. Overdue tasks turn red automatically. Settings holds editable dropdowns for statuses, priorities, owners and project names.

Sample data pre-filled across four example projects. Works in Excel and Google Sheets, no macros and no add-ons.

Get the Project Management & Gantt Chart Tracker →