Excel Tutorials · Lesson 102b
Excel has no Gantt chart type, which is why most people give up before they start. There are two ways to build one anyway. The video walks through the conditional formatting method, where a grid of dates colors itself in from your start and end dates, and this page covers that in full plus the stacked bar chart method, so you can pick whichever fits your plan.
The same free Excel Xpert companion workbook used in the video, one tab per lesson. No login, no email required.
A Gantt chart is a horizontal view of a project schedule where each row is one task and each bar shows when that task runs, positioned along a date axis so its left edge sits at the start date and its length reflects the duration. Read down the left side and you have your task list. Read across and you can see what overlaps, what runs long, and where the schedule gets crowded. It has been the standard way to display a project schedule for over a century, and it remains the fastest way for someone who has never seen your plan to understand it.
Excel has no Gantt chart type in the Insert menu, so there are two ways to produce one. The first, and the one demonstrated in the video, is conditional formatting: lay dates across a header row, list tasks down the side, and write one rule that colors any cell whose date falls between that row's start and end. Nothing is typed into the grid, the bars draw themselves, and moving a date redraws the whole chart instantly.
The second is a stacked bar chart with the first series made invisible. A Gantt bar is really two stacked segments, an invisible one that pushes the bar right by the start date and a visible one whose length is the duration. Hide the first and what remains is a Gantt chart. This produces a real chart object you can drop into a slide, but it takes more setup and is fussier to maintain.
Which to use comes down to volume and audience. The conditional formatting grid handles long task lists, lives inside the sheet where the data already is, and updates itself with zero chart maintenance, which makes it the better working tool. The bar chart is the better presentation object. Both consume the same source table: task name, start date, and either an end date or a duration.
Step-by-step, matching the video above.
Task names down column A, start dates in column B, end dates in column C. These three columns drive everything, and the dates must be real dates rather than text or no rule will ever fire.
In the first grid cell of row 1, reference your project start date, then in the cell to its right type =D1+7 for weekly columns or =D1+1 for daily, and drag it across to the end of the project.
Apply a short format like d-mmm, then narrow the grid columns to roughly 3 to 5 pixels wide for daily columns so months of schedule fit on one screen.
Highlight only the date cells, not the task names or the date columns, starting from the top-left grid cell. Conditional formatting rules are written relative to that first selected cell, so where you start matters.
Go to Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format and enter =AND(D$1>=$B2,D$1<=$C2), adjusting the references to your own first grid cell and date columns.
Click Format > Fill, pick a solid color, and click OK. Every cell whose date falls inside a task's range fills instantly, and the rows of colored cells are your Gantt bars.
Create a second rule with =D$1=TODAY() and a contrasting fill or right border, then a third with =WEEKDAY(D$1,2)>5 and a light gray fill. Order them so the task bar rule sits on top.
| A: Task | B: Start | C: End | D: Sep 7 | E: Sep 14 | F: Sep 21 | G: Sep 28 |
|---|---|---|---|---|---|---|
| Discovery | 2026-09-07 | 2026-09-17 | █ | █ | ||
| Design | 2026-09-15 | 2026-09-29 | █ | █ | █ | |
| Build | 2026-09-28 | 2026-10-19 | █ | |||
| Testing | 2026-10-14 | 2026-10-26 |
Nothing is typed into columns D onward. Every colored block comes from the single rule =AND(D$1>=$B2,D$1<=$C2) comparing the date in the header row against the start and end dates on that row. Change one end date and the bar resizes immediately, with no chart to edit and no series ranges to update. Notice Design starts before Discovery finishes, and that overlap is the whole reason for drawing this instead of reading a table.
Mixed references are what make one rule work across hundreds of cells.
The dollar sign before the 1 pins the reference to row 1 while letting the column shift. As the rule is evaluated across the grid, every cell compares itself against the date directly above it in the header.
The dollar sign before the B pins the reference to the start-date column while letting the row shift, so every cell compares against its own row’s start date. $C2 does the same for the end date.
Excel writes the rule relative to the active cell in your selection. If your grid begins at D2, the formula must be written as though it lives in D2, or the shading appears offset by however many rows or columns you were out.
Open Conditional Formatting > Manage Rules and check the Applies To range. A range that shrank when rows were inserted is the second most common cause, after dates being stored as text.
Five additions that turn a colored grid into a working project view.
Add a Phase column, then write one rule per phase combining the date test with a category test: =AND(D$1>=$B2,D$1<=$C2,$E2="Design"). Each phase gets its own fill without touching the grid.
Add a % Complete column and a higher-priority rule that shades only the portion of the bar already elapsed, giving a two-tone bar where the darker section is work done.
A rule with =AND(D$1>=$B2,D$1<=$C2,$C2<TODAY(),$F2<>"Complete") turns any bar red once its end date has passed without the task being closed.
For a zero-length event, set the start and end date to the same day. The rule colors exactly one cell, which reads as a point on the timeline rather than a bar.
Switch the header row from daily to weekly increments with =D1+7, or monthly with =EDATE(D1,1). The same rule works unchanged, the grid simply gets coarser and a year fits on one screen.
A real chart object, better for slides, fussier to maintain.
Task name, start date, and duration in days. If you only hold end dates, add a helper column with =EndDate-StartDate, because the visible bar segment has to be a length rather than a position.
Select the task names and start dates, then Insert > Charts > Bar > Stacked Bar. Right-click, choose Select Data > Add, and add the duration column as a second series.
Click a start-date segment to select the whole series, press Ctrl+1, and set Fill > No fill. Do not delete it, that series is what pushes every bar out to its start date.
Excel plots bar categories bottom-up, so right-click the vertical axis, choose Format Axis, and tick Categories in reverse order to put task one at the top where a reader expects it.
The horizontal axis defaults to starting at zero, which is January 1900, squeezing the project into a sliver at the right. Set the Minimum to your project start and the Maximum to its end.
Drop the Gap Width to 20–50% for thicker bars, format the date axis with a short d-mmm format, set the major unit to 7 for weekly gridlines, and delete the legend, which distinguishes nothing once one series is hidden.
Put =TODAY() in two cells with the values 0 and 1 beside them, paste them into the chart as a new series, change its type to Scatter with Straight Lines, fix the secondary axis bounds at 0 and 1, hide that axis, and format the line as a red dash.
| A: Task | B: Start | C: Duration (days) | D: End |
|---|---|---|---|
| Discovery & requirements | 2026-09-07 | 10 | 2026-09-17 |
| Design | 2026-09-15 | 14 | 2026-09-29 |
| Build | 2026-09-28 | 21 | 2026-10-19 |
| Testing | 2026-10-14 | 12 | 2026-10-26 |
| Launch | 2026-10-26 | 3 | 2026-10-29 |
The chart consumes three columns: task, start and duration. The end column is shown only to make the point that duration should be derived with =D2-B2 rather than typed, so editing an end date resizes the bar automatically. The conditional formatting method uses the same table, just reading the end date directly instead of a duration.
Same source data, two very different maintenance stories.
It handles fifty tasks as easily as five, sits in the same sheet as the data, needs no chart object, and redraws the instant a date changes. For a plan someone updates weekly, this is the right answer.
A chart is an object you can copy into PowerPoint, resize and restyle. A conditionally formatted grid is a range of cells, which pastes as a picture and is harder to fit onto a slide cleanly.
Both methods plot calendar days, so bars visually span weekends. The grid can shade them with a WEEKDAY rule so they read as non-working. For accurate working-day math in your dates, use NETWORKDAYS and WORKDAY.
Excel cannot draw the arrows linking a predecessor to a successor, and it cannot cascade dates when a task slips. You can set one start date from another’s end with a formula, but nothing will draw the relationship.
Build the source as an Excel Table. A chart based on a Table extends automatically, and conditional formatting rules applied to Table rows carry down to new ones instead of stopping at the old last row.
Both methods carry over, with one shortcut Excel does not offer.
Use Format > Conditional formatting > Custom formula is with the same =AND(D$1>=$B2,D$1<=$C2) rule and the same mixed references. A grid built in one application transfers to the other unchanged.
Insert a stacked bar chart and set the first series to transparent under Customize > Series. Sheets is stricter about mixing dates and numbers, so calculate the offset as days from the project start with =B2-$B$2 rather than feeding raw dates in.
Insert > Timeline reads a range of start dates, end dates and labels and renders an interactive, zoomable schedule with no setup at all. There is no Excel equivalent, and for a quick plan it beats building either version by hand.
A date that only looks like a date will never satisfy the AND rule, so the grid stays blank, and the chart version plots categories instead of positions. Check alignment or use Text to Columns to convert the column first.
D$1 must lock the header row and $B2 must lock the date column. Absolute references everywhere colors either the whole grid or nothing, and it is the single most common reason a conditional formatting Gantt fails.
Excel builds the formula relative to the active cell in your selection. Select the grid starting from its true top-left cell, or the shading appears offset by however many rows or columns you were out.
Excel plots bar categories bottom-up, so an unreversed chart lists your last task first and reads as though the schedule runs backward.
Zero on a date axis is January 1900, which compresses the entire project into a thin band at the right edge. Set explicit minimum and maximum bounds to your real project dates.
Removing the first series collapses every bar back to the left edge. It has to stay in the chart with No fill applied, because it is what positions each bar along the timeline.
Neither method can link tasks so a slip cascades down the plan, and neither can draw connecting arrows. Once dependencies matter, the spreadsheet has hit its ceiling.
A plan shouldn’t need a chart rebuild every time a date moves.
Free 14-day trial. No credit card required.
No. You build one either with conditional formatting across a grid of dates, which is the method shown in the video, or from a stacked bar chart with the first series hidden.
List tasks with start and end dates, fill a header row with consecutive dates, select the grid, and add a rule using the formula =AND(D$1>=$B2,D$1<=$C2) with a solid fill color.
It checks whether the date in the header row above a cell falls on or between that row's start and end dates. If it does, the cell gets filled, which is what draws the bar.
Usually the dates are stored as text rather than real dates, or the mixed references are wrong. D$1 must lock the header row and $B2 must lock the start-date column.
Conditional formatting is better for working plans because it scales to long task lists and redraws instantly when dates change. The bar chart is better when you need an object to drop into a presentation.
Task name, start date, and either an end date for the conditional formatting method or a duration in days for the chart method. Duration is just end minus start.
In the grid version, add a second rule using =D$1=TODAY() with a contrasting fill or right border. In the chart version, add a two-point scatter series anchored to TODAY() on a secondary axis fixed between 0 and 1.
Add a rule with the formula =WEEKDAY(D$1,2)>5 and a light gray fill, which colors Saturday and Sunday columns behind your task bars.
Add a phase column, then write one rule per phase combining the date test with a category test, such as =AND(D$1>=$B2,D$1<=$C2,$E2="Design"), each with its own fill color.
In the grid, add a higher-priority rule shading only the elapsed portion of each bar. In the chart, split the duration into days completed and days remaining and stack both as visible series in different shades.
No. Neither method can cascade dates when a predecessor slips or draw dependency arrows. You can link start dates with formulas, but the relationship is never drawn.
Use NETWORKDAYS and WORKDAY when calculating durations and end dates, so working-day counts are correct, though the bars themselves will still stretch across weekends visually.
Excel plots bar chart categories from the bottom up. Right-click the vertical axis, choose Format Axis, and tick Categories in reverse order.
Yes. The conditional formatting rule works identically under Format > Conditional formatting > Custom formula is, the stacked bar method also works, and Sheets additionally offers a built-in Timeline view under Insert > Timeline.
Every business starts with a spreadsheet. Updoot is where you scale past it, with timelines and dependencies that keep up on their own.
Start Your Free Trial →