Recently I wrote a tutorial showing How to Make Vertical Bullet Graphs in Excel. Bullet charts are also useful in a horizontal arrangement, which is the topic for today.
Stephen Few developed Bullet Graphs to address the many issues with gauge charts. Bullet graphs require less space than gauges to show the same amount of information, and their linear style makes it easy to compare values on adjacent bullet graphs. Bullet graphs are described in Bullet Graphs for Not-to-Exceed Targets and Few has published a Bullet Graph Design Specification.
How to Make Horizontal Bullet Graphs in Excel
There are a number of ways you could make a bullet graph in Excel. I’ve picked a method which is not too difficult, and I’ve selected a protocol that works as written in Excel 2003 (and earlier) and in Excel 2007 (and later).
A bullet chart in its simplest form needs only a few values. There are three shaded bands in the background, usually light, medium, and dark gray, to indicate qualitative ranges of, for example, poor, fair, and good values. There is a bar that indicates the actual measured value. And there is a short line tht indicates a target (comparative) value.
The following table shows our data. The dark band goes from 0 to 50%, the medium band from 50 to 75%, and the light band from 75 to 100%. Column A shows the absolute values for the transitions between shaded bands, while column B shows the relative values, needed for stacked bar charts. The actual value is 70%, and the target is 85%. You don’t need to use percentages; any numerical values work just as well.
Unlike the vertical bullet graph, the horizontal bullet graph needs some data conditioning. To get the appearance wide background and narrow foreground bars, the horizontal bullet graph uses three sets of stacked bars. The outer two stacks show the background bars, while the middle bar shows the value, plus whatever parts of the background bars are taller than the value bar.
This table shows the added columns added to make the horizontal chart work properly.
Cells D3 and F3 link to cell B3, cells D4 and F4 link to cell B4, and cells D5 and F5 link to cell B5. Cell E2 links to cell B2, and cell E6 links to cell B6. Cells E3:E5 contain more complicated formulas:
Cell E3: =MIN(MAX(SUM($B$3:$B3)-$B$2,0),$B3)
Cell E4: =MIN(MAX(SUM($B$3:$B4)-$B$2,0),$B4)
Cell E5: =MIN(MAX(SUM($B$3:$B5)-$B$2,0),$B5)
Start by selecting C1:F6 and making a stacked bar chart. So far, so good. Here is the Excel 2007 version.
Here is the 2003 version.
Select the Target series, and from the Chart Tools > Design tab, choose Change Chart Type, and select XY (Scatter). This is the Excel 2007 chart.
On the Chart Tools > Layout tab, go to Axes > Secondary Horizontal Axis, and select Show Default Axis.
Excel 2003 automatically gives you this axis when you change a bar series to an XY series, so the above two take only one step in Excel 2003.
Now we need to adjust the source data of the Target series. When the series is selected, we see the highlighted name (green), X labels (purple) and Y Values (blue) outlines.
We need to change the source data so the orphan cell containing 0.5 becomes the Y value and the cell with 85% (the old Y value) becomes the X value.
You can change the source data by dragging and resizing the highlighting rectangles, by opening the source data dialog and changing selections there, or by changing the series formula from:
=SERIES('Bullet Graph'!$C$6,'Bullet Graph'!$D$1:$F$1,'Bullet Graph'!$D$6:$F$6,5)
=SERIES('Bullet Graph'!$C$6,'Bullet Graph'!$E$6,'Bullet Graph'!$E$7,5)
I like editing the formula, because I can highlight an incorrect address in the formula and select the correct range with the mouse.
Here are the Excel 2007 and Excel 2003 charts with the corrected Target series.
The right vertical axis has to be rescaled so its min and max are 0 and 1, so the Target marker (value = 0.5) is vertically centered in the chart. Here are the Excel 2007 and 2003 charts after changing the axis scale.
Now let’s fill out the chart. Set the min and max of both horizontal axes to 0 and 1. The top axis looks okay, but it’s max is automatic, and if the Target is changed to a number close to 100%, Excel will adjust the maximum. So fix the maximum at 1.
Also format any of the bar series, and change the gap width to zero (no gap)
Add error bars to the Target series. You can use the Error Bar Utility that is discussed in my post Custom Error Bars in Excel Charts, or you can follow the manual instructions below.
In Excel 2007, go to Chart Tools > Layout > Error Bars > More Error Bars Options. The Vertical Error Bars dialog opens, but at first you get the default error bars, which look like this:
In the Vertical Error Bars dialog, change the value to 0.35 and remove the end caps, change the line color to red, and choose a medium line width (I used 1.5 pt). Then select the horizontal error bars in the chart and press the Delete button.
In Excel 2003, you add error bars by formatting the series. You can add the Y error bar with the value you want (0.35), and you get the default formatting.
Remove the end caps, change the line color to red, and select the medium line thickness.
Format the Target series so it has no marker, shown below for Excel 2007 and 2003.
Now hide all of the axes. Format each, choose No Line, and set tick marks and labels to None. Stretch the plot area to fill up the chart as much as possible. Excel 2007 lets the plot area extend just about to the edges of the chart, while Excel 2003 keeps a no-mans-land border that nothing can encroach upon. These charts show the bullet graphs with their enveloping chart areas for Excel 2007 and 2003.
Now the bullet graphs can be resized to fit where they are needed. The Excel 2007 charts can be shrunk as much as you want; these two are three rows and one row high.
The Excel 2003 charts have a limit on how much they can be shrunk without problems. The chart area can be shrunk indefinitely, but the plot area stops shrinking, so it isn’t all visible as the chart area continues to shrink. This is illustrated below with visible borders on chart area and plot area.
This is as far as the Excel 2003 chart can be shrunk without truncating the plot area.
Oops! There goes part of the chart…
… and now there’s not much left.
We can address this problem by shrinking the plot area within the chart area. It seems that the plot area itself doesn’t stop shrinking, but the largest space allocated for it does.
The plot area is still visible here, where before it was already partially hidden.
But eventually we start cutting off the plot area.
You can alternately shrink the plot area and chart area until you get something that works. Here the plot area is about one row high, within a chart area that’s about twice that.
Here is a 1-row-high plot area within a chart area that is just about to start blocking it. So we’ll have to stop here. Note that we need to allow for a few points above and below the chart if we want a visible bullet chart that’s one row high.
Without borders, and with a transparent chart area (i.e., no fill color), we can get 1-row-high bullet graphs in Excel 2003.
You can use different colors for your bullet charts. In the ones below, I’ve changed the Target line from red to black. The color schemes are the same dark-medium-light gray as above, reversed light-medium-dark gray, a red-amber-green traffic light, and dark-medium-light blue.
Just remember, a little color goes a long way, and you don’t want the graph to obscure or outshine the data.