How to make a Gantt chart in Excel
A Gantt chart, sometimes called a Gantt diagram, lays out every task of a project as a bar on a timeline. In Excel you do not need a chart object for it: a few date formulas and one conditional formatting rule draw the bars for you.
In short: one row per task with start, duration and end. =WORKDAY(C6,D6-1) returns the end date in working days, a row of dates runs across the columns, and the rule =AND(G$5>=$C6,G$5<=$E6) colors each cell between start and end. The finished Gantt chart template is free to download further down.
Try it now
Start with the simplest calculation in any schedule: turn a start date and a duration into an end date. A task that lasts one day starts and finishes on the same day.
Task In D2, calculate the end date of the task from its start and its duration in calendar days. A task that takes one day ends on the same day. The formula is filled down to D7.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Task | Start | Duration (days) | End |
| 2 | Kickoff | 1/11/2027 | 1 | |
| 3 | Requirements | 1/12/2027 | 4 | |
| 4 | Concept | 1/16/2027 | 7 | |
| 5 | Build | 1/25/2027 | 21 | |
| 6 | Test | 2/15/2027 | 5 | |
| 7 | Go-live | 2/22/2027 | 1 |
What a Gantt chart shows
Tasks run down the left side and time runs from left to right. Each task gets a bar from its start to its finish, so you can see at a glance what is in progress, what overlaps and when the project will be done. For small and medium projects with a few dozen tasks, Excel handles this well, and everyone on the team can open the file without a new tool.
There are two common ways to build one. Most templates use a grid of date columns where conditional formatting fills in the bars. The other way is a stacked bar chart. The grid is easier to keep up to date, so this guide focuses on it.
Gantt chart example: the layout of the template
Headers sit in row 5 and tasks start in row 6. The project start date goes in B2.
| Column | Content | Entry or formula in row 6 |
|---|---|---|
| A | Task | Text, such as Draft concept |
| B | Owner | Name |
| C | Start | A date, or a formula for tasks that depend on another one |
| D | Work days | Duration as a number, such as 5 |
| E | End | =IF(OR(C6="",D6=""),"",WORKDAY(C6,D6-1,Holidays)) |
| F | Status | Drop-down list with Open, In progress, Done |
| G to BQ | Timeline | In G5 =$B$2-WEEKDAY($B$2,2)+1, in H5 =G5+1 and so on |
The formula in G5 moves back from the project start to the Monday of that week, so the timeline always opens with a full week. The date cells use the number format d, which shows only the day of the month and lets the columns stay narrow. Row 4 shows the Monday of each week above the days. Panes are frozen at G6, so task names and dates stay in view while you scroll.
Durations in work days: NETWORKDAYS and WORKDAY
Schedules run on work days, not calendar days. Excel has two functions that answer opposite questions. NETWORKDAYS tells you how many work days fall between a start and an end. WORKDAY tells you on which date a task ends if it needs a given number of work days. Both skip Saturday and Sunday and accept a list of holidays as the third argument.
Task In D2, calculate how many work days the task takes. Weekends and the holidays in G2:G3 do not count. The formula is filled down to D7.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Task | Start | End | Work days | Holidays | ||
| 2 | Analysis | 3/1/2027 | 3/5/2027 | 3/26/2027 | |||
| 3 | Design | 3/8/2027 | 3/19/2027 | 3/29/2027 | |||
| 4 | Build part 1 | 3/22/2027 | 4/2/2027 | ||||
| 5 | Build part 2 | 3/29/2027 | 4/6/2027 | ||||
| 6 | Test | 4/7/2027 | 4/16/2027 | ||||
| 7 | Rollout | 4/19/2027 | 4/30/2027 |
This exercise is part of Premium.
Premium unlocks every exercise in every lesson, unlimited practice with fresh tasks and the final test with certificate.
See Premium Already Premium? Log in
The first exercise in this lesson is free.
The template stores the duration and calculates the end: =WORKDAY(C6,D6-1,Holidays). The minus 1 is there because the start day is already the first work day, so a five-day task that starts on Monday ends on Friday, not the following Monday. Holidays is a named range on the Lists sheet, which keeps all days off in one place.
Drawing the bars with conditional formatting
A cell in the grid belongs to a bar when the date above it is on or after the task's start and on or before its end. A formula rule in conditional formatting checks exactly that.
Task Draw the Gantt bars: use conditional formatting on D2:M6 to highlight every cell whose date in row 1 falls between the start and end of the task (both included). Write the formula as it applies to D2.
| A | B | C | D | E | F | G | H | I | J | K | L | M | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Task | Start | End | 1/11/2027 | 1/12/2027 | 1/13/2027 | 1/14/2027 | 1/15/2027 | 1/16/2027 | 1/17/2027 | 1/18/2027 | 1/19/2027 | 1/20/2027 |
| 2 | Kickoff | 1/11/2027 | 1/11/2027 | ||||||||||
| 3 | Requirements | 1/12/2027 | 1/15/2027 | ||||||||||
| 4 | Concept | 1/14/2027 | 1/18/2027 | ||||||||||
| 5 | Review | 1/17/2027 | 1/20/2027 | ||||||||||
| 6 | Build | 1/19/2027 | 1/24/2027 |
This exercise is part of Premium.
Premium unlocks every exercise in every lesson, unlimited practice with fresh tasks and the final test with certificate.
See Premium Already Premium? Log in
The first exercise in this lesson is free.
To set up the rule in Excel:
- Select the whole grid, G6:BQ35 in the template, with G6 as the active cell.
- Go to Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format.
- Enter
=AND(ISNUMBER($E6),G$5>=$C6,G$5<=$E6), pick a fill color under Format and confirm.
The mixed references do all the work. G$5 locks the row, so every column reads its own date from the header. $C6 and $E6 lock the column, so every row compares against its own task. If that is new to you, the lesson on absolute references explains the dollar signs. ISNUMBER keeps empty rows clear: without an end date, the comparison with a blank cell would be true for every date.
A few more rules make the chart easier to read. The template uses four, in this order:
| Rule | Formula | Format |
|---|---|---|
| Finished task | =AND(ISNUMBER($E6),G$5>=$C6,G$5<=$E6,$F6="Done") | green |
| Bar | =AND(ISNUMBER($E6),G$5>=$C6,G$5<=$E6) | blue |
| Today | =G$5=TODAY() | orange |
| Weekend | =WEEKDAY(G$5,2)>5 | light grey |
The first three have Stop If True ticked. The first matching rule wins, so a finished bar stays green even where it crosses a weekend.
Gantt chart with dependencies
Often a task can only start once the previous one is finished. Instead of typing that start date, calculate it: =WORKDAY(E6,1,Holidays) is the next work day after the task in row 6 ends. When one task slips, every task that depends on it moves along, and so do the bars. The sample tasks in the template are chained this way. For a task with a fixed date, simply type the date over the formula.
With several predecessors, take the latest end: =WORKDAY(MAX(E7,E9),1,Holidays). Long chains with many links are where dedicated project software starts to pay off.
Weekly or monthly Gantt charts
The template shows one column per day for nine weeks, which suits projects of a few weeks to a couple of months. For a longer plan, make each column a week: =G5+7 across the header, and change the rule so a cell is filled when the week overlaps the task, =AND($C6<=G$5+6,$E6>=G$5). The same idea with EOMONTH gives you one column per month.
The stacked bar chart version
If you would rather use a real chart, insert a stacked bar chart with two series: start date and duration in calendar days. Set the first series to no fill so only the duration shows as a bar, and set the task axis to Categories in reverse order so the first task appears at the top. It looks polished, but every new task means extending the chart's data range by hand.
Free downloadable Gantt chart template for project management
The finished workbook has everything on this page: room for 30 tasks, end dates with WORKDAY, chained sample tasks, a nine-week timeline with the four rules, a holiday list, a status drop-down, the share of tasks done and an instructions sheet.
Download the finished template
The finished Excel file gantt-chart-template.xlsx, with sample rows and instructions, is free. All you need is a free account: no subscription, no payment details.
Create a free account and download the template Already have an account? Log in
More templates you can download or build yourself are on the Excel templates page.
Common mistakes
Every row is filled in
The rule compares dates with empty cells. In comparisons Excel ranks text above numbers, so an end cell holding "" counts as later than any date. ISNUMBER($E6) in the rule fixes it.
The bars are shifted by a column or a row
The formula does not match the active cell of the selection. Write it for the top-left cell of the range you selected.
Tasks end one day late
The minus 1 is missing. WORKDAY(start,5) returns the fifth work day after the start, not the fifth including it.
Gantt formulas in German Excel
| English | German |
|---|---|
| WORKDAY | ARBEITSTAG |
| NETWORKDAYS | NETTOARBEITSTAGE |
| WEEKDAY, TODAY, ISNUMBER | WOCHENTAG, HEUTE, ISTZAHL |
German Excel separates arguments with semicolons. The template works in either language, because Excel stores functions in a neutral form and shows them in your language.
Which Excel versions support this?
WORKDAY, NETWORKDAYS, WEEKDAY and formula-based conditional formatting work in all current versions, including Excel for Mac and Excel for the web. If your weekend is not Saturday and Sunday, WORKDAY.INTL and NETWORKDAYS.INTL, available since Excel 2010, let you choose the days off.
FAQ
Does Excel have a built-in Gantt chart?
No, there is no Gantt chart type. You build one with conditional formatting or a stacked bar chart, as shown on this page.
Is there an easy way to build a simple Gantt chart?
Put tasks, start and end dates in three columns, dates across a header row, and add the single rule =AND(D$1>=$B2,D$1<=$C2) to the grid. That is a working Gantt chart in a few minutes.
How do I add holidays?
List them as dates on the Lists sheet of the template. WORKDAY and NETWORKDAYS skip any date in that range.
Can I use the template in Google Sheets?
Google Sheets has WORKDAY, NETWORKDAYS and formula rules too, so the method carries over. The template and the exercises here are made for Excel, so check the formatting rules if you open the file in another program.
Is the Gantt chart template really free?
Yes. Create a free account and you can download the .xlsx file and use it for your own projects.