Making a Gantt chart in Excel

The stacked bar chart most tutorials propose looks pretty in a screenshot and is painful to live with: it has to be reconfigured every time a task is added. Conditional formatting gives a better result, and it fits in two formulas.

Updated on 17 September 2026 · 14 min read · Excel on Windows

The starting table

A readable schedule fits in four columns. The rest — percentages complete, owners, budgets — is added later, on the right, without breaking anything.

The formula for column D, in D2 and then copied down:

=WORKDAY(B2,C2-1,$Z$1:$Z$20)

The -1 is not an affectation: without it, a one-day task would finish the day after it started. The duration counts the start day. The range $Z$1:$Z$20 will hold the public holidays — we come to that.

The date scale

To the right of the table, one column per day. In F1, the first date; then each column adds a day:

F1 :  =B2
G1 :  =F1+1      then copy to the right over 60 to 120 columns

Select the whole of row 1 from F onwards, set the custom number format to j so that only the day of the month shows, and give the columns a width of 2.5. You get a dense, readable grid.

A second header row makes the schedule far more comfortable — the month, shown once per month:

F2 :  =IF(MONTH(F1)<>MONTH(E1),UPPER(TEXT(F1,"mmm yyyy")),"")

The bars, through conditional formatting

This is where the method parts company with the stacked bar chart. Rather than drawing, we colour: each cell of the grid asks itself whether its date falls within its row's task.

1. Select the grid, from F3 to the last column and the last task.
2. Home → Conditional Formatting → New Rule → Use a formula.
3. Type the formula below, then choose a green fill.

=AND(F$1>=$B3,F$1<=$D3)

Where you put the $ is the whole secret. F$1 freezes the row: whatever the cell, we always look at the date on the scale, at the top. $B3 and $D3 freeze the column: we always look at the start and the end of the task, on the left. Without those dollars, the rule drifts and colours anything at all.

Working days and French public holidays

The schedule is right, but the bar runs through the weekends: the task looks as if it lasts longer than it does. Two corrections are needed.

Declare the public holidays

Put them in a column out of the way — Z1:Z20 in our formulas. Six of them have a fixed date (1^st January, 1^st May, 8 May, 14 July, 15 August, 1^st November, 11 November, 25 December). The other three depend on Easter, which is worked out by this formula — here it is for the year in A1:

' Easter Sunday
=ROUND(DATE(A1,4,1)/7+MOD(19*MOD(A1,19)-7,30)*14%,0)*7-6

' The three holidays that follow from it
Easter Monday      = Easter + 1
Ascension          = Easter + 39
Whit Monday        = Easter + 50

Checked in Excel: this formula gives Sunday 5 April 2026, Sunday 28 March 2027 and Sunday 16 April 2028. It is correct from 1900 to 2199.

Colour only the working days

Replace the conditional formatting rule with this one:

=AND(F$1>=$B3,F$1<=$D3,WEEKDAY(F$1,2)<6,COUNTIF($Z$1:$Z$20,F$1)=0)

Two conditions are added: WEEKDAY(F$1,2)<6 rules out Saturday and Sunday — the argument 2 makes the week start on Monday, which gives 6 and 7 for the weekend. And COUNTIF(...)=0 rules out the dates present in the holiday list.

Add a second rule, in pale grey this time, to tint the weekend columns down the whole height of the schedule. It becomes readable at a glance:

=WEEKDAY(F$1,2)>5

Today, overruns and milestones

The today line. A third rule, applied to the whole grid, with a thick red left border:

=F$1=TODAY()

Overruns in red. Add a Progress column in E (from 0 to 100%), then a rule with priority:

=AND(F$1>=$B3,F$1<=$D3,$D3<TODAY(),$E3<100%)

Any task whose end date has passed while it is not finished lights up. Place this rule above the green rule in the manager, and tick “Stop If True”: the order of the rules decides which colour shows.

Milestones. A task with zero duration produces no bar. Give it a duration of 1 and a rule of its own that shows a diamond, through a custom cell format "◆" on a transparent background.

Where this method stops

This schedule is honest and it is yours. Three things will be missing from it the day the project grows.

• Dependencies. Nothing links the end of “Design” to the start of “Order”. If the design slips by three days, you have to shift all the following tasks by hand. You can write =WORKDAY(D2,1) in B3, but the chain breaks as soon as you insert a row or reorder.
• Direct manipulation. Lengthening a task means going back to column C and typing a number. On a thirty-row schedule, the constant to-and-fro between the table and the grid wears you down.
• The critical path. Knowing which tasks have no float calls for a forward and backward pass that conditional formatting cannot carry.

As long as your schedule fits on one screen and moves once a week, the method above is amply enough — and that is why this guide exists. Beyond that, you need an engine that recalculates the dependencies at every gesture.

Frequently asked questions

Read next