People have struggled for a long time to find effective means of displaying single points of data. With the recent obsession with dashboard displays of business data, this struggle has come to a head. On one side, there are myriad gauge designs; on the other are more effective charts, notably Stephen Few’s Bullet Graphs. In this tutorial I’ll show how to make vertical bullet graphs in Excel.
Gauge graphs are based on the numerous dials found in automobile dashboards and aircraft cockpits. These dials make sense in a mechanical setting: they are activated by small voltages generated by sensors in the equipment. In the gauge, this small voltage results in rotation of a needle around a pivot. These gauges are reliable and inexpensive, compared to comparable linear electromechanical gauges. These dials also make sense in a cockpit: you need to know what’s happening right now, and in general you do not need to compare the values onseveral dials.
Some business dashboard designers have missed the point. Instead of treating a dashboard as a place where a lot of information is displayed in a limited field of view, they apply the metaphor of cockpit too literally, as if the manager is driving his desk around the company. Dial gauges in a business dashboard are remarkably ineffective. They take up a lot of space to show very little information, and comparisons among several gauges are difficult to judge with any precision. They also show only a “right now” value, rather than a historical view afforded by line charts.
Yet so-called Business Intelligence dashboards are loaded with gauges. BI graphics packages are full of them, each more glossy and glittery than the next.
To address many of the problems with gauges, Stephen Few started with a simple bar chart and developed Bullet Graphs. 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. Stephen describes bullet graphs in Bullet Graphs for Not-to-Exceed Targets and has published a Bullet Graph Design Specification.
How to Make Vertical Bullet Graphs in Excel
There are a number of ways you could make a bullet graph in Excel. I’ve picked one 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%. The actual value is 70%, and the target is 85%. You don’t need to use percentages; any numerical values work just as well.
Start your bullet graph by creating a stacked column chart, with series in rows. If you get a column chart with five identically colored bars, you need to switch from “by columns” to “by rows”.
I’ve formatted the series in the chart using light, medium, and dark gray for the background bands, black for the actual value of interest, and red for the target (to keep it distinct from the black bar for the measured value). In all of these steps, the Excel 2003 chart is shown at left, the corresponding Excel 2007 chart at right.
Format the value bar so it is charted on the secondary axis. It will nearly obscure the primary axis column stack.
Change the widths of the columns by changing their gap widths. (Gap width is the width of the gap between adjacent columns, or in this case, the amount of white space beside the only column. A gap width of 100 is as wide as a single column.) Change the gap width of any of the primary axis columns (Target, Dark, Medium, Light) to a low value, 50 in this case. Change the gap width of the secondary axis column (Value) to a large value, 350 in this case.
This choice of gap widths results in a Value bar which is 1/3 the width of the background bands. If you want the bands to stretch to the edges of the chart, use a gap width of zero for the bands and 200 for the Value bar.
Change the chart type of the Target series to XY. This results in a marker floating in the chart, which I’ve colored red to continue the theme.
Delete the secondary Y axis, so that all series use a single scale.
Add error bars to the Target data point. In Excel 2003, format the series, and on the X Error Bars tab, select a constant value of 0.25. In Excel 2007, error bars are much less intuitive to use, as I’ve written about several times, including in Error Bars in Excel 2007 Charts. With the series selected, go to the Chart Tools > Layout tab, click on Error Bars, and choose More Error Bar Options at the bottom. This adds horizontal and vertical error bars with values of 1.
Format the error bars. In Excel 2007, first select and delete the vertical error bars, then select the horizontal error bars and assign a constant value of 0.25. Then in both versions, format the error bars to not show the end caps, and format the line style appropriately. I’ve used the medium weight (1.5 pt) line and colored it red, though black is very common.
Now make the Value data point invisible. In Excel 2007 that’s easy, just format it to use no marker. In 2003 you should use a square marker of size 2 in the same color as the error bars.
If you use no marker in Excel 2003 you will get a small divot in the horizontal line made by the error bars. Maybe you don’t care, but this really bugs me.
Lock in the vertical axis scale to a maximum of 100% (or the maximum value you want to show, if you’re entering regular numbers).
Clean up the bullet graph by deleting the legend and hiding the axes: no line, no tick marks, no tick labels. Of course, you could leave the vertical axis in place with its labels if you wanted to retain the scale.
Now resize the bullet graph. This is medium size, larger really than you need.
Note that in Excel 2003, you can only shrink a chart so far before the inside stops shrinking, and instead it is truncated.
You can avoid this truncation by shrinking the plot area within the chart before the chart becomes too narrow. To illustrate, this chart has its chart area and plot area outlined in light gray.
As you shrink this chart, the excess margin to the right of the plot area is truncated. The last bullet graph has had its pot and chart areas re-hidden.
Here is an Excel 2003 bullet graph which has not been truncated (below left). There is no such truncation in the Excel 2007 chart (below right), so we can shrink it almost indefinitely.
These small bullet graphs are legible despite their small size. Their formats can be modified to suit particular purposes. In the first example below, the target line has been changed from red to black. In the second example, the gray color scheme has been reversed, perhaps to indicate that higher values are less desirable. The last two show various schemes using colored fills instead of gray. Keep in mind that a very small amount of color goes a long way.
Now you know how to construct a vertical bullet graph in Excel. Horizontal bullet graphs are a little trickier, and will be covered in a future post.