How to Build a Gantt Chart in Excel That Updates Automatically

The build, stage by stage

This monthly Gantt chart isn’t a chart object at all. It’s a grid coloured by conditional formatting, so it redraws itself when dates change. It shows years, quarters and months, fills in progress, flags tasks on hold, marks finished tasks and shows where today is.

1. Header and project details

Add a light grey divider bar across row 4 (out to column AZ). In C2 and C3 add the labels Project start date and Project name, right-aligned. Enter the start date in D2 (the example uses 1 March) with a custom format such as d-mmm-yy, and the project name in D3. Make both bold, white on dark green.

2. The task table

From B4, create eight columns: #, Activity, Assigned to, Start date, End date, Days, Status and % Done. Number the tasks, add some sample activities, format the dates like the project start date, and use size 14 text throughout for a consistent look.

• Days (dark green, white text) counts working days only, and stays blank until there’s an end date: =IF(F5="","",NETWORKDAYS(E5,F5)).
• Status gets a drop-down (Data Validation > List: To Do,In Progress,On Hold,Done) and a conditional formatting rule that fills “On Hold” light yellow.
• % Done is formatted as a percentage. It drives the progress fill later.

Turn off gridlines and add thin grey inside and outside borders to the table.

3. The calendar header

Across the top of the chart area, build three rows: years, then quarters (each merged across three months), then months. Copy the structure across for several quarters, give the month row a light grey fill, and add light grey borders to the whole grid. Use View > Freeze Panes so the task details and timeline headers stay visible as you scroll.

4. Make the timeline start at the quarter

The first month (K3) should be the first day of the quarter that contains your start date, so a June start begins the timeline in April. One way to write it:

=DATE(YEAR($D$2),FLOOR(MONTH($D$2)-1,3)+1,1)

Format it with a custom mmm format (and optionally rotate the text 90°), then fill the next months with =EDATE(K3,1), which moves forward one month at a time. Above it, the year row references the month cell with a yyyy format, and the quarter row converts the month number to Q1–Q4. A conditional formatting rule shades alternate years light grey.

5. Draw the bars with conditional formatting

Select the whole grid and add a formula rule that colours a cell when its month falls between the task’s start and end month. Because the timeline uses the first day of each month, compare against first-of-month versions of the dates, for example:

=AND(K$3>=DATE(YEAR($E5),MONTH($E5),1),K$3<=DATE(YEAR($F5),MONTH($F5),1))

Use a light pink fill, and add light borders so the bars read as blocks.

6. Layer on progress, on-hold and done

• Progress: another rule on top that fills a darker pink from the task’s start month up to the point that matches % Done, but only when % Done is above zero.
• On hold: a copy of the progress rule with one extra condition (status is “On Hold”), filled light yellow, so blocked tasks stand out.
• Done: a rule that fills the end month bright green when the status is “Done”.

7. Add milestone diamonds

In the grid, a formula returns the letter u only in the month and year that match the task’s end date:

=IF(AND(MONTH(K$3)=MONTH($F5),YEAR(K$3)=YEAR($F5)),"u","")

Paste it across the grid as formulas only, centre and bold it, and change the font to Wingdings, which turns the u into a diamond. Colour it dark pink.

8. Mark today

A final rule draws a grey dashed left border on the current month’s column, so you always see where you are: =K$3=DATE(YEAR(TODAY()),MONTH(TODAY()),1).