For task names in column A, start dates in B, finish dates in C, and timeline dates across row 1 starting in F1, apply this conditional-formatting formula to the task grid: =AND(F$1>=$B2,F$1<=$C2). It colors a cell when its column date falls between that row’s start and finish dates, creating a simple Gantt chart.
How the Excel Gantt chart formula works
A Gantt chart places tasks in rows and dates in columns. The colored cells show when each task is scheduled. Excel does not have a dedicated Gantt chart type in its standard chart menu, but a conditional-formatting rule can make a useful chart directly in a worksheet.
The rule tests two conditions at once: the date at the top of the current column must be on or after the task’s start date, and on or before its finish date. The dollar signs keep the date row fixed while the rule is applied across the timeline, and keep the start and finish columns fixed while it is applied down the task list. The row number remains relative so each task is checked against its own dates.
In this setup, finish dates are inclusive. A task that starts on Monday and finishes on Wednesday is shown across all three dates. The formula compares calendar dates, so weekends are included unless you deliberately make a workday-only timeline.
Set up the task table and timeline
Start with a small, consistent table. Use one row per task and store actual Excel dates—not date-like text—in the start and finish columns. A practical layout is:
- Column A: task name
- Column B: start date
- Column C: finish date
- Column D: duration, if you want to display it
- Row 1, beginning in column F: one date per timeline column
For example, a task called Draft outline might start on November 3, 2026, and finish on November 5, 2026. Enter those dates in B2 and C2. Use the same date format throughout the table. Excel may display a date as a serial number if the cell is formatted as General; apply a date format from Home > Number to make it readable.
You can type the first timeline date into F1 and fill consecutive dates to the right. If you want the first date to be determined automatically, with task start dates in B2:B6 and the first timeline cell at F1, enter =MIN($B$2:$B$6) in F1. This returns the earliest start date in the task list. With the example dates above and no earlier start date in that range, the result is November 3, 2026.
To generate the following dates, with F1 holding the first timeline date and G1 as the next cell, enter =F1+1 in G1 and fill right. Each cell advances by one calendar day. Format the header cells as dates, then extend the timeline far enough to include the latest finish date.
Apply conditional formatting to draw the bars
With task rows 2 through 6 and the timeline starting in F1, select the full grid that should display bars, such as F2:AJ6. Choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. For the stated layout, where task starts are in B, finishes are in C, and the selected grid begins at F2, enter =AND(F$1>=$B2,F$1<=$C2) in the formula box.
Select Format, choose a fill color, and confirm the rule. Cells that meet both date tests receive the selected fill. Keep the formula’s F aligned with the first column of the selected range: if your timeline begins in G instead, the formula must refer to G$1. Likewise, if your first task is on row 5, use row 5 in the task-date references. Excel adjusts the relative references across the selected cells.
For a clean presentation, format the timeline row with a short date style, widen the task-name column, and reduce the width of the date columns. You can center the date headings and use borders or a light background for weekends. Freeze the task names and date headings with View > Freeze Panes if the schedule is wide or long; select the cell below and to the right of the rows and columns you want to keep visible before using the command.
Add duration and check a worked example
Duration is useful for reviewing a plan, but it is separate from the conditional-formatting rule. With start dates in B2:B6 and finish dates in C2:C6, put this formula in D2 and fill it down: =C2-B2+1. The added 1 counts both the first and last dates. For a November 3 through November 5 task, the result is 3 calendar days.
Suppose the first two tasks are Draft outline, November 3–5, and Review outline, November 4–7. The first bar appears under November 3, 4, and 5; the second appears under November 4, 5, 6, and 7. The bars overlap on November 4 and 5, correctly showing that both tasks are scheduled during those dates. If a bar is missing, compare the header dates with the start and finish cells before changing the formula.
If your duration should count Monday through Friday rather than every calendar day, use a working-day count instead. With the same start and finish dates in B2 and C2, enter =NETWORKDAYS(B2,C2) in D2. For November 3–5, 2026, this returns 3 because all three dates are weekdays. This changes the displayed duration, not the bar rule: a timeline with every calendar date will still show weekend cells between the start and finish dates.
Choose calendar days or working days
A calendar-day timeline is the simplest option and is often suitable for events, delivery windows, or work that continues through weekends. For a weekday-only schedule, you can create headers that skip Saturday and Sunday. With F1 set to the first date and G1 as the next header, enter =WORKDAY(F1,1) in G1 and fill right. If F1 is Friday, the next header is Monday, assuming no holidays are specified.
The conditional-formatting rule still works because it compares actual date values, even when some dates are omitted from the header row. However, a weekday-only view does not display weekend spans. Consider whether that omission could be misleading for tasks that take calendar time or run continuously. For schedules with official holidays, WORKDAY can accept a holiday range as a third argument; keep that list of holiday dates in the workbook and use an absolute reference to it when filling the formula right.
Make the schedule easier to read and maintain
A basic Gantt chart communicates timing, but a few design choices make it more useful. Keep task names concise, use one consistent bar color for ordinary work, and reserve a different color for a meaningful category such as a critical milestone or delayed task. If you add extra conditional-formatting rules, inspect their order in Home > Conditional Formatting > Manage Rules so the rules do not produce unexpected fills.
- Sort task rows in a logical sequence, such as project phase or planned start date.
- Use a separate column for owners or status instead of adding those details to the task name.
- Mark milestones with a clear label and a one-day start and finish date, or use a separate symbol or formatting rule.
- Check that every finish date is on or after its start date and that the timeline extends through the latest finish.
- Use an Excel Table for the task list if it will grow; tables can make the task data easier to filter and maintain, though the timeline formatting range may still need to be extended.
Conditional formatting changes the appearance of cells, not their values. The colored blocks are not separate objects that can be dragged to reschedule a task. To move a task, edit its start or finish date in the table; Excel recalculates the rule and redraws the bar.
Common problems and fixes
If nothing colors, check that the conditional-formatting rule was applied to the correct grid and that the formula uses the first cell of that grid. A rule for F2:AJ6 should begin with F$1 and row 2. Also confirm that the header dates and task dates are real dates. Text imported from another system can look like a date without comparing as a date value.
If the bars shift into the wrong rows or columns, inspect the dollar signs. The header reference needs a fixed row, as in F$1. The task date references need fixed columns but relative rows, as in $B2 and $C2. Locking both row and column in $B$2 would make every task compare with the same start date.
If the final day is missing, confirm the finish-date comparison includes equals: the rule should use less than or equal to the finish date. If a task’s bar extends beyond its finish, check for a mistyped date, an incorrect range, or a formula whose row references do not adjust. If date headings show numbers, change their number format rather than altering the underlying dates.
When a stacked bar chart is a better choice
Conditional formatting is usually the quickest way to make an editable, worksheet-based schedule. A stacked bar chart can be preferable when you need a chart object that can be copied into a presentation or printed independently of the worksheet grid. The common approach is to chart a task’s start offset and duration as two series, then format the offset series with no fill so only the duration bars remain visible.
A chart takes more setup and may need adjustments to its date axis, task order, and labels. It is also less convenient for reading exact daily intersections than a cell grid. Choose conditional formatting for a compact working plan that people will update in Excel; choose a stacked bar chart when the chart itself is the deliverable. Neither approach automatically manages dependencies or resolves scheduling conflicts.
Frequently Asked Questions
What is the formula for a Gantt chart in Excel?
For timeline dates in row 1, task start dates in column B, finish dates in column C, and a formatting range beginning at F2, use =AND(F$1>=$B2,F$1<=$C2) as a conditional-formatting formula. It colors dates within each task’s inclusive start-to-finish period.
Can Excel create a Gantt chart automatically?
Excel does not provide a standard chart type named Gantt chart. You can build one with conditional formatting, as described here, or create a stacked bar chart using start dates and durations. Templates may provide a prebuilt layout, but the schedule still depends on correctly entered dates and ranges.
How do I show weekends in an Excel Gantt chart?
Use one column per calendar date and generate the headers by adding one day at a time. The conditional-formatting rule will color weekends that fall within a task’s dates. To omit weekends, generate weekday-only headers with WORKDAY instead; that produces a different view rather than changing the task’s dates.
Why is my Gantt chart formula not working?
Check the selected formatting range, the first cell referenced in the formula, and the dollar signs. Then verify that the headers and task dates are numeric Excel dates rather than text. A reversed start and finish date also leaves the rule with no dates to color.
Can I add dependencies to this formula?
The bar formula only displays dates between the entered start and finish. It does not calculate task dependencies or automatically move later tasks when an earlier task changes. You can add dependency data and separate scheduling formulas, but the simple Gantt rule itself is a visual indicator, not a project-planning engine.
Related
- Excel Job List Template: Track Applications and Follow-Ups
- Excel To-Do List Templates: How to Choose and Use One
- Excel Meal Prep Template: Plan Meals, Shop Smarter
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Excel Sparklines: Why They’re Missing and How to Fix ItIf Excel has no Sparklines button, the most likely reason is that you are using Excel for the…
- Pareto Chart in Excel: Create and Read One Step by StepTo make a reliable Pareto chart in Excel for Microsoft 365 on Windows, summarize your categories and counts,…
- Excel Pivot Chart Tutorial: Create and Customize ChartsTo create a PivotChart in Excel for Microsoft 365 on Windows, select a clean data range or Excel…
- Excel Shade Under Line Graph: How to Fill the AreaTo shade under a line graph in Excel, change the chart to a 2-D Area chart; Excel fills…
- X Values on a Graph: Meaning, Examples, and ExcelX values are the values plotted along a graph’s horizontal axis; each one tells you where a point…
- Excel Budget Chart: Build and Read ItAn Excel budget chart compares planned spending with actual spending so you can see where money is on…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.