How to create a Gantt chart in Excel to plan projects

Once a project has more than four or five tasks, a simple list with dates is no longer enough: you can’t see which tasks overlap, which depend on others, or whether you’re running late. A Gantt chart solves this with a very simple idea: each task is a horizontal bar placed on a timeline.

You don’t need project management software to make one. Here you’ll see two methods in Excel: a stacked bar chart, ideal for presentations, and a grid with conditional formatting, which is handier for day-to-day tracking. Both start from the same task table.

Set up the task table

Every Gantt chart starts with a table that includes, at a minimum, the task name, the start date and the duration. From there, Excel calculates the rest. Create these columns:

• A – Task: a short, clear name.
• B – Start: the date it begins.
• C – End: calculated with a formula.
• D – Duration: in days.
• E – Progress: percentage complete, from 0% to 100%.

Calculate the end date

If duration is measured in calendar days, enter this in C2 and copy it down:

=B2+D2-1

You subtract 1 because the start day already counts as a working day. If you only work Monday to Friday, use working days:

=WORKDAY(B2,D2-1)

And if you already have the start and end dates and want to know how many working days fall between them:

=NETWORKDAYS(B2,C2)

Link dependent tasks

When a task can’t start until the previous one ends, don’t type the date by hand. In B3, enter:

=WORKDAY(C2,1)

That way, the task starts on the next working day after the previous one ends. If the first task slips, the whole plan shifts automatically. For calendar days, =C2+1 is enough.

Method 1: Gantt with a stacked bar chart

The trick is to stack two series: the start date, which you make invisible, and the duration, which is the bar you see.

1. Select A1:B6, hold down Ctrl and also select D1:D6.
2. Go to Insert › Bar Chart › Stacked Bar.
3. Click the blue series (Start), open Format Data Series › Fill and choose No fill. Remove the border too.
4. The tasks appear upside down. Click the vertical axis, open Format Axis and check Categories in reverse order.
5. The horizontal axis starts at a much earlier date. Change the format of B2 to General to see its serial number (dates in Excel are numbers) and enter it as the Minimum in the horizontal axis options.
6. Reduce the gap width between bars so they look thicker, and delete the legend.

The result is a clean Gantt chart that updates whenever you change any date or duration in the table. It’s the best option for a report or a presentation.

Method 2: Gantt with conditional formatting

This method turns cells into a timeline. It’s easier to maintain, handles many tasks and lets you show progress.

Create the timeline

1. In F1, enter =$B$2 (the project start date).
2. In G1, enter =F1+1 and copy it to the right until it covers the whole project, for example up to AM1.
3. Apply the custom format d to row 1 to show only the day, and narrow the columns to a width of 3.

Draw the bars

1. Select F2:AM6.
2. Go to Home › Conditional Formatting › New Rule and choose the option to use a formula to determine which cells to format.
3. Enter the following formula and pick a light fill color:

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

Mixed references are the key: F$1 locks the row of dates, and $B2 and $C2 lock the start and end columns.

Show progress and today’s date

Add another rule, with a darker color, for the completed part of each task:

=AND(F$1>=$B2,F$1<$B2+$D2*$E2)

In the Rules Manager, move it above the previous rule so it takes priority. Finally, a third rule with a border or eye-catching color marks today’s column:

=F$1=TODAY()

If the today line has already passed a bar that isn’t fully dark, that task is behind schedule.

Which method to choose

Many users combine both: the grid for daily tracking and the chart for reports.

Share it and keep the file light

A Gantt chart gets updated for weeks and often ends up with lots of formatted columns, conditional formatting rules duplicated by copy and paste, and added images or screenshots. The file grows and becomes hard to email. Before sharing it:

• Open Home › Conditional Formatting › Manage Rules and delete duplicate rules.
• Delete any spare date columns at the end of the timeline.
• If it’s still too large, use our free tool to reduce the size of Excel files.

To send the plan to someone who only needs to view it, convert it to PDF: it keeps the colors and can’t be edited.

Common mistakes

• Dates stored as text: if the chart shows no bars or the formulas return #VALUE!, check that the dates are real dates and not left-aligned text.
• Forgetting the -1 in the end date: a 5-day task that starts on Monday ends on Friday, not Saturday.
• Typing dates by hand for dependent tasks: any delay forces you to review the whole plan. Link them with formulas.
• Badly set chart axis: if the bars are squeezed to the right, the horizontal axis minimum doesn’t match the project start.

Conclusion

A Gantt chart in Excel is built on a well-made task table: start, duration and a formula for the end. With that, you can choose the stacked bar chart for presentations or conditional formatting for daily tracking, with progress and a today line included. Link tasks with formulas and the plan will readjust itself every time something changes.

Frequently asked questions