Reporting Strategies
How to Create Bullet Graphs to Replace Gauges in Excel
Bullet graphs show the same information that dashboard gauges do, but they're smaller and easier to read. Here's how to create your own bullet graphs in Excel.
by Charley Kyd, MBA Microsoft Excel MVP, 20052014
The Father of Spreadsheet Dashboard Reports

(Download
the workbook.)
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.
Some time 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, as I
set up here:
Managers... 
Charley Kyd can
personally help you to apply the Excel methods in this
article to your own organization.
Click here to learn more. 
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:
If you'd rather not go through the steps to build this chart, I offer
it as Charley's Swipe
File #62.
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 123 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" interchangably when applied to this topic. So it
doesn't really matter.
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 bulletgraph
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.
I can think of three ways to create bullet charts.
 Using a column or bar "sandwich," which is the approach
I describe below.
 Using columns or bars without the sandwich.
 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 bulletgraph 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(MaxPoorMAX(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(MaxOKC12,0)
D13: =MAX(MIN(MaxOKMaxPoor,MaxOKActual),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.
2. Because Excel applies the wrong Row/Column assignment in this
case, choose Chart Tools, Design, Switch Row/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.
5. Click on any of the columns, then press Ctrl+1 to launch the
Format Data Series dialog. In the Series Options tab, set the Gap Width to zero. Then choose OK.
Your chart now should look something like this.
(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 uparrow 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 Xax 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 uparrow 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 somthing 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.
Finally, you can
follow this link to get a
free working version of both the horizontal and vertical bullet
charts. And you can
follow this
link to get a copy of the bulletchart report.
