Home >  Exploring Excel  > 

An Excel Tutorial:

How to Create Bullet Graphs
To Replace Gauges in Excel

Bullet graphs present the same information that dashboard
gauges do, but they're smaller and easier to read. This tutorial
shows to how to create your own bullet graphs in Excel.

by Charley Kyd
July, 2006

(Update: Several people have pointed out that when actual values are negative, the original bullet graph gave distorted results. One simple change in the formula for cell F4 below corrects this problem. This article now shows the correct formula.) 

Business presentations desperately need a replacement for dashboard gauges. Gauges consume too much space in a report. They use excessive "chart junk." And Excel does them badly.

Stephen Few has invented an excellent replacement.

Several months ago I reviewed Steve's two recent books, Information Dashboard Design and Show Me the Numbers. In his Design book, Steve described his bullet graph, shown here:

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 the bullet graphs in Steve's book, 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 mockup illustrates bullet graphs in an Excel report.

Also, if you'd rather not go through the process to build this chart, you can download several working examples.
 

An Aside: "Graph" or "Chart"?

Microsoft calls these displays "charts" and Steve calls them "graphs". Which is correct?

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

For this article, I'll use "bullet graph" to refer to the specific type of Excel chart that we're creating. That is, I'll explain how to create bullet-graph charts.

Bullet Graphs as a Charting Exercise

Creating an Excel bullet graph is a moderately advanced exercise in Excel charting. Depending on your perspective, this could be either good or bad.

It's good if you want to learn more about Excel charting. It's bad if you just want to have a bullet graph to use as a template for your own work. If you would rather not create these charts from scratch, you can follow this link to order an Excel bullet-graph template.

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.
 

Preparing the Chart Controls

This figure shows the data that the bullet graph relies on.

The numbers shaded in gray are returned by formulas, or they display values that shouldn't be changed.

The four numbers that aren't shaded are values. In practice, their cells typically would contain formulas linked to a spreadsheet or OLAP database.

This chart displays the four numbers.

Cells B2 through B5 contain the four values. 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.

After you enter the labels in the range A2:A7, assign the labels as names for the corresponding cells in column B. To do so, select the range A2:B7. Choose Insert, Name, Create. In the Insert Names dialog, ensure that only Left Column is checked. Then choose OK.

Enter the following formulas shown in the figure:

B6:  =MAX(B2:B5)
B7:  =MIN(B2:B5)

Several SERIES formulas will rely on the values returned by these formulas to ensure that the chart displays its values properly.
 

Preparing the Chart Data

Both the bar chart above, and the column chart shown here refer to the data in the shaded area that begins in column E. The green shaded areas in row 2 label the data areas used by the chart columns and bars.

Cells E3 and E5 always equal zero. Cell E4 contains the Actual value, which is displayed as the dark green bar. The formulas in these cells are:

E3:  0%
E4:  =Actual
E5:  =E3

Cells F3 and F5 contain the value for the largest unsatisfactory amount. If the actual value is greater than the largest unsatisfactory amount, cell F4 contains zero; if the actual value is less than the largest unsatisfactory amount, cell F4 fills in the gap above the dark column. And if the Actual value is less than zero, cell F4 contains the same value as cell F3.

Their formulas are:

F3:  =MaxPoor
F4:  =MAX(MaxPoor-MAX(0,Actual),0)
F5:  =F3

If the OK amount is greater than the unsatisfactory amount, cells G3 and G5 contain that positive difference; otherwise, they contain zero. Cell G4 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:

G3:  =MAX(MaxOK-F3,0)
G4:  =MAX(MIN(MaxOK-MaxPoor,MaxOK-Actual),0)
G5:  =G3

Cells H3 and H5 contain values that are at least as large as the Target value, but not less than zero. Cell H4 always equals zero. Their formulas are:

H3:  =MAX(Target-SUM(F3:G3),0)
H4:  0
H5:  =H3

Cells F7 and G7 contain data for the Target bar. Cell F7 plots the bar vertically in the figure at the right; cell G7 positions the bar horizontally. Their formulas are:

F7:  =Target
G7:  2

We now can create the bullet graph.
 

A. Create the Column Chart

Begin by creating a standard stacked column chart.

1. Select the range E3:H5 in the table. Click on the Chart Wizard button in your toolbar or choose Insert, Chart.

2. In the Standard Types tab, choose the Column chart type and the Stacked Column chart sub-type. Then choose Next.

3. In the Data Range tab, specify that the Series is in Columns by choosing the Columns radio button. Choose Next.

4. In the Gridlines tab, uncheck the gridlines. And in the Legend tab, uncheck Show Legend. Then choose Finish.

5. Click on any of the columns, then choose Format, Selected Data Series.

6. In the Options tab, set the Gap Width to zero. Then choose OK.

7. With the Chart Area active, choose Format, Selected Chart Area. In the bottom left corner of the Font tab, uncheck Auto Scale, then choose OK.

8. With the Chart Area still active, click and drag one of the selection handles to make the chart narrower. At this point, your chart should look something like the figure. However, your colors may be different.
 

B. Format the Column Chart

The chart will look more like a bullet graph if we format the columns.

1. Select the bar that's red in the figure. We need to remove its borders and change its color to a dark color. To begin, choose Format, Selected Data Series.

2. In the Patterns tab, choose None for the Border. For the Area, choose a dark color, perhaps even black. Then choose OK.

3. Select one of the bright green bars, as shown in the figure above. Do the same thing that you did for the red column, with one exception. Choose a medium dark color, perhaps gray. Then choose OK.

4. Select one of the blue bars. Do the same thing again, but choose a medium-light shade for the pattern.

5. Select one of the yellow bars. Do the same thing again, but choose a light shade for the pattern.

6. Select the Plot Area. Choose Format, Selected Plot Area. Turn off the borders and assign the same light shade to its area.

Your chart now should look something like the figure at the right.
 

C. Add the Target Marker Data Series

Now it's time to add the data series that returns the target marker.

1. The easiest way to add this data series is to use the menu. Select the chart and choose Chart, Source Data. In the Series tab, choose Add. This generates a Series 5 item.

2. Select Series 5. Enter the Name Target. Click inside the the Values edit box, then select cell F7, which specifies the value that the chart must display. Then choose OK.

3. At this point your chart will display a column that looks very much out of place, as shown here. Select the new column. Choose Chart, Chart Type. Select the Scatter chart type, then choose OK.

4. Select the new data series. If you don't see it in your chart, select one of the column data series then press the Up arrow on your keyboard until series 5, the Target data series, is active. Its formula should look something like this:

=SERIES("Target",,Sheet1!$F$6,5)

(If your worksheet has a different name, you'll see that name rather than "Sheet1".

6. You need to edit the series formula so that you also specify the horizontal position of the point. Therefore, edit the SERIES formula so that it picks up the value of 2 in cell G6:

=SERIES("Target",Sheet1!$G$7,Sheet1!$F$7,5)

(Again, you might have a different sheet name.)

7. When you added the scatter plot, Excel automatically moved it to the Secondary axis. You need to modify the Secondary X axis so that its values are the same as those for the Primary X axis. This will ensure that the Target marker is centered horizontally in the chart. To display that axis, first make sure your chart is selected. Then choose Chart, Chart Options. In the Axes tab, in the Secondary Axis section, check the Value (X) Axis checkbox.

8. Select the Secondary Value (X) Axis. (The one at the top.) Choose Format, Selected Axis. In the Scale tab, enter these values:

Minimum:   1
Maximum:  3
Major unit: 1

9. Now you need to format the Target marker. Select the Target data series again. Choose Format, Selected Data Series. In the Patterns tab, choose None for the Marker. In the X Error Bars tab, choose to display Both sides of the error bar, and specify a Fixed Value of 0.6. (Specify a larger value, 0.8 for example, if you want the Error Bar to be wider.) Then choose OK.

10. To format the Error Bar, first select it. (After you do so, the Name Box at the far left of your formula bar will display the text: "Target" Error Bars.) Choose Format, Selected Error Bars. In the Patterns tab, select the Marker without the cross bar. Select the maximum Line Weight. Then choose OK.

11. To complete this stage, turn off the Secondary axes. To do so, first activate the chart. Choose Chart, Chart Options. In the Axes tab, deselect both checkboxes in the Secondary section.

At this point, your chart should look something like the one at the right.
 

D. Test Your Scaling

Remember that Excel automatically assigned the Target series to the Secondary axis. We therefore rely on Excel to display the Target value correctly against the labels for the Primary axis, which are the only ones we see.

Therefore, it's a good idea to test that the chart is working correctly.

To do so, enter a variety of values in the range B2:B5. Make sure that your chart displays each of those four values correctly.

If your test fails, review the steps described above.
 

E. Complete the Chart

Now we merely need to clean up some formatting.

1. To remove the border around the Plot Area, first select the Plot Area. Choose Format, Selected Plot Area. In the Patterns tab, ensure that Border is set to None. Choose OK.

2. To hide the labels in the X axis, first select it. Then choose Format, Selected Axis. In the Patterns tab, set the three Tick Mark options to none. Leave the Lines option set to Automatic.

3. The bullet graph is wider than we want it to be. However, as we make the Chart Area narrower, we also reduce the space available for the Y axis labels. This forces them to wrap, producing an ugly result.

To get around this problem, first set your Chart Area about the size of the first figure below. Then select your Plot Area, as shown the figure. Click on the selection handle by the 15% label, and drag it to the right. When you do so, you'll see the dashed lines displayed in the second figure.

The horizontal distance between the first and second dashed lines is the space available for your labels. You'll see that this space stays the same as you drag your Plot Area to the right.

4. Stop dragging when the horizontal distance between the second and third dashed lines is about the width of the Plot Area in the third figure above.

5. To remove the outer border, first select the Chart Area. Choose Format, Selected Chart Area. In the Patterns tab, set the Border and Area settings to None. Choose OK.

6. To complete the formatting, you should lighten the border for each axis, because the dark lines are too distracting. To lighten them, first select the Value (Y) Axis. Chose Format, Selected Axis. In the Patterns tab, choose a medium-gray color for the Line. Choose OK. Press the right arrow once to select the Category (X) Axis, and then repeat the process. Your chart then will look something like the fourth figure above.

Next Steps

If you like the vertical bullet chart, you'll probably want to create a horizontal version as well. 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 add bullet charts to your reports, I recommend that you maintain one data sheet to support each chart. Although you could wind up with many data sheets, I think you'll find them easier to maintain.

Finally, you can follow this link to get a working version of these figures.

(Email Comments)