To make a detailed Gantt chart in Excel, list each task with a start date and duration, create a row of calendar dates, then use conditional formatting to shade the cells that fall within each task’s dates. This flexible worksheet-based method shows timing and overlap clearly; a stacked bar chart is another option when you need a chart object for a presentation.
What a Gantt chart shows
A Gantt chart places tasks down the left side and time across the top. A horizontal bar marks when each task starts and ends. Its main value is that it turns a task list into a schedule you can scan: overlapping work, gaps, long tasks, and milestones become easier to spot.
Excel does not have a dedicated Gantt chart type in its standard chart list. People commonly build one either by formatting a worksheet grid or by using a stacked bar chart. The grid method is easy to customize and update, while the chart method behaves more like a conventional chart that can be copied into a report or slide.
A Gantt chart is a schedule, not a guarantee that work will finish on time. It displays the dates and durations entered; it does not automatically know whether a person is available, whether a task is blocked, or how much work a team can do at once. Those details must be represented in the data or managed separately.
Set up the task table
Start with a simple table in a blank worksheet. Use these columns, beginning in row 5:
- A: Task name
- B: Start date
- C: Duration in days
- D: Finish date
- E: Percent complete, if you want to track progress
Enter the first task in row 6 and continue down the sheet. For example, a small website project might include requirements, page design, content writing, development, testing, and launch. Give each task its own row, even when two tasks have the same dates. Use real Excel dates in column B rather than text that only looks like a date, and use whole numbers for durations.
Decide what “day” means before entering durations. For a calendar-day schedule, a five-day task starting April 6 includes April 6 through April 10. With task names in A6:A20, start dates in B6:B20, and calendar-day durations in C6:C20, enter this in D6 and fill it down:
=B6+C6-1
For a task starting April 6, 2026, with a duration of 5, the result is April 10, 2026. The minus one makes the start date count as the first day. Format column D as a date using Home > Number Format. If duration means working days rather than calendar days, use the alternative described below instead of this calculation.
Create the date timeline
Reserve row 5 for the timeline, beginning in F5 so it does not overlap the task table. With start dates in B6:B20, enter this in F5:
=MIN($B$6:$B$20)
If the earliest start date in that range is April 6, 2026, F5 returns April 6, 2026. In G5, enter the following formula and fill it right across the dates you need:
=F5+1
If F5 is April 6, G5 returns April 7. Continue until the timeline covers the latest finish date. Format the timeline cells as dates, for example with a day-and-month display such as 6-Apr. The underlying values remain dates, so comparisons and conditional formatting work correctly.
For a long project, a daily column for every day can make the sheet unwieldy. You can instead show weeks or months, but the bar logic must match the time scale. A simple daily grid is best for short schedules. For a broader view, a weekly timeline often offers a more readable balance; label each column with the week’s starting date and decide consistently how to represent tasks that begin or end midweek.
Shade task bars with conditional formatting
Select the schedule area, such as F6:AZ20, adjusting the last column and row to fit your plan. On the Windows desktop version of Excel for Microsoft 365, choose Home > Conditional Formatting > New Rule, select the option to use a formula to determine which cells to format, and enter:
=AND(F$5>=$B6,F$5<=$D6)
This formula assumes that dates are in row 5, task start dates are in column B, finish dates in column D, and the first schedule cell is F6. For F6, it returns TRUE when the date in F5 falls on or between that task’s start and finish dates. Choose a fill color, confirm the rule, and Excel shades the cells for each task’s scheduled days. The dollar signs keep the date row and start/finish columns fixed as the rule adjusts across the range.
Use a distinct fill for a milestone if useful. A milestone is usually a zero-duration event, so it may not appear as a bar with the inclusive finish-date formula above. One practical approach is to give it a one-day duration for display and label it clearly, or mark its date with a separate conditional-formatting rule or symbol. Keep the convention consistent so readers do not mistake a milestone for a day of work.
To add the rule in Excel for Mac, select the schedule cells and use Home > Conditional Formatting > New Rule; the dialog wording may vary slightly by release. Excel for the web also supports conditional formatting, though some rule-creation options and dialogs can differ from the Windows desktop app. The formula itself uses ordinary Excel functions and cell references.
Represent working days, dependencies, and progress
Calendar-day durations include weekends and holidays. If the schedule should count weekdays only, use Excel’s WORKDAY function to calculate the finish date. With the start date in B6, the number of working days in C6, and holiday dates listed in H2:H10, enter this in D6:
=WORKDAY(B6,C6-1,$H$2:$H$10)
For a five-working-day task starting Monday, April 6, 2026, with no holidays in that week, the result is Friday, April 10, 2026. The holiday range is optional; omit the third argument if you have no holiday list. WORKDAY excludes Saturdays and Sundays by default. If your workweek differs, Excel also has WORKDAY.INTL, which allows weekend days to be specified.
Dependencies describe task order: for example, testing may need to start after development finishes. Excel will not automatically reschedule every dependent task just because an earlier date changes, unless you build formulas for that logic. For a small plan, record a predecessor task ID in another column and update dates deliberately. For a larger project with many linked tasks, resource assignments, or frequent changes, dedicated project-planning software may be more suitable than a manually maintained grid.
Put percent complete in column E and format it as a percentage. You can apply Home > Conditional Formatting > Data Bars to E6:E20 to show progress in the task table. Treat this as a separate indicator from the schedule bar: a task’s planned date range and its reported completion are different facts. Agree on what counts as complete, and update the percentage from actual status rather than letting it imply that elapsed time equals work completed.
Build a stacked bar chart instead
A chart-based Gantt uses the start date as an invisible offset and the duration as the visible bar. Arrange task names, start dates, and durations in adjacent columns, then select that data and choose Insert > Insert Bar Chart > 2-D Stacked Bar. In the chart, format the start-date series with no fill so only the duration series remains visible. Reverse the task category order if the first task appears at the bottom, and format the horizontal date axis to show readable dates.
The exact chart formatting takes more setup than the conditional-formatting grid. Excel may initially display dates as large serial numbers or include the offset series as a colored segment; that is why formatting the date axis and hiding the start series matter. Keep the original start-date values in the data table, since the hidden series still positions the visible duration bars.
Choose the grid when the schedule needs to be edited frequently, filtered, or viewed alongside notes and owners. Choose a stacked bar chart when the visual needs to be resized or placed in a report. Either way, verify that the task order, date scale, and duration convention are obvious to anyone reading it.
Make the schedule readable and maintainable
Detailed does not have to mean crowded. Freeze the task labels while scrolling across a long timeline by selecting the cell just below and to the right of the area to keep visible, then choosing View > Freeze Panes > Freeze Panes. In this layout, selecting F6 before using the command keeps the upper rows and columns A:E visible. On Windows, the shortcut for Freeze Panes is not a single universal key combination; use the ribbon path. Mac has the same command under View, though the menu presentation can vary.
- Use concise task names and add separate columns for owner, status, or predecessor when those details are useful.
- Use consistent colors and include a small legend if color has a specific meaning.
- Apply borders sparingly; heavy gridlines across a long calendar can obscure the bars.
- Check that dates are actual date values and that the timeline reaches every task’s finish date.
- Keep a saved copy before making substantial layout or formula changes, especially if the workbook is shared.
For accessibility and printing, do not rely on color alone to communicate status. Include text such as a status column, use sufficient contrast, and set the print area to include the task table and relevant timeline. A multi-month daily grid may print across many pages; change the time scale or print orientation rather than shrinking the text until it is hard to read.
Common problems and how to fix them
If bars do not appear, check that the conditional-formatting formula points to the correct start and finish columns and that the selected range begins at the same cell used in the formula. Also check whether dates are stored as text. Re-entering a date or changing the cell format alone may not convert text into a usable date value.
If the bars are one day too long or short, review the duration convention. The formula B6+C6-1 treats both the start and finish date as scheduled days. A duration of one therefore ends on the start date. Do not mix that inclusive convention with a formula that treats duration as the number of days after the start.
If the timeline begins too early or stops too soon, confirm that the MIN formula and the visible timeline cover all task rows and the latest finish. If you add tasks below row 20, expand the ranges in formulas and conditional formatting. Excel tables can help keep task data organized as rows are added, but the timeline formatting range may still need to be extended.
Frequently Asked Questions
Does Excel have a built-in Gantt chart template?
Excel may offer project-planning templates through its template gallery, but the standard chart types do not include a dedicated Gantt chart type. A conditional-formatting grid or a stacked bar chart is a common way to make one from task data.
How do I show weekends in an Excel Gantt chart?
A daily timeline created by adding one to each date includes weekends. To calculate task finishes using weekdays only, use WORKDAY or WORKDAY.INTL; the display scale and the duration calculation are separate choices.
Can a Gantt chart in Excel update automatically?
The shaded bars update when the start dates, finish dates, or timeline dates change, provided the formulas and conditional-formatting range include the relevant cells. Excel does not automatically infer dependencies or revise a complete project plan unless you create formulas or use another planning tool.
How can I show task completion?
Add a percent-complete column and format it as a percentage. Conditional Formatting > Data Bars can provide a quick visual indicator. Keep it distinct from the planned date bar so readers can tell schedule from actual progress.
Is the worksheet method available in Excel for Mac or the web?
Conditional formatting and formulas are available in Excel for Mac and Excel for the web, though some dialog details and chart-formatting controls differ from Microsoft 365 for Windows. The steps above use the Windows ribbon paths as the main reference.
Related
- Copy and Paste X Times in Excel: Repeat Cells or Rows
- Convert H:M:S to a Number in Excel
- Contact Number List Template for Excel: Setup Guide
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Unique Excel Chart Designs: Ideas and How to Build ThemUnique Excel chart designs combine a chart type suited to the data with thoughtful formatting, labels, and context.…
- Excel Box Plot X-Axis Labels: Set and Fix ThemIn Excel for Microsoft 365 on Windows, a box plot’s x-axis labels normally come from the column headers…
- Excel Pivot Table IFERROR: Calculated Field GuideYes. In a regular Excel PivotTable, you can use IFERROR in a calculated field formula to replace an…
- Excel Chart Title From Cell: Link, Format, and FixesTo link an Excel chart title to a cell, select the chart title, click the formula bar, type…
- How to Insert an Excel Bar Chart Into PowerPoint SlidesTo insert an Excel bar chart into PowerPoint, copy the chart in Excel, switch to your slide, and…
- Excel Pie Chart: Exclude Zero Values and Empty SlicesExcel pie charts automatically omit zero values as slices, so you usually do not need to change a…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.