An Excel Tutorial:
How to Create Bullet Graphs
To Replace Gauges in Excel
Bullet graphs present the same information that dashboard
but they're smaller and easier to read. This tutorial
shows to how to create your
graphs in Excel.
by Charley Kyd
(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
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,
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
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
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
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
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
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
From an Excel perspective, these are some of the advanced aspects of
- 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
- Several bars or columns "display" values that always must be
- They use error bars with special formatting.
These issues will become clear as you continue
through this article.
Preparing the Chart Controls
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:
Several SERIES formulas will rely on the values returned by these
ensure that the chart displays its values properly.
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
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:
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:
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
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:
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.
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
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
B. Format the Column Chart
The chart will look more like a bullet graph if we format the
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
2. In the Patterns tab, choose None for the Border. For the Area,
choose a dark color, perhaps even black. Then choose OK.
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
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:
(If your worksheet has a different name, you'll see that name rather
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:
(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:
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
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
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.
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
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
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
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