Enhanced Gantt Charts with Excel

This morning a business intelligence consultant from Finland, Janne Pyykkö, posted an example of a Gantt Chart that he created with Excel that uses a heatmap approach to display quantitative values along the timelines, such as the number of hours worked. This brings the Gantt Chart to life with additional information, which allows you to see patterns that often remained buried in the data. Janne’s example points out one of the useful visualization features that was added to the latest version of Excel: the ability to encode quantitative values as color in spreadsheet cells. You can see a full explanation of Janne’s example at his Blog and read the discussion surrounding it in my Discussion Forum.

Here’s a picture to whet your appetite:

Heatmap Matrix Gantt Chart.png

In this example, varying intensities of blue represent the hours worked per week for individual projects. The row at the bottom uses varying intensities of red to summarize the hours worked for all the projects. As you can see, this makes it easy to see the varying use of human resources on projects through time. The point isn’t to precisely compare the hours worked from one week to the next, but to view the pattern of change through time and to spot exceptions (for example, extreme values on either end of the scale).

Take care,

Signature

9 Comments on “Enhanced Gantt Charts with Excel”


By Janne Pyykkö. July 30th, 2007 at 3:12 am

Steve, thanks for publishing my idea here.

A short correction: Actually I composed the example with Voyant (a BI tool created in Finland), but Excel could have made it as well.

By Stephen Few. July 30th, 2007 at 10:08 am

Janne,

I do apologize for the error. After criticizing the charting capabilities of Excel so many times, I was excited to find an example of something useful that can be done with the latest version, which I could write about favorably. In my excitement I managed to miss the fact that you used Voyant. As you generously point out, your Gantt Chart design can also be produced in Excel, which makes it available to a broad audience. The one thing that Excel does not appear to handle, however, is the ability to turn off the numbers in the cells of the spreadsheet. This is an unfortunate omission, because when the numbers appear as text in the cells, they distract from the visual information that is encoded as color.

By Janne Pyykkö. July 30th, 2007 at 10:43 am

From my blog you can now find how to do it by using Voyant.

By Janne Pyykkö. July 30th, 2007 at 12:02 pm

By the way, you can turn off Excel numbers by setting a custom number format = a single space character!

By Helpful?. October 10th, 2007 at 9:36 am

Excel (at least up until version 2003) will not allow more than 3 conditional formats per cell, so I don’t think this chart can be replicated in quite the same way.

However, excel does allow for another way to hide the number in the cells by giving them the same color as the background.

By Fabrice Rimlinger. March 17th, 2008 at 2:44 am

Hi,

Regarding the previous comment, in order to have an unlimited list of criteria for conditional formatting, here is a simple macro for MS Excel :

Public Function CheckCells()
Set RangeToFormat = Sheets(“Plan”).Range(“CellsToCheck”)
For Each Cell In RangeToFormat
With Cell
‘ Empty cells
If IsEmpty(Cell) Then
.Interior.ColorIndex = xlNone
‘ Numeric cells
ElseIf IsNumeric(Cell.Value) Then
Select Case Cell.Value
Case Is = Worksheets(“Plan”).Range(“limite1″).Value
.Interior.Color = RGB(255, 255, 255)
.Font.Color = RGB(255, 255, 255)
Case Is = Worksheets(“Plan”).Range(“limite3″).Value
.Interior.Color = RGB(255, 128, 128)
.Font.Color = RGB(255, 128, 128)
‘Case Is = Worksheets(“Plan”).Range(“limite1″).Value
‘ .Interior.Color = RGB(255, 255, 255)
‘Case Is = Worksheets(“Plan”).Range(“limite6″).Value
‘ .Interior.Color = RGB(128, 0, 0)
‘ .Font.Color = RGB(128, 0, 0)
End Select
‘ Error cells
ElseIf IsError(Cell.Value) Then ‘Error cells
.Interior.ColorIndex = xlNone
.Font.Color = RGB(255, 255, 255)
‘ Other cells (text)
Else
.Interior.Color = RGB(192, 192, 192)
End If
End With
Next Cell
End Function

By Robert. July 30th, 2009 at 5:48 am

I’ve been dabbling with gantt charts & plotting data on calendar charts lately, and happened across the example in this blog. I thought it was pretty neat, so tried to create one with SAS/Graph. Here’s a link to my ouptut (and hoping I’ve made a few small improvements, of course! :)

http://robslink.com/SAS/democd39/gantt_color.htm

And here’s a link to more info on how I did it, and also the actual SAS code, if anybody would like a copy:

http://robslink.com/SAS/democd39/gantt_color_info.htm

By itcouple. February 17th, 2011 at 12:20 pm

Hi

Really nice example. I’m just trying to do that in SQL (SSRS). First attempt is promising the difficulty is blue color scale…. but a bit of google helped me to work it out…. for those looking for the same thing (algorithm) below is example

RGB, 0,0,255 = blue
RGB 255,255, 255 = white

colour scale? Just increase R + G without B
I’m just going to try that with starging RG = 50, ending RG = 170 and interval 20 which will give me 7 colours for 7 different range values.

I will post SSRS example soon.

Regards
Emil

By itcouple. February 17th, 2011 at 4:27 pm

Hi

Below is SSRS version

http://www.itcouple.co.uk/ssrs-gantt-chart-heat-map.aspx

Regards
Emil