I’ve developed what I think is a new chart type: The Ganttimekko (I think it’s new because I haven’t seen it used anywhere, but if I’m wrong, please let me know.) As its name suggests, the Ganttimekko is a combination of a Gantt chart and a Marimekko chart. It enables the creator to scale the height (or thickness) of the bars in a Gantt chart according to another variable, such as revenues or funding level. In a sense, it is a special case of a Marimekko chart.
Before I dive into different variations on the standard Gantt chart, the Ganttimekko, and how I created it in Excel, let me first describe the two underlying chart types:
The standard Gantt chart is essentially a bar chart in which horizontal bars are used to show the start and end times of different events or projects. The bars do not necessarily have a consistent baseline because the projects can start at different times. The modern Gantt chart is named for its inventor, Henry Gantt, who designed the chart in the early part of the 20th century to help supervisors manage and schedule different projects.
The Marimekko chart encodes two variables: one along the height of the vertical axis and another using the width of the bars. The height of the bars shows discrete numerical comparisons among the categories and their widths depict a second variable, which usually sums to a total. The Marimekko chart is reportedly named after the Finnish design firm Marimekko, which employed vibrant, colorful design styles in the 1960s. The chart has also been called a mosaic plot (though, technically, I think a mosaic plot sums to 100% on both dimensions), a spineplot, and a Mondrian diagram (after the Dutch painter).
The Ganttimekko combines these two charts, adding a second variable to the standard Gantt chart. The lengths of the bars show the timing of events or projects, and the heights (or thickness) of the bars are scaled to some amount, for instance a budget, funding amount, or revenues.
In the example I’m going to show here, I have (made up) data for seven projects funded by different government agencies across three topic areas: Health, Economy, and Taxes. The funded amounts vary from $1 million to $2.75 million, and the project dates vary between 1995 and 2016. The basic Gantt chart might look something like the following:
There are a couple of ways add the funded amounts to the basic chart. I could, for example, add another bar chart or use a color palette or ramp to encode the values.
I wanted to devise a different way to visualize this second variable within the bars of the Gantt chart itself and hence the Ganttimekko was born.
The Ganttimekko scales the height or thickness of the Gantt chart bars according to their dollar values—in this case, the funded amounts. Following the default Excel row height, I normalize everything so that the smallest project will have a row height of 16 pixels. The other rows are then scaled to 16 pixels; in other words, the row height for the third project of $2.0 million will be 32 pixels, exactly double the value of the first (smallest) project. (I could have done this manually, but I built a little VBA script to do it for me, which you’ll see below).
Bar Thickness versus Area
You might ask why I use the bar thickness rather than area to scale the bars. In short, I think it’s easier to see the differences between the values when thickness is used. Additionally, in some—maybe many—cases, the overall amount is what’s important. Consider box office returns, for example; we don’t really care about the per-month grosses, only the totals. We also know from lots of research in the data visualization field that we are pretty lousy at judging quantities from areas and are better able to discern them from the length of height of a bar or column.
An illustrative example may help. Take two projects, one project that will take place over 10 years and cost $10 million and another will take a single year and cost $1 million. For both, the annual cost is $1 million. If I size the Ganttimekko by area, the two bars are exactly the same height:
Area (total project dollars) = Length (years) X Height (unit)
Height (unit) = Area (total project dollars) / Length (years)
Project #1: Height = $10 million / 10 years = 1 unit
Project #2: Height = $1 million / 1 year = 1 unit
When the same data are sized by height, however, it’s easier and, I think, more intuitive to see the differences between the two. But note that in doing so, the differences in the areas are now exaggerated so that the area of the first project ($10 million over 10 years) is now 100 times that of the second.
Scaling by bar height/thickness may lead to other challenges—for example, if there are too many bars, it may be hard to fit them all on a page. In that case, it might make sense to use one of the different alternatives above or a different chart type altogether.
Building the Ganttimekko in Excel
Now that you’ve seen it, let me show you how I built it in Excel. There are two main parts: the bars themselves, for which I’ll use a couple of IF formulas and Excel’s Conditional Formatting tool, and the height of the bars, for which I’ll use a short VBA script. I’ll continue the example from above.
For a simple Gantt chart, you could simply highlight different cells manually using the Fill menu. That can be a little tedious and in cases where you have numerous projects or charts to create, you might want to create the chart more efficiently. I use two IF formulas to place a “0” in the cells that I will fill with a color so that it looks like there is a bar, and a “1” in the cells where there will be no fill color.
The IF statements themselves are quite simple. I compare the Start and End dates to the years listed in the column headers. If either of the IF statements are true, the cell fills with the number 1; if neither are true, the cell fills with the number 0. This gets a little more complicated with monthly data–if you’re interested in seeing that solution, please leave a note below.
In case you’re having trouble following, take a look at cell H2 and N2 in the screenshot above. In cell G2, the start date (1995) is not greater than the first year (1995), so the result of the first IF statement is zero; the end date (2000) is not less than the end year (2000), so the result of the second IF statement is also zero. In cell N2, the start date (1995) is not greater than that year (2001), so the result of the first IF statement is zero; the end date (2000) is less than this year (2001), so the result of the second IF statement is one.
Once the cells are filled with 1s and 0s, I use Excel’s Conditional Formatting tool to add color to cells with zeros and nothing with ones. To hide the numbers, I use my old number formatting trick of setting the custom format to “;;;”. I also use another set of Conditional Formatting formulas to put in the background colors (which could also be easily done manually). Finally, I place a white border around each Gantt bar to help differentiate them; I do this manually though I’m sure a little VBA script would work.
The Row Heights
With a short chart like this, I could resize the row heights manually using the options in the Format menu. But a little VBA script will make the process faster and easier. Here’s the VBA program I’ll use (thanks to Jon Peltier for helping me debug this).
Sub RowHeight() Dim hgt As Variant Dim WkgRng As Range Dim sAddress As String Dim WkgCell As Range Dim dblMin As Double sAddress = ActiveWindow.RangeSelection.Address On Error Resume Next Set WkgRng = Application.InputBox("Select range containing row heights", "Select Range", sAddress, , , , , 8) On Error GoTo 0 If WkgRng Is Nothing Then GoTo Cancelled 'Find minimum value in range and create scalar so that height of minimum value is 16 dblMin = Application.WorksheetFunction.Min(WkgRng) / 16 For Each WkgCell In WkgRng.Cells If WkgCell.Value > 1 Then hgt = WkgCell.Value / dblMin WkgCell.EntireRow.RowHeight = hgt End If Next WkgCell Cancelled: Exit Sub End Sub
The code is actually pretty simple: It prompts the user to input a cell range, and with that range [WkgRng], I find the minimum value [dblMin] and divide it by 16. When I resize the cells in the For Each loop, the smallest value gets a row height of 16 [hgt] and all other rows are scaled accordingly. Thus, if the smallest data value is 0.5, that row will be 16 pixels high, and a row with a data value of 2.0 will be 64 pixels high.
I have tried to present a few alternatives to the standard Gantt chart to add a variable showing the relative sizes of the different projects or observations. Some will surely disagree that changing the height of the bars is a useful way to encode this additional variable (and some will argue the area is preferred), but I find the chart easier to read with the thickness of the bars adjusted to the data and to use the height rather than area.
You can use this tutorial to create your own Ganttimekko chart, but if you’d like to use my Excel template, with macro and all, you can purchase it for a measly $5 in the PolicyViz Shop.