August 21

# Another way to do bullet graphs in Excel

Those of you who know me, know Iâ€™m a huge fan of Stephen Few. (I mean, I had him autograph my copy of Information Dashboard Design!) Few is highly regarded as an expert on data visualization and dashboard design. He is the original designer of bullet graphs.

Bullet graphs are not native to Excelâ€™s chart library. If you want to create a bullet graph, be prepared for some finagling with chart features, layouts, and color. Several Excel blogs have articles about creating bullet graphs. Hereâ€™s a list of my favorites:

How to Create Bullet Graphs To Replace Gauges in Excel by Charley Kyd

Excel Bullet Graphs by Chandoo

Bullet Graphs for Excel: A Simple Way? by Matt Grams

In addition, Michael Alexanderâ€™s book Excel 2007 Dashboards & Reports for Dummies has several great suggestions regarding bullet graphs. And you might also consider Sparklines in Excel, a free tool that can create bullet graphs (and much more) for you automatically. Finally, if you havenâ€™t attempted making these graphs on your own before, Iâ€™d start with Jon Peltierâ€™s tutorials. In my opinion, his are the best. Or, alternatively, you could just wait for my book to come out. (Great idea!) But I donâ€™t blame you if you donâ€™t feel like waiting.

### My solution

Iâ€™m ready to submit my own solution to creating bullet graphs in Excel. Iâ€™ll tell you now itâ€™s an imperfect solution â€“ and there are tradeoffs depending upon what you want to display. Youâ€™ll see what I mean in a moment. Basically, my version is a combination of Excelâ€™s Sparklines features and in-cell bar charts.

Hereâ€™s how it works. First, you create a series of numbers like Iâ€™ve done in the graphic below. These numbers will help form the shaded part of my bullet chart. The basic rule for now is that you need three different groups of three different numbers.

Next, select the entire range and insert a new Sparkline Column Chart (in the Sparklines Group on the Insert tab). Choose a location off to the side in which to place your new chart to take advantage of the full length cell. You should have something that looks like the image below.

If white spaces appear between the columns in your cell, adjust the cellâ€™s column width to a smaller size until the white spaces disappear.

Next, click on the cell so that the Design tab appears in the tab menu at the top. Check both the High Point and Low Point checkboxes in the Show group. Now, staying on the Design tab, select Marker Color (in the Style Group), pull up the High Point color selector and choose a dark shade of green. Click Marker Color again and this time select the Low Point color and choose a very light shade of green. Finally, click Sparkline Color and select a middle-level shade of green. You now see something like this:

From here, weâ€™ll do some axes adjustments. From the Design tab, press the Axis dropdown and select custom value for the minimum value. Type 0 and press ENTER. Now, again, select a custom value but this time click on the maximum value. Type 1 and press ENTER. You should now see a graph like this:

OK. With our shaded regions defined, weâ€™re now ready to do some experimentation.

For a simple bar, we can use the olâ€™ pipe symbol repetition method, like this. One cool feature of the pipe symbol method is that I can make the bar go in the other direction by changing the alignment from left-aligned to right-aligned. Make sure to try it out in the enclosed example file.

We could also use various symbols as well (as a variation on the on the pipe symbol method). See this article by Jon Peltier for ideas.

If we wanted to insert a target into the chart, we could also insert a line shape like this (now with new colors!):

Iâ€™m not a huge fan of this method because itâ€™s not easy to automate. If you have a series of bullet charts all of which have a target of 100%, the line isnâ€™t such a bad idea. You could draw a straight line down across the serie
s of them. But the pipe symbol repetition and shaded regions are all easily defined and changed with formulas. For example, I went back to our original data range and replaced several threeâ€™s with twoâ€™s to increase the range of the middle region.

One way to make the target-line automated is to create the line out of the Sparkline columns. If I go back to my original data set, I can change it to look like this:

Note that Iâ€™ve inserted a one into the series of twos and left the remainder blank. (You donâ€™t need the red font, I just wanted to draw your attention to the difference.) My graph will now look like this:

But if I change the Low Pointâ€™s marker color to black, I can make it look like this:

If I move that one in the range, I can make it look like this:

Obviously, I lose the final shaded region with this method. But that might not be a big deal in the end. With some scales and design, the last shade is still visually implied:

One last alternative (see edit!), also not really a favorite, is to use the the pipe symbol for the target and setting the font to a strikethrough style, like this:

I donâ€™t like this method as much because the thinness of the strikethrough line bothers me. But of all the methods presented so far, strikethrough doesnâ€™t require I give up any regions. Also, note, the last region hasnâ€™t been shaded but the region still feels somewhat there.

### EDIT!

I donâ€™t know what I was thinking when I posted the strikethrough method. If you use strikethrough, you wonâ€™t ever get to a point where thereâ€™s distance between your performance measure and your target. Iâ€™ve kept it in the in the example file so you can see what I mean. However, you can still get something similar to the strikethrough method by using the dash (â€œ-â€œ) character with the pipe symbol instead.

### Thoughts

I really havenâ€™t said anything about scaling â€“ and there are scaling issues! Youâ€™ll have to play around with your REPT() formula to figure out how many pipes (or other desired character) can fit in the cell. Youâ€™ll then have to figure how much one character unit represents. Finally, youâ€™ll have to ensure your character units align correctly with your Sparkline Column units. For now, I leave that up to you. In the future, I plan to have more applied examples. Until then, you can download this file of examples:

In Cell Chart Examples.xlsx

As a bonus, Iâ€™ve included this in-cell box and whisker chart:

One last, final point. Iâ€™d love to hear your feedback. I still have yet to play around with these charts in full, so Iâ€™m not entirely convinced of their usefulness. If you hate them, let me know. If you love them, let me know. Or, if you have other ideas for creating in cell charts using Excel Sparklines, share â€˜em!

Also, Stephen Few recently released a new edition of Information Dashboard Design: Displaying Data for At-a-Glance Monitoring, which I highly recommend.

Latest posts by Jordan Goldmeier (see all)

Tags

#### You may also like

February 26, 2015

## What Year Were Sparklines Added to Excel? â€“ Excel Challenges

What Year Were Sparklines Added to Excel? – Excel Challenges

September 9, 2014

## Info Graphics with Excel

Info Graphics with Excel

February 12, 2016

## Fonts That Make Bars Look Filled & Complete â€“ Excel Challenges

Fonts That Make Bars Look Filled & Complete – Excel Challenges

August 28, 2012

## Details on Demand: Bring up details on a graph with Excel rollovers

Details on Demand: Bring up details on a graph with Excel rollovers

August 21, 2013

## Another way to do bullet graphs in Excel

Another way to do bullet graphs in Excel

June 19, 2012

## Rollovers for Gantt Charts

Rollovers for Gantt Charts