How to Make Horizontal Bullet Graphs in Excel

Recently I wrote a tutorial showing How to Make Vertical Bullet Graphs in Excel. Bullet charts are also useful in a horizontal arrangement, which is the topic for today.

Stephen Few developed Bullet Graphs to address the many issues with gauge charts. Bullet graphs require less space than gauges to show the same amount of information, and their linear style makes it easy to compare values on adjacent bullet graphs. Bullet graphs are described in Bullet Graphs for Not-to-Exceed Targets and Few has published a Bullet Graph Design Specification.

How to Make Horizontal Bullet Graphs in Excel

There are a number of ways you could make a bullet graph in Excel. I’ve picked a method which is not too difficult, and I’ve selected a protocol that works as written in Excel 2003 (and earlier) and in Excel 2007 (and later).

A bullet chart in its simplest form needs only a few values. There are three shaded bands in the background, usually light, medium, and dark gray, to indicate qualitative ranges of, for example, poor, fair, and good values. There is a bar that indicates the actual measured value. And there is a short line tht indicates a target (comparative) value.

The following table shows our data. The dark band goes from 0 to 50%, the medium band from 50 to 75%, and the light band from 75 to 100%. Column A shows the absolute values for the transitions between shaded bands, while column B shows the relative values, needed for stacked bar charts. The actual value is 70%, and the target is 85%. You don’t need to use percentages; any numerical values work just as well.

Unlike the vertical bullet graph, the horizontal bullet graph needs some data conditioning. To get the appearance wide background and narrow foreground bars, the horizontal bullet graph uses three sets of stacked bars. The outer two stacks show the background bars, while the middle bar shows the value, plus whatever parts of the background bars are taller than the value bar.

This table shows the added columns added to make the horizontal chart work properly.

Cells D3 and F3 link to cell B3, cells D4 and F4 link to cell B4, and cells D5 and F5 link to cell B5. Cell E2 links to cell B2, and cell E6 links to cell B6. Cells E3:E5 contain more complicated formulas:

Cell E3:   =MIN(MAX(SUM(\$B\$3:\$B3)-\$B\$2,0),\$B3)

Cell E4:   =MIN(MAX(SUM(\$B\$3:\$B4)-\$B\$2,0),\$B4)

Cell E5:   =MIN(MAX(SUM(\$B\$3:\$B5)-\$B\$2,0),\$B5)

Start by selecting C1:F6 and making a stacked bar chart. So far, so good. Here is the Excel 2007 version.

Here is the 2003 version.

Select the Target series, and from the Chart Tools > Design tab, choose Change Chart Type, and select XY (Scatter). This is the Excel 2007 chart.

On the Chart Tools > Layout tab, go to Axes > Secondary Horizontal Axis, and select Show Default Axis.

Excel 2003 automatically gives you this axis when you change a bar series to an XY series, so the above two take only one step in Excel 2003.

Now we need to adjust the source data of the Target series. When the series is selected, we see the highlighted name (green), X labels (purple) and Y Values (blue) outlines.

We need to change the source data so the orphan cell containing 0.5 becomes the Y value and the cell with 85% (the old Y value) becomes the X value.

You can change the source data by dragging and resizing the highlighting rectangles, by opening the source data dialog and changing selections there, or by changing the series formula from:

=SERIES('Bullet Graph'!\$C\$6,'Bullet Graph'!\$D\$1:\$F\$1,'Bullet Graph'!\$D\$6:\$F\$6,5)

to

=SERIES('Bullet Graph'!\$C\$6,'Bullet Graph'!\$E\$6,'Bullet Graph'!\$E\$7,5)

I like editing the formula, because I can highlight an incorrect address in the formula and select the correct range with the mouse.

Here are the Excel 2007 and Excel 2003 charts with the corrected Target series.

The right vertical axis has to be rescaled so its min and max are 0 and 1, so the Target marker (value = 0.5) is vertically centered in the chart. Here are the Excel 2007 and 2003 charts after changing the axis scale.

Now let’s fill out the chart. Set the min and max of both horizontal axes to 0 and 1. The top axis looks okay, but it’s max is automatic, and if the Target is changed to a number close to 100%, Excel will adjust the maximum. So fix the maximum at 1.

Also format any of the bar series, and change the gap width to zero (no gap)

Add error bars to the Target series. You can use the Error Bar Utility that is discussed in my post Custom Error Bars in Excel Charts, or you can follow the manual instructions below.

In Excel 2007, go to Chart Tools > Layout > Error Bars > More Error Bars Options. The Vertical Error Bars dialog opens, but at first you get the default error bars, which look like this:

In the Vertical Error Bars dialog, change the value to 0.35 and remove the end caps, change the line color to red, and choose a medium line width (I used 1.5 pt). Then select the horizontal error bars in the chart and press the Delete button.

In Excel 2003, you add error bars by formatting the series. You can add the Y error bar with the value you want (0.35), and you get the default formatting.

Remove the end caps, change the line color to red, and select the medium line thickness.

Format the Target series so it has no marker, shown below for Excel 2007 and 2003.

Now hide all of the axes. Format each, choose No Line, and set tick marks and labels to None. Stretch the plot area to fill up the chart as much as possible. Excel 2007 lets the plot area extend just about to the edges of the chart, while Excel 2003 keeps a no-mans-land border that nothing can encroach upon. These charts show the bullet graphs with their enveloping chart areas for Excel 2007 and 2003.

Now the bullet graphs can be resized to fit where they are needed. The Excel 2007 charts can be shrunk as much as you want; these two are three rows and one row high.

The Excel 2003 charts have a limit on how much they can be shrunk without problems. The chart area can be shrunk indefinitely, but the plot area stops shrinking, so it isn’t all visible as the chart area continues to shrink. This is illustrated below with visible borders on chart area and plot area.

This is as far as the Excel 2003 chart can be shrunk without truncating the plot area.

Oops! There goes part of the chart…

… and now there’s not much left.

We can address this problem by shrinking the plot area within the chart area. It seems that the plot area itself doesn’t stop shrinking, but the largest space allocated for it does.

The plot area is still visible here, where before it was already partially hidden.

But eventually we start cutting off the plot area.

You can alternately shrink the plot area and chart area until you get something that works. Here the plot area is about one row high, within a chart area that’s about twice that.

Here is a 1-row-high plot area within a chart area that is just about to start blocking it. So we’ll have to stop here. Note that we need to allow for a few points above and below the chart if we want a visible bullet chart that’s one row high.

Without borders, and with a transparent chart area (i.e., no fill color), we can get 1-row-high bullet graphs in Excel 2003.

You can use different colors for your bullet charts. In the ones below, I’ve changed the Target line from red to black. The color schemes are the same dark-medium-light gray as above, reversed light-medium-dark gray, a red-amber-green traffic light, and dark-medium-light blue.

Just remember, a little color goes a long way, and you don’t want the graph to obscure or outshine the data.

1. Jon-
here is my soulution for bullet, maybe a little sample:)
http://excelpro.blog.sohu.com/131084696.html

2. Chris says:

How would you build a dashboard containing multiple horizontal Bullet Graphs one below the other.
Just use individual aligned charts or is there an easy way to a “bullet chart panel”?

..
chris

3. Neat approach and neat formulas! totally inspiring! Thanks Jon!
Did some enhancement on multi ones, may just answer Chris’s question.
http://mephisto.blogbus.com/logs/104638965.html

4. Hubert says:

John,

I have been working on an idea using this method. There seem to be so many methods to make bullet charts in excel. I like Liu’s idea on using the error bars.

Your method creates three rows of data for every chart. The report I am designing has 17 bullet charts thus it would have 51 rows worth of information.

So I made a slight change to your design and I wanted to share it. Instead of using the “a” and “c” bars for each chart I only use it once for every chart and then make the chart series reference noncontiguous cell ranges. The Dark, Medium and Light series for the chart look something like this:

=SERIES(‘Bullet Graph’!\$C\$3,’Bullet Graph’!\$D\$1:\$F\$1,’Bullet Graph’!\$D\$3:\$F\$3,1)

I have replaced it with something like this:

=SERIES(‘Bullet Graph’!\$C\$3,(‘Bullet Graph’!\$D\$1,’Bullet Graph’!\$E\$1,’Bullet Graph’!\$D\$1),(‘Bullet Graph’!\$D\$3,’Bullet Graph’!\$E\$3,’Bullet Graph’!\$D\$3),1)

That is all. Thank you for the great blog.

5. Hubert -

Thanks for sharing your suggestion. As you say there are so many methods to make the charts, and each has so many variations.

In the old days I used to fret about using any extra cells, but in the meantime I’ve realized that extra cells don’t cost anything, and they can be stashed away on hidden sheets or in hidden rows and columns. Having all calculations available makes debugging easier. Of course, not really a problem with your usage.

However, people less familiar with charts may have problems with the discontiguous ranges used by your series. In particular, using such a range prevents highlighting of the series data ranges in the worksheet. I probably would only share something like this with an advanced user.

6. Hubert says:

Jon,

Yes there are many different ways to make these charts. Have you thought about showcasing the method that Liu showed on the the Chinese website? I think I have “decoded” how it is done even though it is all in Chinese but I think others might benefit from the information.

You are right, extra cells don’t cost anything. Also, I am sure I will be cursing Excel when I have to debug this thing. The fact that using such a range prevents highlighting of the series data ranges in the worksheet is a big deterrent.

In the end people are going to make their reports/dashboards in what ever manner best fits their skill set.

7. Lauren says:

Have y’all seen the Freeware bullet graph app from Aculocity?
http://www.aculocity.com/BulletGraph.aspx
I found it useful for my needs, and, you know, FREE.

8. Upex says:

A great article and some excellent methods. I wonder if anyone can offer me a quick solution to do the same, but with 4 and 5 areas in the backgroung. I need to build a dashboard with 3 4 and 5 area charts and have used the above for the 3s but can’t figure out the 4 and 5. Any one able to help?

9. LeonK says:

Jon, Firstly, excellent series on Bullet graphs, I went through all of your blogs on them last year when I was learning to recreate Stephen Few’s techniques – I love the use of the xy scatter to set the target marker with error bar.

I now have a question. Having only yesterday been presented with the opportunity to use a bullet chart, Is Excel 2010/13 more flexible? I don’t have access to previous editions of excel to know if this is true.

The reason I ask is because I have discovered that, as I needed to create the chart within a 10 minute window, it is now stupidly simple to create bullets of any number of columns or rows very easily from the original table, adding only additional (hidden) rows for the differing segments.

Firstly, I created the multi stacked column chart with my data and added a secondary axis column for the VALUES, adjusting the gap width to get the narrow bullet effect. I then added another data column and converted it to a scatter xy chart, adding horizontal error bars as you described above. Later, I repeated the exercise but for a stacked bar. The only difference was that I needed to reverse both primary and secondary vertical axes.

My data columns run from Jan to Dec. My rows are:
Visitor Capacity
Actual Visitors
Low (40%of Visitor Capacity) – Hidden
Med (30% of Visitor Capacity) – Hidden
High (10% of Visitor Capacity) – Hidden
Target (75% of Visitor capacity) – Hidden

Regards,

LeonK

10. Leon -

The actual question I saw in your post was “Is Excel 2010/13 more flexible?”

Compared to 2003? No.
Compared to 2007? 2010 is about the same, 2013 added some UI charting features that may help a bit.

Subscribe without commenting

1. [...] bullet charts in Excel. For example Peltier Tech has looked at this in this article called “How to make horizontal bullet graphs in Excel“. There is still quite some effort involved in creating such charts, as Excel doesn’t [...]

2. […] How to Make Horizontal Bullet Graphs in Excel and  How to Make Vertical Bullet Graphs in Excel by Jon Peltier […]

3. […] Creating bullet charts in Excel can be a bit tricky. This slidedeck lays out a step-by-step approach to creating bullet charts in Excel. The approach was first made public by Jorge Camoes. An alternative version was published in early 2011 by Jon Peltier. […]

4. […] there is a bullet chart tutorial here, but I found it pretty complicated. And one here that’s fairly straightforward but, as an […]

Write a Comment

Subscribe without commenting