Windows & Mac Excel
Charley's Swipe File #62
These bullet graphs show the same information that dashboard gauges do, but they're smaller and easier to read.
Charley Kyd, MBA
Microsoft Excel MVP, 2005-2014
The Father of Spreadsheet Dashboard Reports
Stephen Few is the well-known author of Information Dashboard Design, Show Me the Numbers, and other books about
the visualization of business data.
Several years ago, he invented bullet graphs as a replacement
for gauges. Not only are bullet graphs easier to read than
gauges, but they take up less space and are easier to create in
Although Few calls them "graphs" Excel users typically call them
After I created this Bullet Chart report I experimented with its
colors. Because I couldn't decide which version I liked best I
decided to include them both.
In all charts, the center bar shows actual performance; the
heavy black line shows the target; and the dark, light, and
lighter colors indicate poor, acceptable, and good performance,
If you’re like most Excel users, you’ve never used an error bar
in a chart. One reason for this is that the name “error bars”
implies that you should use them for doing something with errors
in charts…but you’ve never had that particular need.
Here, however, I use error bars to generate the heavy black
target line in each chart. To see how it’s done, click on one of
those error-bar lines, then choose Chart Tools, Layout,
Analysis, Error Bars, More Error Bars Options. Then, in the
Format Error Bars dialog, you’ll be able to see the settings I
How to Make Changes
Each chart in these two reports stands by itself. That is, each
chart figure gets its data from a different Figure Data Support
In these reports, each FDS sheet contains cells with data
entered manually. But in actual use, you probably would use
INDEX-MATCH (preferred) or VLOOKUP formulas to return data from
another data source. And to perform the lookups, the formulas
typically would rely on the value for the current period, which
the Control sheet contains.
Therefore, it’s easy to use these reports to add bullet charts
to your existing reports. Here’s how…
First, select the bullet chart you want to copy to your own
report. Then click on one of the series in that chart. For
example, if you click on the black bar in the top-left chart,
you’ll see the formula…
…which tells you the chart gets its data from Figure Data
Support sheet AA.
Second, copy that FDS sheet to your report. An easy way to do so
is to select the FDS sheet, hold down your Ctrl key, then click
and drag that sheet’s tab to your report.
Finally, click on the edge of the chart to select it, copy the
chart, and paste it into your report. When you do so, the chart
will get its data from the FDS sheet you copied in the previous
(Continued in the documentation.)
For Charley's Swipe Files #1
Excel 97 and
after, for either Windows or Mac
For Charley's Swipe Files
#51 and after:
Excel 2007 and after, for either Windows or Mac
Instant download of zipped Excel and
PDF files. If you ever need
another download, you can get it at any time.
One full year, unconditional.
All prices are in US Dollar currency.
Two copies: One for work plus one