ExcelUser logo Learn to create Excel dashboards
Work In Progress...After nearly ten years, I'm redesigning ExcelUser.com. This is the new design. You can learn more here. Also, if you find something wrong with the site, please tell me about the problems. And thanks for your patience.--Charley Kyd

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.


Charley Kyd is a Microsoft Excel MVP by Charley Kyd, MBA
Microsoft Excel MVP

(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:

Excel bullet chart explanation

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:

An Excel bullet-chart report

If you'd rather not go through the steps to build this chart, I offer it as Charley's Swipe File #62.
 

A vertical Excel bullet graphAn 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.

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

I can think of three ways to create bullet charts.

  1. Using a column or bar "sandwich," which is the approach I describe below.
  2. Using columns or bars without the sandwich.
  3. Using XY plots.

Here's method 1, which probably is the easiest method to set up:

Prepare the Chart's Data

Data to support the Excel bullet graph

This figure shows the data that each graph in the bullet-graph 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(MaxPoor-MAX(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(MaxOK-C12,0)
D13:  =MAX(MIN(MaxOK-MaxPoor,MaxOK-Actual),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.

Creating an Excel bullet chart, step 12. 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.

Creating an Excel bullet chart, step 25. 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 up-arrow 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.

Creating an Excel bullet chart, step 3

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 X-ax 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)

Creating an Excel bullet chart, step 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.

Creating an Excel bullet chart, step 5

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 up-arrow 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.

Creating an Excel bullet chart, step 6

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

Creating an Excel bullet chart, step 7

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.

Creating an Excel bullet chart, step 8

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:

Horizontal Excel bullet chart

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.

Data for the Excel bullet graph

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 bullet-chart report.

 

Click to see more testimonials

"Our company’s President, CEO and Board members were blown away by the format and summarized information."  Ron Barrett

"We love your e-book and have deployed a good number of dashboard using it !!! Our Clients think we are gods..."  John Clark

Click to see more users.


IncSight DB Excel Dashboards


Charley's SwipeFile charts


IncSight Colors