5/27/2018 My Excel Gantt Chart
1/20
A B C D E F G H
1 Start Done Not Done Duration End Finished
Unfinishe
d
2 Task 1 4/5/2001 14 0 14 4/19/2001 0.5 #N/A
3 Task 2 4/12/2001 21 0 21 5/3/2001 1.5 #N/A
The milestone values in column J (0.5, 1.5, etc.), which columns G and H link to, are de
and Unfinished markers at the mid-height of the Done/Not Done bars.
The user enters Task Name (column A), Start Date (B), either Duration (E) or End Date (as well as the data in the yellow range. The other columns are calculated.
e ue s a e range co umns t roug conta ns t e ne c art ata use to p ot m
time scale horizontal axis. Column I within the blue range is filled with #N/A errors, ent
#N/A in the cells. In a Line or XY chart series, no marker is plotted for #N/A; if there ar
the connecting line connects these valid points, interpolating across the #N/A.
e ye ow s a e range e ow e ue s a e range s an ex ens on o e ne c ar
the vertical line(s); add more rows to add more vertical lines. The value in the yellow cel
dynamically linked to the current date by inserting the formula =TODAY()in the cell. Colu
yellow range is filled with #N/A errors, enter the formula =NA()or type #N/A in the cells
cell(s) of column I is selected to put the point at the top edge of the chart.
Sample Data
The table below contains the data for this example. The sections of the data range are de
The green shaded range (columns A through D) contains the stacked bar data which is u
the chart.
Gantt charts are useful tools in program management, which help to show graphically whfinish, and which tasks are underway at any given time. Gantt charts help in scheduling o
program, and in identifying potential resource issues in the schedule. A simple Gantt char
chart, that is, a stacked bar chart in which the first series is formatted to be invisible. The
stacked on the first, but these bars appear to float in the middle of the chart, because the
invisible. My article Gantt Charts in Microsoft Excel in Tech Trax e-zine describes this simp
s examp e s more eta e , an t ere ore more comp cate . ere are two v s e ar
show fraction complete and fraction incomplete. In addition, two line chart series are add
completed and not-yet-completed tasks. Excel will not allow an XY series to be added to a
so an additional line series is used as an anchor for a vertical line and label. Using a line c
versatile time scale axis of the line chart as the horizontal axis of the Gantt chart.
Advanced Gantt Charts in Microsoft Excel.
Introduction
5/27/2018 My Excel Gantt Chart
2/20
4 Task 3 4/25/2001 10.5 3.5 14 5/9/2001 #N/A 2.5
5 Task 4 4/25/2001 21 7 28 5/23/2001 #N/A 3.5
6 Task 5 5/15/2001 7 7 14 5/29/2001 #N/A 4.5
7 Task 6 5/18/2001 7 21 28 6/15/2001 #N/A 5.5
8 Task 7 5/18/2001 12.25 22.75 35 6/22/2001 #N/A 6.5
9 Task 8 5/25/2001 8.75 26.25 35 6/29/2001 #N/A 7.5
10 Task 9 6/5/2001 0 24 24 6/29/2001 #N/A 8.511 6/1/2001 #N/A #N/A
Cell FormulaC2: 0
D2: 0E2*: 0
F2 : 0
G2: #N/A
H2: 0
J2: 49.5
Delete the horizontal gridlines, and add vertical gridlines (Chart menu > Chart Options >
Major Gridlines).
To create the chart, select the data in range F1:I11 (the blue and yellow shaded regions i
Chart Wizard, and choose a Line chart in Step 1 of the wizard.
Formulas
These are the formulas that make the chart work.
Constructing the Chart
Formulas filled
down to row 10
n y or as
formulas,
depending on
which is filled by
the user.
5/27/2018 My Excel Gantt Chart
3/20
Select the Start series, then use Chart Type on the Chart menu to change the series to a
Select and copy range A1:D10 (shaded green above), select the chart, and use Paste Spe
add the data as New Series.
The new chart has only primary X and Y axes.
Click here to see chart with axes identified.
Double click the horizontal (time) axis; on the Scale tab, set appropriate Minimum (4/1/0
(14 days), and Minor (7 days) scale parameters. Also on the scale tab, uncheck the Value
Dates checkbox. On the Number tab, choose an appropriate date format (m/d).
5/27/2018 My Excel Gantt Chart
4/20
Choose Chart Options on the Chart menu, click on the Axes tab, and check the Secondary
adds the list of tasks as axis labels.
The chart gains a secondary Y axis when the series is converted to a b
Select the Done series, and again use Chart Type on the Chart menu to change the series
Repeat for the Not Done series. You can use the F4 key to repeat the latest action for the
the Done series; for some reason, when you assign the Stacked Bar type to the Start seri
considers it a Clustered Bar.
Click here to see chart with axes identified.
5/27/2018 My Excel Gantt Chart
5/20
Double click on the bottom time axis, and click on the Scale tab. Check the Value (Y) Axis
checkbox. This moves the numeric vertical axis from left to right (where the numbers tem
on the right axis).
The chart has primary and secondary X and Y axes.
The task labels (secondary X axis labels) are on the right, not the
Click here to see chart with axes identified.
5/27/2018 My Excel Gantt Chart
6/20
Double click left (Task) axis, and click on the Scale tab. Check the Categories in Reverse
Value (Y) Axis Crosses at Maximum Category box, so the tasks are represented from top t
Number of Categories Between Tick Mark Labels box, to force Excel to display each label.
The chart has primary and secondary X and Y axes.The task labels (secondary X axis labels) are on the left, where we wa
Click here to see chart with axes identified.
Double click on the top time axis, and click on the Scale tab. Uncheck the Category (X) Ax
checkbox. This moves the task list to the left side of the chart.
5/27/2018 My Excel Gantt Chart
7/20
Format the bar timeline series and milestone markers. Make the Start series invisible by c
Area on the Patterns tab. Choose appropriate colors, marker shapes, and marker sizes for
Double click the top axis. Set the scale parameters to: Minimum 4/1/01, Maximum 7/8/0
to match the bottom time axis that was formatted earlier. Even though this is a value axis
number, Excel will accept numbers in date format. On the Patterns tab, set Tick Mark Lab
top and bottom time scales must be synchronized (manually) whe
Double click on the right numerical axis. Check the Values in Reverse Order box and also
Maximum Value box. Make sure the minimum and maximum are set to 0 and 9, and unch
each, so Excel doesn't unexpectedly change the axis. If more tasks are added, b
be rescaled in tandem.On the Patterns tab, choose None for Tick Marks and Tick L
5/27/2018 My Excel Gantt Chart
8/20
Double click on the Vert Line series, and on the Patterns tab, select None for Line and Mar
choose Plus (it goes down , but the axis is plotted in reverse order), with a Fixed Value of
bottom).
Resize the plot area, moving down the top edge to make room for a label. Double click th
point in this example), and add data labels, using the Category Name option. Double click
Alignment tab, choose Above for Label Position.
Delete the Legend, and stretch the Plot Area to fill the whole chart.
5/27/2018 My Excel Gantt Chart
9/20
The primary Y axis and secondary Y axis are hidden.
The chart has primary and secondary X and Y axes.
Click here to see chart with axes identified.
5/27/2018 My Excel Gantt Chart
10/20
I J K
Vert Line Milestone
%
Complete
#N/A 0.5 100%
#N/A 1.5 100%
ined to place the Finished
), and Percent Complete (K),
estones an prov e t e
r the formula =NA()or type
valid points on both sides,
a a. e ye ow ce s anc ors
ll of column F could be
mns G and H within the
. The value of 0 in the yellow
cribed in more detail:
sed to create the timelines in
n tasks must start andthe many tasks in a
is merely a floating bar
second series of bars are
first series is formatted to be
le approach.
, so t e oat ng ar can
d to show milestones for
Bar-Line combination chart,
hart allows us to use the
5/27/2018 My Excel Gantt Chart
11/20
#N/A 2.5 75%
#N/A 3.5 75%
#N/A 4.5 50%
#N/A 5.5 25%
#N/A 6.5 35%
#N/A 7.5 25%
#N/A 8.5 0%0
ridlines tab > Category Axis
the table above), start the
5/27/2018 My Excel Gantt Chart
12/20
tacked Bar type.
ial from the Edit menu to
1), Maximum (7/8/01), Major
(Y) Axis Crosses Between
5/27/2018 My Excel Gantt Chart
13/20
Category checkbox. This
ar series.
to a Stacked Bar type.
Not Done series, but not for
s in the previous step, Excel
5/27/2018 My Excel Gantt Chart
14/20
Crosses at Maximum Value
porarily overlap the task list
left.
5/27/2018 My Excel Gantt Chart
15/20
rder box and uncheck the
o bottom. Enter 1 in the
nt them.
is Crosses at Maximum Value
5/27/2018 My Excel Gantt Chart
16/20
hoosing None for Border and
the other series.
, Major Unit 14, Minor Unit 7
that is expecting a "regular"
ls to None. Note: the
the data changes.
he Category Axis Crosses at
eck the Auto box in front of
th vertical axes must
abels to hide the numbers.
5/27/2018 My Excel Gantt Chart
17/20
ker. On the Y Error Bars tab,
9 (to stretch from top to
Vert Line series (single
the data label, and on the
5/27/2018 My Excel Gantt Chart
18/20
5/27/2018 My Excel Gantt Chart
19/20
Start Done Not Done Duration End Finished
Unfinishe
d Vert Line
Task 1 8/12/2008 14 0 14 8/26/2008 0.5 #N/A #N/A
Task 2 8/19/2008 21 0 21 9/9/2008 1.5 #N/A #N/A
Task 3 9/1/2008 10.5 3.5 14 9/15/2008 #N/A 2.5 #N/ATask 4 9/1/2008 21 7 28 9/29/2008 #N/A 3.5 #N/A
Task 5 9/21/2008 7 7 14 10/5/2008 #N/A 4.5 #N/A
Task 6 9/24/2008 7 21 28 10/22/2008 #N/A 5.5 #N/A
Task 7 9/24/2008 12.25 22.75 35 10/29/2008 #N/A 6.5 #N/A
Task 8 10/1/2008 8.75 26.25 35 11/5/2008 #N/A 7.5 #N/A
Task 9 10/12/2008 0 24 24 11/5/2008 #N/A 8.5 #N/A
4/14/2014 #N/A #N/A 0
8/1 8/15 8/29 9/12 9/26 10/10 10/24 11/7
Task 1
Task 2
Task 3
Task 4
Task 5
Task 6
Task 7
Task 8
Task 9
5/27/2018 My Excel Gantt Chart
20/20
Milestone
%
Complete
0.5 100% 1
1.5 100%
2.5 75%3.5 75%
4.5 50%
5.5 25%
6.5 35%
7.5 25%
8.5 0%
11/21