ExcelUser logo Free guides and templates

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 is a Microsoft Excel MVP by Charley Kyd, MBA
Microsoft Excel MVP

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

Although Few calls them "graphs" Excel users typically call them "charts."

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, respectively.

Excel Issues

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

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 (FDS) sheet.

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…

=SERIES(,,AA!BarAct,1)

…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 step.

(Continued in the documentation.)

Order Details

Excel Versions:

For Charley's Swipe Files #1 through #50:

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

Availability:

Instant download of zipped Excel and PDF files. If you ever need another download, you can get it at any time.

Guarantee:

One full year, unconditional.

Currency:

All prices are in US Dollar currency.

Licensing:

Two copies: One for work plus one for home.

Credit Cards:

Credit Cards
 

Only $16.95

New Excel