Skip to content
Tutorials Center
My learning

Topics

Lesson 53 of 93 ยท Charts and Visualization

Gantt, Timeline and Milestone Charts

Build a Gantt chart in Excel with a stacked bar chart, a conditional-formatting Gantt that follows your dates, a dynamic progress Gantt, and a milestone timeline.

A Gantt chart shows a project as horizontal bars on a calendar: each task starts where its start date is and lasts as long as its duration. Excel has no built-in Gantt type, but two reliable techniques build one: a stacked bar chart with an invisible first series, and conditional formatting that paints cells in a date grid. A timeline or milestone chart is the simpler cousin: dots on a line for events that happen on one day.

Excel cannot run on this site, so formulas and menu steps here are real Excel but read-only. The TryIts use a JavaScript stand-in to compute the numbers a Gantt needs (start offsets, durations, bar positions, progress) and draw them as text bars. It checks the arithmetic; the drawing itself is what Excel does for you.

The data you need

Every Gantt starts with a small table. Put it in an Excel Table (Excel Tables) so new tasks join the chart automatically.

Task Start End Duration Done %
Plan 2025-03-03 2025-03-07 5 100%
Design 2025-03-10 2025-03-21 12 60%
Build 2025-03-17 2025-04-04 19 20%
Test 2025-04-07 2025-04-11 5 0%

Duration can be calendar days, =C2-B2+1, or working days, =NETWORKDAYS(B2,C2) (Date Calculations). Bars on a date axis show calendar time, so for a bar chart use calendar days and use workdays only for a grid that skips weekends.

Method 1: a stacked bar chart

The idea: draw two stacked bars per task. The first bar is as long as the number of days from the project start to the task start and is made invisible; the second bar is the duration and is the visible one.

  1. Select the Task and Start columns and the Duration column (hold Ctrl). Make sure the Start column holds real dates, not text.
  2. Insert > Charts > Bar > Stacked Bar.
  3. Click the Start series (the first one), then Format Data Series > Fill > No fill. The bars now float at the right distance.
  4. Task order is upside down: select the vertical (category) axis, Format Axis > Categories in reverse order. This also moves the date axis to the top; set Horizontal axis crosses: At maximum category to bring it back to the bottom.
  5. Fix the date axis scale. The start dates are stored as numbers (3 March 2025 is serial 45719), so the axis would begin at zero. Click the horizontal axis and set Minimum to the project start serial number and Maximum to the finish. To find a serial, type the date in a cell and format it as Number, or use =B2*1. Set Major unit to 7 for weekly gridlines.
  6. Reduce Gap width to about 40% and delete the legend.

The reason this works is arithmetic: a stacked bar chart places each value after the previous one, so (start date) followed by (duration) ends exactly at the end date.

Run it: the numbers behind a stacked-bar Gantt

The stand-in converts dates to serial numbers (with Excel's 1900 date system), computes the invisible offset and the visible duration, and prints text bars scaled to the project length. Notice the invisible series is the start date itself, so the axis minimum must be fixed to the project start.

JS Offsets, durations and bars for a Gantt

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Adding progress

Split each bar into Done and Remaining series by adding two columns: Done days = Duration * Done% and Remaining = Duration - Done days. Chart three series: Start (no fill), Done (dark color), Remaining (light color), stacked. The same split is the #/- bar in the stand-in above.

Method 2: a conditional-formatting Gantt

This one is a worksheet, not a chart, and it moves with the data with no axis settings. Lay out dates across row 1 (for example in G1:AZ1, one column per day or week), then paint a cell when its date falls inside the task.

  1. In G1 put the project start; in H1, =G1+1, and fill right. Format row 1 as d (day number) and shrink the column width.
  2. Select G2:AZ20, choose Conditional Formatting > New Rule > Use a formula.
  3. Enter the rule (assuming Start in column B and End in column C; reference row 1 with a fixed row):
TEXT
=AND(G$1>=$B2, G$1<=$C2)
  1. Pick a fill color. Add a second rule above it for completed work, using a progress cell:
TEXT
=AND(G$1>=$B2, G$1<=$B2+($C2-$B2+1)*$E2-1)

Put the progress rule first and tick Stop if true so the done part is dark and the rest light. For a today line, add =G$1=TODAY() with a left and right border. Because the rules read the dates, changing a task's start or end moves its bar instantly, which makes this the easiest "dynamic Gantt". Rule formulas are explained in Conditional Formatting.

Run it: which cells light up

JS The conditional-formatting rule, cell by cell

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Dynamic and automatic Gantt charts

To make the Gantt-style grid react to inputs without hand edits:

  • Drive the first date from one cell (G1 = MIN(B2:B20)) so the grid always starts at the earliest task.
  • Use a scrolling start cell, so changing it by 7 moves the visible window by a week.
  • Use WORKDAY or NETWORKDAYS (Date Calculations) to compute End from Start and workdays, and let each task start the day after its predecessor: =WORKDAY(C2,1). When a duration changes, all following tasks move.
  • Add a Status column with IFS (IFS and SWITCH) to label tasks Not started, In progress or Complete from the dates and the done percentage.

Timeline and milestone charts

A milestone chart shows events, not spans. A simple version uses a scatter or line chart with dates on the X axis and a fixed Y value for every point (0), with data labels from the milestone names:

  1. Make a table: Date, Milestone, Height (positive and negative numbers, such as 1, -1, 2, -2, so labels stagger).
  2. Select Date and Height, Insert > Scatter.
  3. Add Error bars (Chart Elements > Error Bars > Minus, percentage 100%) so each point gets a vertical stem to the axis.
  4. Add data labels and choose Value From Cells to show the Milestone column. Hide the Y axis, and format the X axis as dates.

Excel also has SmartArt > Process > Basic Timeline, useful for a quick graphic with a handful of milestones but not tied to real dates (see Flowcharts, Org Charts and Infographics).

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. In the stacked-bar Gantt, why is the first series (Start) given No fill?

    Stacked bars start each series where the last one ended, so an invisible bar the length of the start date pushes the Duration bar to the right place.
  2. 2. A task starts 10 March 2025 and ends 21 March 2025. What does =End-Start+1 return?

    Both the first and last day count, so the duration is the difference plus one, which is 12 calendar days.
  3. 3. Which conditional-formatting formula paints a cell in G2 when its header date in G1 falls inside the task in row 2 (Start in B, End in C)?

    Locking the header row (G$1) and the Start/End columns ($B2, $C2) lets the rule copy correctly across every cell of the grid.
  4. 4. Why do you set the date axis minimum manually in a stacked-bar Gantt?

    Excel chooses the axis from zero for stacked values; a start date of 45719 would leave a huge empty area. Fixing the minimum to the project start fixes it.

Summary

  • A Gantt chart needs Task, Start and Duration (End-Start+1); progress can be a second visible series.
  • Stacked bar method: an invisible Start series, a visible Duration series, categories in reverse order, and a date axis with fixed minimum and maximum.
  • Conditional-formatting method: a date header row and the rule =AND(G$1>=$B2, G$1<=$C2) makes bars that follow the dates.
  • Chain tasks with WORKDAY for an automatic schedule.
  • Milestones are scatter points with stems (error bars) and labels from cells.

Next: Tornado, Funnel, Waffle, Pictograph and Actual-vs-Target Charts.