In Excel 2013, make a Gantt chart by putting task start and finish dates beside a row of timeline dates, then applying this conditional-formatting formula to the chart grid: =AND(E$1>=$B2,E$1<=$C2,$B2<>"",$C2<>""). With task names in A, start dates in B, finish dates in C, and timeline dates beginning in E1, the rule colors each cell whose date falls within that task’s span.
What the Formula Does
A Gantt chart displays tasks against a calendar: each row is a task, and a horizontal bar shows when it starts and finishes. In this Excel 2013 setup, the formula does not go into every timeline cell as a worksheet formula. It is the test used by a conditional-formatting rule. Excel evaluates it for each cell in the selected grid and fills cells where the test returns TRUE.
The formula compares the date at the top of a timeline column with the start and finish dates for the task on that row. The dollar signs are important: E$1 keeps the header on row 1 while allowing its column to change; $B2 and $C2 keep the start and finish columns fixed while allowing the task row to change. The final two tests prevent empty task rows from being formatted.
The comparisons include both endpoints. A task starting Monday and finishing Wednesday therefore shows a bar on Monday, Tuesday, and Wednesday. That inclusive convention is useful for day-by-day plans, but it is different from subtracting dates to find elapsed time: a three-day inclusive task has a finish date minus start date of two.
Set Up the Worksheet
Use a simple table so the formula has predictable references. Put headers in row 1 and tasks in rows 2 through 20, for example:
- Column A: task name
- Column B: start date
- Column C: finish date
- Column D: optional duration
- Columns E onward: one date per timeline column
Enter real Excel dates in B and C, not text that merely looks like a date. A quick check is to select a date and temporarily change its number format to General; a real Excel date displays as a serial number. Set the date columns to a readable format such as 2-Mar or 02-Mar-26. Keep the start and finish dates in the same date system and ensure the finish is not earlier than the start.
For a daily plan, place the earliest task date in E1 and continue the calendar across row 1. With start dates in B2:B20 and finish dates in C2:C20, the formula for E1 is =MIN($B$2:$B$20). It returns the earliest numeric start date in that range. This is convenient when the plan changes, but it returns zero if there are no valid start dates, so enter at least one task date before relying on the timeline.
With the first timeline date in E1, the formula for F1 is =E1+1. Fill it right to create consecutive daily dates; for example, if E1 is March 2, F1 returns March 3. Format the headers as dates so that the serial values display as calendar dates. If you prefer to type the dates yourself, do so, but check that they increase by one day in each successive column.
Apply the Conditional-Formatting Rule
Select the blank chart area that corresponds to the task rows, such as E2:AZ20. The top-left cell of the selection must be E2 if you use the formula below. In Excel 2013 for Windows, choose Home > Conditional Formatting > New Rule, select Use a formula to determine which cells to format, and enter the formula. Select Format, choose a fill color, and confirm with OK. The formula assumes start dates in B2:B20, finish dates in C2:C20, and timeline dates in row 1:
=AND(E$1>=$B2,E$1<=$C2,$B2<>"",$C2<>"")
Apply the rule to the whole grid, not just E2. Excel adjusts the relative references as it evaluates other columns and rows. For instance, in F3 it effectively checks the date in F1 against the start and finish dates in row 3. Choose a clear fill color, then add borders or gridlines only if they improve readability. Freeze the task-name and date columns if the plan is wide: select the cell just below and to the right of the area to keep visible, then choose View > Freeze Panes > Freeze Panes.
Check the Result with a Small Example
Suppose A2 contains Design, B2 contains March 2, 2026, and C2 contains March 5, 2026. The timeline runs from E1 through H1 with dates March 2, 3, 4, and 5. The conditional-formatting test is true in all four corresponding cells, so the row displays a four-cell bar. In I2, where the header is March 6, the finish-date comparison is false, so that cell remains unfilled.
If you want a displayed inclusive duration, put a formula in D2 and fill it down. With the start date in B2 and finish date in C2, use =C2-B2+1. For the March 2–5 example, Excel returns 4 because the calculation counts both the first and last dates. This duration is a useful check against the visible daily bar, but it is not required for conditional formatting.
Test the setup with one short task before adding many rows. Confirm that a same-day task creates one colored cell, a multi-day task starts and ends in the intended columns, and a blank row stays uncolored. These checks catch reference mistakes while the grid is still small.
Choose a Daily or Workday Timeline
A daily timeline is usually easiest to understand: weekends appear as dates and the bar spans every calendar day between start and finish. This matches plans for events, rentals, or processes that run continuously. For a business schedule, you might instead want the duration to count weekdays and optionally skip holidays. Excel’s NETWORKDAYS function can calculate that duration, but it does not automatically change the date headers or make the conditional-formatting rule skip weekends. Those are separate choices.
For example, if B2 contains a Monday and C2 the following Friday, a calendar-day Gantt bar occupies five cells. A task spanning Friday to Monday occupies four calendar-date cells, including Saturday and Sunday. If weekends should not appear as working time, either hide or visually distinguish those columns, or construct a timeline that contains weekdays only. The simple “add one” header formula creates calendar days, not a working-day sequence.
You can shade weekend columns with another conditional-formatting rule. With timeline dates in E1 onward and the rule applied to the chart area, use =WEEKDAY(E$1,2)>5. The second argument makes Monday equal 1 and Sunday equal 7, so values greater than 5 identify Saturday and Sunday. Apply a subtle fill that remains distinguishable from the task-bar color; otherwise overlapping formatting can make bars hard to see.
Formatting and Useful Variations
Keep the dates readable without making the chart unnecessarily wide. A daily plan might use narrow columns and a compact date format, while a long project may be clearer in weekly or monthly increments. If you change the timeline to weekly headers, the basic daily formula needs a deliberate adjustment: each header must represent a date interval, and the rule must test whether a task overlaps that interval. Simply replacing daily dates with arbitrary week labels will not make the original formula work correctly.
Milestones can use the same basic setup. If a milestone has the same start and finish date, the rule colors one cell. Give milestone rows a distinctive task name or use a separate conditional-formatting rule and format so they stand out from longer tasks. Avoid making every task a different color unless the colors have a meaning, such as a workstream or owner; a consistent bar color is easier to scan.
Excel 2013 does not make the colored grid a separate project-management object. It is a worksheet table with conditional formatting, so changing a task’s dates updates the bar, but the chart will not automatically calculate task dependencies or reschedule later work. You can add columns for owner, status, or predecessor, but those fields need their own logic. Keep the task information in ordinary cells so it can be sorted, filtered, and reviewed alongside the timeline.
Common Problems and Fixes
- No bars appear: Check the conditional-formatting formula and its Applies to range. If the selected range begins at E2, the formula must be written as though E2 is the first cell. Also confirm that the dates are real numeric dates, not text.
- Bars are shifted one row or column: The formula’s starting reference probably does not match the top-left cell of the selected range. Recreate the rule with the correct first cell, or edit the rule under Home > Conditional Formatting > Manage Rules.
- Every task row has the same bar: Check that the task-row numbers are relative. In the intended formula, the row numbers in $B2 and $C2 are not locked, so they change for each task.
- Blank rows are colored: Include the two blank checks in the formula and make sure the selected area does not contain an unintended second rule. In Manage Rules, inspect the rule order and Applies to range.
- The bar ends a day too soon: Check whether the project uses inclusive dates. The formula includes both start and finish dates. A bar with a finish date equal to its start date is intentionally one cell long.
- Dates show as numbers: The values may still be valid dates; change the cell number format to a date format. If formatting them as dates produces unexpected results, check whether the entries were imported or stored as text.
Excel’s conditional-formatting formula uses the same regional list separator conventions as other formulas. The examples here use commas. If your Windows regional settings require semicolons, Excel may expect that separator when you enter a formula; the logic and cell references are unchanged.
Formula Grid or Stacked Bar Chart?
The conditional-formatting grid is a good choice when you want dates in columns, task details in rows, and a chart that is easy to edit directly on the sheet. It is quick to extend and makes a single day’s date easy to line up with tasks. Its limitation is that a long daily schedule can require many columns, and it does not behave like a chart object that can be moved or resized independently.
A stacked bar chart is another Excel option. It can represent a start-date offset series and a task-duration series, with the offset series formatted invisible to make the visible duration bars begin at different dates. That approach produces a conventional chart graphic, but it takes more chart configuration and can be less convenient for reading exact dates in a dense schedule. For a compact schedule that people will maintain in cells, conditional formatting is often more practical; for a presentation graphic, a chart may be preferable.
Frequently Asked Questions
What formula makes a Gantt bar in Excel 2013?
Use =AND(E$1>=$B2,E$1<=$C2,$B2<>"",$C2<>"") as a conditional-formatting rule. It assumes timeline dates begin in E1, task starts are in column B, task finishes are in column C, and the selected grid begins at E2.
Does the Gantt formula go in every cell?
No. Enter it in the conditional-formatting rule and apply that rule to the entire timeline grid. Excel evaluates the formula separately for each cell in the Applies to range, adjusting its relative references as needed.
Can Excel 2013 make a Gantt chart automatically?
Excel 2013 can display a Gantt-style schedule using worksheet cells and conditional formatting, or by configuring a stacked bar chart. The formula-based grid updates its bars when dates change, but it does not automatically manage dependencies or project rescheduling.
How do I show weekends in a different color?
Add a conditional-formatting rule based on =WEEKDAY(E$1,2)>5 to the timeline area. It identifies Saturday and Sunday when the headers are real Excel dates. Use a subtle fill so the task bars remain visible.
Why does my task bar show one extra day?
The example deliberately counts both the start date and finish date. If a task runs from March 2 through March 5, it occupies four calendar dates. If your project defines durations differently, decide whether its finish date is inclusive and adjust the schedule convention consistently rather than changing the rule at random.
Related
- Excel Named Ranges: How to Create and Use Them
- Excel Uppercase to Lowercase Shortcut: What Actually Works
- Excel Unmerge Cells Shortcut: Keys, Steps, and Fixes
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Set Up a Waterfall Chart in Excel: A Practical GuideTo set up a waterfall chart in Microsoft Excel for Microsoft 365 on Windows, place each category in…
- Side-by-Side Column Chart in Excel: How to Make OneTo make a side-by-side column chart in Excel for Microsoft 365 on Windows, arrange your categories in the…
- Excel Bar Graph Creation: Steps, Tips, and FixesTo create a bar graph in Excel for Microsoft 365 on Windows, select a labeled data range, then…
- Excel SmartArt Org Chart: Create and Customize ChartsTo create an org chart in Excel for Microsoft 365 on Windows, choose Insert > SmartArt > Hierarchy…
- Google Sheets Graphs: Create and Customize OneTo make a graph in Google Sheets, select the cells containing your data, choose Insert > Chart, then…
- Google Sheets Chart With Two Y-Axes: Setup and TipsYes. In Google Sheets, you can put one data series on a second, right-side y-axis: open the chart…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.