Build a Gantt Chart with Conditional Formatting

Excel Formulas › Conditional Formatting

All versionsAND

No add-ins, no chart objects — just shade the cells where each task is active. An AND formula rule across a date grid draws a clean, live Gantt chart that updates as your dates change.

Quick formula: with task start in $B2, end in $C2 and date headers across row 1 (D1, E1…), select the grid and add:

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

Each grid cell turns on when its column’s date falls within that row’s start–end span. Mixed references do the magic.

Functions used (tap for the full reference guide):

AND

The example

Three tasks across a week; shaded cells mark each task’s span.

	A	B	C	D
1	Task	Mon	Tue	Wed
2	Design
3	Build
4	Test

The formula

The single rule that builds the whole chart:

=AND(D$1 >= $B2, D$1 <= $C2) // shade where the column date is in the task span

How it works

One formula, applied across a grid, draws every bar:

Lay out date headers along row 1 (one column per day/week) and task start/end dates in columns B and C.
Select the whole grid (the cells under the date headers), then add the rule =AND(D$1 >= $B2, D$1 <= $C2).
The references are mixed: D$1 locks the header row (column floats), $B2/$C2 lock the date columns (row floats). So each cell compares its own column-date to its own row-span.
Choose a fill color — that’s the bar. Drag a date and the bar moves; the chart is fully live.

Add a “today” line with a second rule: =D$1=TODAY() in a contrasting color marks the current date down the whole grid. Use TEXT in the headers to label weeks instead of days for longer projects.

Try it: interactive demo

Live demo

Move a task’s start/end; the bar redraws.

Build start (day)

Build end (day)

Variations

Add a today marker

Second rule, contrasting color:

=D$1 = TODAY()

Percent-complete shade

Lighter bar past the % done:

=AND(D$1>=$B2, D$1<=$B2+($C2-$B2)*$E2)

Weekend columns

Dim non-working days:

=WEEKDAY(D$1,2)>5

Pitfalls & errors

Mixed references are critical. D$1 (row locked) and $B2/$C2 (column locked). Getting the $ placement wrong shades the whole grid or nothing.

Header dates must be real dates. Format them as dates (you can display just the day), or the >=/<= comparisons fail.

Active-cell alignment. Write the formula relative to the top-left cell of your selection, or the bars shift by a row/column.

Practice workbook

📊

Download the free Build a Gantt Chart with Conditional Formatting practice workbook

A working CF Gantt grid with the today-line and weekend rules applied, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I make a Gantt chart with conditional formatting in Excel?

Put date headers across a row and task start/end in two columns, then add a formula rule =AND(D$1>=$B2, D$1<=$C2) across the grid. Cells inside each task span get shaded as the bar.

Why are the wrong cells shaded?

Check the mixed references: the header must be D$1 (row locked) and the spans $B2/$C2 (columns locked). Also align the formula to the active cell of your selection.

How do I add a line for today?

Add a second rule =D$1=TODAY() in a contrasting color to mark the current date down the grid.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Function references: AND