How to Make Vertical Bullet Graphs in Excel

small bullet chart format variationsPeople 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.

Gauges

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.

Bullet Graphs

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.

data for bullet chart

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.

Excel 2003 stacked bar chart – – Excel 2007 stacked bar chart

Format the value bar so it is charted on the secondary axis. It will nearly obscure the primary axis column stack.

Excel 2003 primary and secondary stacked bar chart – – Excel 2007 primary and secondary stacked bar chart

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.

Excel 2003 primary and secondary stacked bar chart – – Excel 2007 primary and secondary stacked bar chart

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.

Excel 2003 stacked bar chart and XY chart – – Excel 2007 stacked bar chart and XY chart

Delete the secondary Y axis, so that all series use a single scale.

Excel 2003 stacked bar chart and XY chart with one axis scale – – Excel 2007 stacked bar chart and XY chart with one axis 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.

Excel 2003 stacked bar chart and XY chart with error bar – – Excel 2007 stacked bar chart and XY chart with error bar

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.

Excel 2003 stacked bar chart and XY chart with formatted error bar – – Excel 2007 stacked bar chart and XY chart with formatted error bar

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.

Excel 2003 stacked bar chart and XY chart with formatted error bar – – Excel 2007 stacked bar chart and XY chart with formatted error bar

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.

gap in middle of target line

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).

Excel 2003 bullet chart with visible 100% axis – – Excel 2007 bullet chart with visible 100% axis

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.

Excel 2003 full-size bullet chart – – Excel 2007 full-size bullet chart

Now resize the bullet graph. This is medium size, larger really than you need.

Excel 2003 medium size bullet chart – – Excel 2007 medium size bullet chart

Note that in Excel 2003, you can only shrink a chart so far before the inside stops shrinking, and instead it is truncated.

truncated Excel 2003 bullet chart

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.

narrow plot area within the bullet graph

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.

Excel 2003 bullet chart – – Excel 2003 bullet chart – – Excel 2003 bullet chart – – Excel 2003 bullet chart


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.

Excel 2003 small bullet chartExcel 2007 small bullet chart

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.

small bullet chart format variationssmall bullet chart format variationssmall bullet chart format variationssmall bullet chart format variations

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.

Peltier Tech Chart Utility

Comments

  1. Very clear, Jon, thanks for your efforts

  2. Jon – Thanks for the tip. I tried to flip the vertical chart to horizontal. However, , when trying to make the target data series an XY-scatter type value, Excel 2007 returned an error stating that some chart types can not be combined.

    Do you know of any work-around to make these bullet charts horizontal?

    Thanks, Wade

  3. Wade –

    I’m working on that tutorial now. It was supposed to be done today, but these clients won’t leave me alone.

  4. Great stuff. If you have several of these graphs, is there a way to make them all the same size?

  5. Andrew –

    The most reliable way to make the charts the same size is to make one chart, copy it, and change the data links in the copied chart.

  6. Thanks John, great tutorial. Took all the hard work away

  7. Is there a way to create a Horizontal Bullet Graphs in Excel, the image paste solution available is not dynamic and when one uses the bar chart option, the error lines does not come along the x axis but comes along the y axis and there does not seemed to be a method to swap this

    cheers

  8. Ashtwinks –

    That tutorial is in draft form. Excel 2007 ate the workbook I was using before I could export all the charts, and I haven’t gotten back to it yet.

  9. Impressive. Thanks Jon.

  10. The book “Excel Dashboards and Reports” by Michael Alexander and John Walkenbach shows a really cool trick for making horizontal bullet graphs. I quote:

    1. Create a vertical bullet graph.
    2. Change the alignment for the axis and the other labels on the bullet graph so that they’re rotated 270 degrees.
    3. Use the Excel camera tool to take a picture of the bullet graph. Rotate the picture to make it horizontal.

    I also await Jon’s method. He is a genius, and it would be good to know his suggestions.

  11. I am trying to apply this concept to nest series within series in a column chart. It obviously works with 2 series by plotting them against 2 axes, but unable to figure out how to extend this to >2 series. The link below is to a dummy file showing what I mean – no macros -(the columns are text boxes formatted to convey the concept).

    Any ideas anyone?

    http://www.keepandshare.com/doc/view.php?id=2319996&da=y

  12. Thanks for the concise, well-illustrated instructions for bullet charts. I had created some previously with other instructions but they were far more of a struggle than they should have been.

    Looking forward to the horizontal bullet chart instructions.

  13. Muchas gracias por la contribución, me sirvio bastante

  14. Thank you VERY much, this solved my “gauge” request from a manager.

  15. Really a very useful post, Thanks for share Jon! I really appreciate your efforts….

  16. thanks a lot Jon, it’s a fantastic and useful for me..

Subscribe without commenting

Trackbacks

  1. [...] This post was mentioned on Twitter by Jay, Excel Insider. Excel Insider said: How to Make Vertical Bullet Graphs in Excel: People have struggled for a long time to find effective means of disp… http://bit.ly/bYfkzz [...]

  2. [...] Peltier always has some new and awe-inspiring chart trick to show us. In How to Make Vertical Bullet Graphs in Excel, he details the steps for creating these charts that take very little room, but are packed with [...]

  3. […] to Make Horizontal Bullet Graphs in Excel and  How to Make Vertical Bullet Graphs in Excel by Jon […]

Write a Comment

I welcome comments from my readers. If you have an opinion on this post, if you have a question, or if there is anything to add, I want to hear from you. Whether you agree or disagree, please join the discussion. If you want to include an image in your comment, post it on your own site or on one of the many free image sharing sites, and include a link in your comment. I'll download your image and insert the necessary html to display the image inline.

Subscribe without commenting

Peltier Tech Chart Utility

 

Create Excel dashboards quickly with Plug-N-Play reports.

 

Site by: Dawud Miracle, Business Coach & WordPress Websites