Why conditional formatting instead of a bar chart
Excel has no built-in Gantt chart type. The usual workaround is a stacked bar chart with an invisible first series, which works but is fiddly to update: dates on the axis have to be fixed by hand and new tasks must be added to the chart's data range. The method below draws the bars in the cells themselves. A new task is just a new row, and the timeline is plain cells you can print, colour and read like a calendar.
Step 1. Set up the task list
On a new sheet, put these headers in row 4: Task (A), Start (B), Days (C), End (D), Done % (E), Status (F). Enter tasks from row 5, for example "Gather requirements, 2026-11-02, 5". Days is the length including the first day, so a 1-day task starts and ends on the same date.
Calculate the end date in D5 and fill down:
D5=B5+C5-1
If work only happens on weekdays, use this instead. WORKDAY skips Saturdays and Sundays (start the task on a weekday):
D5=WORKDAY(B5,C5-1)
Type progress in E as a percentage (0%, 60%, 100%).
Step 2. Status date and late tasks
In A1 type Status date and in B1 put =TODAY(). You can overwrite it with a fixed date when you want to report the plan as of a certain day. In F5, then fill down:
F5=IF(E5>=1,"Done",IF(D5<$B$1,"Late",IF(B5>$B$1,"Planned","In progress")))
A task is late when its end date has passed and it is not 100% done.
Step 3. Build the timeline header
In A2 type View start. In B2 use the earliest start date, or type any date to scroll the chart:
B2=MIN(B5:B20)
Now the day columns. In G4 and H4:
G4=$B$2
H4=G4+1
Copy H4 to the right for as many days as you need (60 columns is about two months). Give row 4 the custom number format d so each cell shows only the day number, and make columns G onward narrow (width about 3). For month names above the days, put this in G3 and copy it right:
G3=IF(OR(G4=$B$2,DAY(G4)=1),TEXT(G4,"mmm"),"")
It shows "Nov" in the first column and again wherever a new month starts, and stays blank elsewhere.
Step 4. Bars, a today column and late tasks with conditional formatting
Select the grid, for example G5:BN20 (the top-left cell must be G5, because the rules are written for it). Choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format and add these rules, each with its own fill colour. They are plain formulas, so the file needs no macros:
Done part (dark blue)
=AND(G$4>=$B5,G$4<$B5+ROUND($C5*$E5,0))
Whole bar (light blue)
=AND(G$4>=$B5,G$4<=$D5)
Today column (status date, light orange)
=G$4=$B$1
Weekends (light grey, optional)
=WEEKDAY(G$4,2)>5
The dollar signs are what make one rule work for the whole grid: G$4 always reads the date in row 4 of the current column, and $B5, $C5, $D5 always read the current row's task. Each rule asks "is this column's date inside this row's task?"
Order matters. Open Manage Rules and put the done rule first and the whole-bar rule second, so the dark part is drawn over the light one. The done part is counted in calendar days from the start (Days x Done %, rounded), so with the WORKDAY end date it is an approximation.
For late tasks, select A5:F20 and add one more rule with a red fill. With B1 set to =TODAY(), the today column moves and late rows turn red by themselves each day you open the file:
=$F5="Late"

Step 5. Keep it usable
- Freeze panes at G5 (View > Freeze Panes) so task names and dates stay visible when you scroll right.
- Long projects: showing a column per day gets wide after three months. Changing H4 to
=G4+7gives a week per column, but with the rule above a task shorter than a week can fall between two column dates and disappear. How to make a weekly Gantt chart in Excel uses an overlap rule that keeps every task. - Moving a task does not move the next one. If task B must start after task A ends, type
=D5+1as B's start date instead of a fixed date. - Printing: set the print area to the columns you need and choose Fit all columns on one page.
Common problems
- No bars at all: the rule was entered while a cell other than G5 was the active cell, so the references are shifted. Delete the rule, select the grid starting from G5 and enter it again.
- Bars in the wrong row: the
$is missing before B, C or D in the rule. - Row 4 shows numbers like 46328: those are dates without a date format; apply the
dformat. - A task shows Done and a full dark bar too early: Done % was typed as 60 instead of 60%. In a plain number cell 60 means 6000%; type 60% or 0.6.
- Tasks show Late that are not: check B1. It should be today's date or the date you are reporting on.
Common questions
How do I add N business days to a start date in Excel, skipping weekends and holidays?
In the Days column enter the length of the task, and in the End column use =WORKDAY(B5,C5-1) (Step 1). WORKDAY skips Saturdays and Sundays and counts the start date as day 1, so start the task on a weekday. The formula in this guide does not skip holidays. Excel's WORKDAY function accepts an optional third argument, a range of holiday dates, if you want that. For a quick check without building a sheet, the free Project Timeline & Working Days Calculator takes a list of your own holidays.
How do I make one task start the day after another one ends?
Moving a task does not move the next one. Type a formula instead of a fixed date in the next task's Start cell: if task A is in row 5, enter =D5+1 as the start of task B. When A's start or length changes, B's bar moves with it (Step 5).
Prefer a ready-made file?
This page gives you a working Gantt chart. If you would rather start from a finished file, there is a free lite edition (15 tasks, day view, no sign-up) and a paid version with 200 tasks, a week view and a skip-weekends option.
Gantt Chart Project Schedule on Gumroad · USD 7Need more than one template? This one is also in the Excel Templates Bundle (all 4) on Gumroad: $19 instead of $30 bought separately.
Free lite edition (xlsx), no sign-upScreenshots and details of the template
Free Project Timeline & Working Days Calculator (runs in your browser)