Project Planner: An Excel Gantt Chart using Conditional Formatting

1.     What is a Gantt Chart?

A Gantt chart is a bar chart that illustrates a project schedule. This chart lists the project’s tasks on the vertical axis. Time intervals are on the horizontal axis. The width of the horizontal bars in the graph shows the duration of each activity.  It was designed and popularized by Henry Gantt around the years 1910–1915. Modern Gantt charts also show the dependency relationships between activities and the current schedule status. (Ref: Wikipedia)

2.     Creating a Gantt Chart in Excel using a Bar Chart

The bad news is that, while a Gantt chart is a variety of bar chart, it is not in Excel’s standard charts. It requires a lot of work to create from scratch. You might look at a dedicated project planning program, like Microsoft Project, instead.

The good news is that, to create a Gantt Chart in Excel, you do not have to start from scratch.  There are already some excellent resources:

This article on ablebits.com demonstrates in detail how to use an Excel stacked bar chart for this.  But there are some features of Gantt charts that it does not do well, or at all.

For greater sophistication, we have to move away from Excel’s charts, and use other methods:

3.     A Gantt Chart in Excel using Cell Shading

For a more complicated version, we went to this article at Microsoft Support.  We then downloaded the sample Gantt Chart from the link on that page.  Now that link instead goes to a general list of templates (some of which are Gantt Charts).  The one we downloaded is better in several aspects, so you can ask us to email it to you.  It looks like this:

It uses cell shading (done with clever conditional formatting), not an Excel chart.

Let’s explain how it works.

4.     Gridlines

It’s a minor point but, to give a cleaner appearance, “Show Gridlines” has been switched off. We use File > Options > Advanced > Display:  This hides the lines that demarcate the cell edges.

Or on the View ribbon > Show > Gridlines:

5.     Data Validation Message Help, not Comments

When the cell selector is in various cells, helpful instruction messages appear.  See, for example, this message (rather like a yellow “Post-It” note)  for cell A1:

These messages are:

A1: Project planner uses periods for intervals. Start=1 is period 1 and duration=5 means project spans 5 periods starting from start period. Enter data starting in B5 to update the chart
B1: Title of the project. Enter a new title in this cell. Highlight a period in H2. Chart legend is in J2 to AI2

B2:F2: Select a period to highlight in H2. A Chart legend is in J2 to AI2
H2: Enter a period in the range of 1 to 60 or select a period from the list. Press ALT+DOWN ARROW to navigate the list, then ENTER to select a value
J2: This legend cell indicates plan duration
P2: This legend cell indicates actual duration
U2: This legend cell indicates the percentage of project completed
Z2: This legend cell indicates actual duration beyond plan
AH2: This legend cell indicates the percentage of project completed beyond plan

B3: Enter activity in column B, starting with cell B5
C3: Enter plan start period in column C, starting with cell C5
D3: Enter plan duration period in column D, starting with cell D5
E3: Enter actual start period in column E, starting with cell E5
F3: Enter actual duration period in column F, starting with cell F5
G3: Enter the percentage of project completed in column G, starting with cell G5
H3: Periods are charted from 1 to 60 starting from cell H4 to cell BO4

B4: Enter activity in column B, starting with cell B5
C4: Enter plan start period in column C, starting with cell C5
D4: Enter plan duration period in column D, starting with cell D5
E4: Enter actual start period in column E, starting with cell E5
F4: Enter actual duration period in column F, starting with cell F5
G4: Enter the percentage of project completed in column G, starting with cell G5

These messages are not what Excel calls “comments”!  On the Review ribbon, in the Comments group, you will see “New Comment” available.  Meanwhile, “Delete”, “Previous” and “Next” are greyed out. This shows that there are no comments.

So how is it done?

They are Data Validation Messages.  Click on any of the cells listed above.  On the Data ribbon, in the Data Tools group, click on the Data Validation button:

That brings up this dialog box, where you can change the message:

6.     Using Data Validation Further

Data Validation Input Messages are useful for Help, but there is much more we can do with Data Validation.

Click on Cell H2 (Period Highlight).  Here we see not only a helpful Input Message, but also a drop-down list:

Pressing Alt+Down Arrow activates the drop-down list.  Or you can click the downward-pointing triangle button on the right of cell H2.

Select the Data ribbon > Data Tools group > Data Validation. The pop-up message is in the Input Message.  The in-cell dropdown List is set up in the Settings, with the allowed values in the Source:

There is also an Error Alert:

7.     The Period Highlight

Change the Period Highlight number in cell H3.  Notice how the orange shaded column moves to the new period number.  How is this done?

With conditional formatting.

To see it, select any of the Period Number cells in H4:BO4.  On the Home ribbon, in the Styles group, click Conditional Formatting > Manage Rules:

The formula is =H$4=period_selected

If we click the button Edit Rule:

The Format button sets the desired Fill:

8.     Other Conditional Formatting

The shading of the other cells in the Gantt Chart also uses Conditional Formatting.  If we show formatting rules for This Worksheet:

Notice that “Stop if True” is never selected.  Hence, non-conflicting patterns can be overlaid.  For example, the striped Pattern Style of Planned Times can overlay on any of the others:

9.     The Conditional Formatting Formulas

Remember the shading?

The formulas are:

% Complete: =PercentComplete (solid purple) applies to $H$5:$BO$30
Horizontal Line under chart: =TRUE [always True!] (purple top border line) applies to $B$31:$BO$31
% Complete (beyond plan): =PercentCompleteBeyond (orange fill) applies to $H$5:$BO$30
Actual Start: =Actual (purple diagonal stripes on light purple) applies to $H$5:$BO$30
Actual (beyond plan): =ActualBeyond (purple diagonal stripes on =$H$5:$BO$30
Plan Duration: =Plan (purple diagonal stripes on no colour) applies to $H$5:$BO$30
Period Highlight (row 4): =H$4=period_selected (light orange background) applies to $H$4:$BO$4
Period Highlight (other): =H$4=period_selected (light orange background) applies to $H$5:$BO$30
Even period columns: =MOD(COLUMN(),2) (light grey background) applies to $H$5:$BO$30
Odd period columns: =MOD(COLUMN(),2)=0 (white background) applies to $H$5:$BO$30

10.   Range Names Containing Formulas

The above formulas rely on the following (bold) Range Names:

period_selected =$H$2 – this is the only one that names a cell.  The rest are formulas.

Let’s pause for a moment to look at Range Names that are formulas.

We’ve seen in an earlier post how you can give a cell or range of cells a range name, and then use that in formulas. However, it is a powerful yet little-known feature (little-known to me anyway!) that you can create a range name that instead uses a formula.

The easiest way to create such a formula is to put it into a cell first.  Then copy it and paste it into Formula (ribbon) > Defined Names (group) > Define Name, in the “Refers to” box:

If you use any non-absolute cell references (without the $ prefix), it is critical to select the correct cell before you enter the formula.  Parts that apply to the current cell (column and/or row) will adjust to match whichever cell uses the formula.  Before taking the picture above, we selected cell H5 on the Project Planner sheet.  So we have ‘Project Planner’!$C5 in the formula.  Had we first activated a cell in row 6, we would have had $C6 in the formula.  And so on.

The above Range Name Formula uses another Range Name Formula, PeriodInPlan.  That itself uses relative cell references, as we will see in a moment.  Using relative references, such a formula can return different results for different cells.  This feature lets the same formula give different conditional formatting for different cells.

The Range Name Formulas below are those you will see with cell H5 activated.  In the formulas, H = current column, 5 = current row:

PeriodInPlan =H$4=MEDIAN(H$4, $C5, $C5+$D5-1)
How does this work?  MEDIAN of three values will always give us the value that is arithmetically in the middle. H$4 (current column, always row 4) is the period number.  $C5 (always column C, current row) is the starting period for the activity. $D5 is the plan duration, hence $C5+$D5-1 is the end period for the activity.  Now take the formula as a whole.  If MEDIAN places the period number between the start and end of the activity, then H$4=MEDIAN will return True. Otherwise, PeriodInPlan will be False.

Plan =PeriodInPlan*($C5>0) – As we have just seen, PeriodInPlan is a True or False value.  ($C5>0) will return True if the starting period for the activity is greater than zero. That is, if there actually is an activity.  TRUE has a value of 1, FALSE has a value of zero.  Hence if both PeriodInPlan and ($C5>0) are true, their product is True.  Plan will be False.  This is equivalent to the AND() function.

PeriodInActual =H$4=MEDIAN(H$4,$E5 -1) – This works like PeriodInPlan.  It uses Actual Start ($E5) and Actual Duration ($F5) to return True if the period number is between actual start and end. Otherwise, PeriodInActual will be False.

ActualBeyond =PeriodInActual*($E5>0) – This works identically to Plan, but for Actual.  It is used for Actual (beyond plan): The part of Actual that extends beyond planned end.

Actual =(PeriodInActual*($E5>0))*PeriodInPlan. – This gives us the overlap of Actual with Plan.  If all three of PeriodInActual, ($E5>0), and PeriodInPlan are True, the product is True. Otherwise, Actual will be False.  Due to the associative rule of multiplication, the parentheses around the first two terms have no effect.  But what are a few superfluous parentheses between friends?

PercentCompleteBeyond =(H$4=MEDIAN(H$4,$E5,$E5+$F5)*($E5>0))*((H$4<(INT($E5+$F5*$G5)))+(H$4=$E5))*($G5>0)
Yes, the terms are more complicated.  Still, this follows the same pattern of PeriodInPlan and PeriodInActual.  It uses MEDIAN to compute whether H$4 is within the completion beyond plan..

PercentComplete =PercentCompleteBeyond*PeriodInPlan. – This works identically to Plan and ActualBeyond, but for the Percent Complete.

That then, is how this Gannt Chart Project Planner works. It does not use an Excel chart.  Instead, it has clever conditional formatting. That, in turn, relies heavily on intricate Range Name Formulas.