To create a bullet chart as shown below, you should first select the data and insert a:

So Stephen Few invented an excellent replacement. He called it a bullet graph. Here’s my version in Excel:

This graph presents the same information that a gauge would. But it doesn’t suffer from the gauge’s many problems.

Because bullet graphs are such an improvement on gauges, I’ve worked to come up with a reasonable implementation for them in Excel. Like Steve’s examples, the Excel versions can be either horizontal (bar charts) or vertical (column charts), as illustrated above and below.

To give you an idea of how they might appear in actual use, this mock-up illustrates bullet graphs in an Excel report:

An Aside: “Graph” or “Chart”?

“Graph” is the more precise term, because the term “chart” can include both graphs and tables.

When Microsoft first introduced Excel, Lotus 1-2-3 dominated the market for spreadsheets. Because Excel graphs were such a significant improvement over Lotus graphs, Microsoft wanted to find a term that could differentiate the two graphing engines. So Microsoft called their graphs “charts”.

This might not be Microsoft’s official position about that choice of terminology, but this is what a senior Excel manager told me at the time.

However, as a practical matter, I find myself using “graph” and “chart” interchangeably when applied to this topic. So it doesn’t really matter.

Bullet Graphs as a Charting Exercise

From an Excel perspective, these are some of the advanced aspects of bullet graphs:

• They are combination charts, using either bars or columns, along with a scatter plot.
• They use the Primary and Secondary axes, but largely ignore the Secondary axis.
• Several bars or columns “display” values that always must be zero.
• They use error bars with special formatting.

These issues will become clear as you continue through this article.

I can think of three ways to create bullet charts.

1. Using a column or bar “sandwich,” which is the approach I describe below.
2. Using columns or bars without the sandwich.
3. Using XY plots.

Here’s method 1, which probably is the easiest method to set up:

Prepare the Chart’s Data

This figure shows the data that each graph in the bullet-graph report relies on.

To make it as easy as possible to create your first chart, change the name of a new worksheet to GG and then set up your data area as shown here. Then, after you create your chart you can rename your worksheet or move your data as you want.

The cells shaded in yellow contain data values entered in cells, and the chart below displays those numbers.

Cell B2 contains the maximum value for the “poor” range. Cell B3 contains the maximum value for the satisfactory or “OK” range. Cell B4 contains the actual value for the period. And cell B5 contains the target value.

To assign the text in column A as names for the yellow cells in column B, select the range A2:B5; choose Formulas, Defined Names, Create From Selection; then in the Create Names dialog, ensure that only Left Column is checked.

The cells shaded in green are referenced by a chart’s SERIES formula. Most of these cells contain formulas.

Here are the formulas you’ll use…

B8:  =Target
B9:  2

Cell B8 determines the Target value. Cell B9 centers the indicator for the Target value in the center of the three charts that combine to create this bullet graph. You’ll get a better idea of how this works shortly.

Cells B12 and B14 always equal zero. Cell E13 contains the Actual value, which is displayed as the black bar. The formulas in these cells are:

B12:  0% B13:  =Actual

B14:  =B12

Cells C12 and C14 contain the value for the largest unsatisfactory amount. If the actual value is greater than the largest unsatisfactory amount, cell C13 contains zero; if the actual value is less than the largest unsatisfactory amount, cell C13 fills in the gap above the black column. And if the Actual value is less than zero, cell C13 contains the same value as cell C12.

Their formulas are:

C12:  =MaxPoor C13:  =MAX(MaxPoor-MAX(0,Actual),0)

C14:  =C12

If the OK amount is greater than the unsatisfactory amount, cells D12 and D14 contain that positive difference; otherwise, they contain zero. Cell D13 contains the amount needed to fill in the gap between the top of the dark bar and the top of the OK amount. Their formulas are:

D12:  =MAX(MaxOK-C12,0) D13:  =MAX(MIN(MaxOK-MaxPoor,MaxOK-Actual),0)

D14:  =D12

We now can create the bullet graph.

Create the Column Bullet Chart

1. Begin by creating a standard stacked column chart. To do so, select the range B12:D14 as shown in the table above. In the Ribbon, choose Insert, Charts, Column, Stacked Column.

3. Select and delete the Legend, which should make your chart look something like this.

(Notice that these charts are labeled 1, 2, and 3. The value 2 entered in cell B9 in the table above refers to the middle one, category 2.)

4. Select one of the gridlines then press Ctrl+1 to launch its Format dialog. In the Line Color tab, choose No Line. Then choose Close.

(Notice how the blue bar is sandwiched between the red and green bars. This is why I refer to this as the “sandwich” method.)

6. Select the Plot Area. Often, the easiest way to do this is to click on the edge of the chart to select the Chart Area, then press your up-arrow once, which selects the Plot Area.

7. Press Ctrl+1 to launch the Format Plot Area dialog. In the Fill tab, choose Solid Fill and then select a light blue color. In the Border Color tab, choose Solid Line, and then specify a light gray color for the line. Close the the dialog.

8. Choose one of the columns and assign the color that corresponds to the bullet graph’s colors shown above. When you’re done assigning each color, your chart should look something like this.

9. Because we won’t need the X-ax labels, just select the X axis and press delete.

10. To add the Target bar, first select the edge of the chart so that no SERIES formula is visible. Then copy and paste this formula in the formula bar:

=SERIES(,GG!$B$9,GG!$B$8,4)

If your data area matches the illustration above, and if you named your worksheet GG as I mentioned, your chart should look something like this.

9. This chart displays the purple column because it automatically used the same chart type as the other types. So we need to change it to a Scatter Plot. To do so, select the purple box, choose Chart Tools, Design, Change Chart Type. In the XY (Scatter) tab, choose Scatter With Straight Lines.

After you take this step, the series will appear to disappear entirely in the chart. This is because we chose to display lines, but lines need two points, not one.

However, if you look in your formula bar you’ll still see the SERIES formula for SERIES 4. (If not, choose any Series in your chart and then press the up-arrow key until you see SERIES 4 in your formula bar.)

Also notice that Excel added vertical axis labels to the right of the chart. They indicate that when you switched to an XY chart, Excel moved the series to the Secondary axis. Because this isn’t what we want, you’ll need to move the series back to the Primary axis.

10. To move Series 4 to the Primary axis, make sure that SERIES 4 is selected. Press Ctrl+1 to launch the Format Data Series dialog. Then, in the Series Options tab, choose Primary Axis, and then choose Close. When you do so, the labels at the right will disappear.

11. We now must give the Target line something to display. To do so, we’ll use an Error Bar. To display the default error bars, first make sure that SERIES 4 is selected. Then choose Chart Tools, Layout, Analysis, Error Bars, More Error Bars Options. Your chart now will look something like this.

When you format error bars, remember that they behave somewhat like gridlines and axes. First, they all consist of both a horizontal and vertical component, which we format individually. Second, we can hide either component by selecting it and deleting it.

So here, because we don’t need the vertical error bar, you can just select and delete it.

12. To format the horizontal data bar, first select it. (If you select the center of the bar you’ll probably select the series instead. So click near one of the ends of the bar to select it.)

Press Ctrl+1 to launch the Format Error Bars dialog. And then…

• In the Horizontal Error Bars tab, choose Both; choose No Cap; and enter a Fixed Value of .6.
• In the Line Color tab, choose Solid Line and make sure its color is black.
• In the Line Style tab, enter a width of 3 points.
• Choose close

Your figure now should look something like this.

8. Now you can resize your chart to the size you want. After you do so, your chart should look something like this example.

At this point, you’ll probably want to remove the border around the chart. To do so, just select the edge of the chart, press Ctrl+1, then in the Border Color tab, choose No Line.

Create a Horizontal Bullet Graph

You’ll probably want to create a horizontal bullet graph as well. Something like this:

You can do so using about the same approach as above. But instead of starting with a stacked column chart, you’ll start with a stacked bar chart.

When you do so, however, you’ll need to make two changes to SERIES 4.

First, of course, you’ll use the vertical error bar to create your Target indicator, rather than the horizontal one.

Second, you’ll use a modified SERIES formula. Specifically, these are the two versions of the SERIES 4 formulas:

Vert:    =SERIES(,GG!$B$9,GG!$B$8,4)
Horiz:  =SERIES(,GG!$B$8,GG!$B$9,4)

Notice that the arguments for cells B8 and B9 are reversed in these two formulas. This is because in an XY plot, the first argument shown provides the X position and the next argument shown provides the Y position.

Therefore, in the vertical bullet graph, the Target Category value (2) indicates the X postion and the Target Value (27%) indicates the Y position. But in a horizontal chart, these values are reversed.