Gantt Chart in Excel 2010: Formula and Setup

Charts & pivot tables · Updated November 20, 2026

In Excel 2010, make a Gantt chart with a Conditional Formatting formula such as =AND(E$1>=$B2,E$1<=$D2). If column B contains each task’s start date, column D its finish date, and row 1 the timeline dates beginning in E1, apply the rule to the task-and-date grid; Excel colors the cells that fall within each task’s schedule.

What the formula does

A Gantt chart is a schedule displayed as horizontal bars against a calendar. Excel 2010 does not have a dedicated Gantt chart command, but it can represent one in a worksheet using dates, a task list, and conditional formatting. The formula checks two conditions for each cell: whether the date above that cell is on or after the task’s start, and whether it is on or before the task’s finish. When both are true, the formatting rule applies its chosen fill color.

The dollar signs in the formula are important. In E$1, the column can change as the rule moves across the timeline, while row 1 stays fixed. In $B2 and $D2, the columns stay fixed as the rule moves down the task list, while the row changes for each task. This mix of fixed and relative references lets one rule cover the whole schedule grid.

Set up the task table and timeline

Start with a simple worksheet layout. Put task names in column A, start dates in B, finish dates in D, and timeline dates in row 1 starting at E1. Column C can hold duration, or you can leave it for another project detail. Use actual Excel dates rather than date-like text: a date should behave like a number when you change its format or add a day to it.

  • Put a clear project title and, if useful, an owner or status field above or beside the task list.
  • Enter one task per row, with consistent start and finish date conventions.
  • Format start, finish, and timeline cells as dates using Home > Number.
  • Leave the timeline row directly above the cells that will show the bars.

For a small project, type the timeline’s first date in E1, then fill the following dates across the row. To have Excel calculate the first date from the earliest task, with start dates in B2:B20, enter =MIN($B$2:$B$20) in E1; the result is the earliest start date in that range. With E1 as the previous day’s date, enter =E1+1 in F1 and copy it right; each cell advances the calendar by one day. Format the cells so the dates are easy to scan, for example as day and month rather than full serial values.

Calculate finish dates from durations

You may prefer to enter a duration rather than calculate every finish date yourself. In that layout, use column B for the start date, C for duration in calendar days, and D for the finish date. With a start date in B2 and a positive whole-number duration in C2, enter =B2+C2-1 in D2 and copy down. For a start date of 6 January and a duration of 3 days, the finish date is 8 January, so the task spans 6, 7, and 8 January. The minus one makes the start date count as the first day.

This calculation treats every calendar day as a scheduled day, including weekends. That is appropriate for continuous activities but not necessarily for a weekday-only work plan. A simple Gantt grid can still show weekends, while you decide whether to schedule work on them; if you need working-day calculations, account for holidays and the project’s working calendar separately. Avoid mixing calendar-day durations with finish dates that were entered using a different convention.

Apply the conditional-formatting formula

Once the dates and task rows are in place, select the cells where the bars should appear, such as E2:AZ20. Choose Home > Conditional Formatting > New Rule, then select “Use a formula to determine which cells to format.” If B contains starts, D contains finishes, and row 1 contains the timeline, enter =AND(E$1>=$B2,E$1<=$D2) in the formula box. Select Format, choose a fill color, and confirm the rule.

  1. Make sure the selected range begins at the same cell referenced as the first grid cell in the formula. For E2:AZ20, the formula starts with E$1 and row 2 references.
  2. Set the intended fill color in the Format dialog. The color is the visible task bar, not a value inserted into the cells.
  3. Check the rule’s Applies to range in Conditional Formatting > Manage Rules. Extend it if you add more task rows or timeline columns.
  4. Test one task whose start and finish dates are easy to verify before filling the sheet with more activities.

For example, suppose row 2 is “Draft proposal,” B2 is 6 January, and D2 is 8 January. If E1, F1, and G1 are 6, 7, and 8 January, the rule colors E2:G2. The dates before 6 January and after 8 January do not meet both conditions, so those cells remain unfilled. The rule does not create dates or change the task data; it only controls the appearance of cells in the selected range.

Make the schedule easier to read

Use a consistent bar color and a contrasting header so the task names and dates remain legible. Keep date labels concise, and choose a timeline scale that matches the project: daily columns work for short schedules, while weekly or monthly columns can make a long schedule fit on a page. If you change to weekly dates, decide what date each column represents and adjust the rule accordingly; the daily formula assumes each header is the exact day being tested.

Excel 2010 can apply multiple conditional-formatting rules, so you can add visual cues for other information, but keep the bar rule straightforward. For example, use separate columns for owner, status, or notes rather than encoding too many meanings in bar colors. You can also add borders or a subtle fill to weekends manually. If you add other conditional formats to the same cells, review their order and any “Stop If True” settings in Manage Rules so one rule does not unintentionally hide another.

For printing, set a sensible print area and use landscape orientation if the calendar is wide. In Excel 2010, use Page Layout > Print Area > Set Print Area, and use Page Layout > Orientation > Landscape when it improves the fit. Preview the result before printing; a timeline that is readable on screen may shrink too much when many date columns are printed on one page.

Common errors and how to fix them

  • No cells are colored: Check that the start and finish cells contain real dates, the finish is not earlier than the start, and the formula’s references match the actual columns. Also confirm the rule’s Applies to range includes the grid.
  • Bars are shifted by a row or column: The formula is interpreted relative to the first cell in the selected range. If the range starts at E2, use E$1 and row 2 references; do not select a different top-left cell without adapting the formula.
  • Only one task row works: Look for dollar signs in the wrong places. Lock the date-header row and the start/finish columns, but leave the task row number relative.
  • Dates display as numbers: Excel stores dates as serial numbers. Apply a date number format to the header and start/finish cells; the underlying values can still be valid even when displayed as numbers.
  • The last day is missing: The main rule uses an inclusive finish date. If you used a different finish convention or calculated a duration incorrectly, check that the finish date is the final day the task should occupy.

A zero-day duration deserves special attention. The finish-date calculation above assumes a positive duration and produces a finish one day before the start if duration is zero. For a milestone, either display a separate symbol or represent it with a one-day marker and explain that convention in the schedule. A milestone is an event, not a task that occupies a span of days, so a full-width bar can be misleading.

Formula grid or stacked bar chart?

The conditional-formatting grid is usually the simplest formula-based Gantt chart in Excel 2010. It is easy to edit alongside the task table, can show individual calendar dates, and does not require building a separate chart series. Its trade-off is that it can become very wide, and the bars are cell formatting rather than objects in a chart. A grid is often convenient for a working plan or a schedule that needs detailed date columns.

A stacked bar chart is another option when you want a chart object that can be resized and placed separately. It typically uses a series for the offset from the project start and a series for task duration; the offset series is formatted with no fill so only the duration bars show. In Excel 2010, use Insert > Bar > Stacked Bar, then adjust the category order and horizontal date axis as needed. That approach takes more chart setup and may need date-axis formatting, but can present a compact overview. Neither method automatically manages dependencies or reschedules later tasks when an earlier one slips; those relationships need additional planning or project-management software.

Frequently Asked Questions

What is the formula for a Gantt chart in Excel 2010?
The common conditional-formatting rule is =AND(E$1>=$B2,E$1<=$D2) when timeline dates are in row 1, task start dates are in column B, and finish dates are in D. Adjust the references to match your sheet.

Can Excel 2010 create a Gantt chart automatically?
Excel 2010 has no dedicated Gantt chart button. You can create the familiar date-grid version with conditional formatting, or build a stacked bar chart. Both methods require you to prepare the task dates and choose how the schedule should display.

How do I show duration instead of entering finish dates?
Use a duration column and calculate the finish date from the start and duration. With B2 as the start date and C2 as a positive number of calendar days, =B2+C2-1 in D2 returns the inclusive finish date. Copy the formula down for the other tasks.

Why does my Gantt chart formula not color the cells?
Common causes are text instead of real dates, a conditional-formatting range that does not include the intended cells, or references that do not match the range’s top-left cell. Verify that every task’s finish date is on or after its start date, then inspect the rule in Home > Conditional Formatting > Manage Rules.

Can the formula exclude weekends?
The basic rule checks the date at the top of each column and does not exclude weekends. You can omit weekend columns from the timeline or display them with a separate visual treatment. If task durations must count working days, define the work calendar and holidays rather than treating the simple calendar-day duration formula as a workday calculation.

Related

Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.

More in Charts & pivot tables

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.