Easy Bullet Charts in Excel

Here is what a normal bullet chart looks like:

BulletChartExample

There are usually areas of performance in the background (acceptable/unacceptable, in this case), a target line, and an actual bar the represents your real value. Bullet charts kick ass for showing part-to-whole relationships for single data points, especially in long lists of metrics, like dashboards.

Sure, there is a bullet chart tutorial here, but I found it pretty complicated. And one here that’s fairly straightforward but, as an Excel graph, it takes some extra steps to resize or copy the graph with the data. There’s also a plug-in that makes bullet charts for you! So why write another blog post on it? Well, I was working with these methods until two things happened (1) I upgraded to Excel 2013 and the plug-in quit working and (2) my client needing something different – and my clients’ needs come first.

My client said, “Yo, we don’t need those gray shades in the background. These metrics don’t have performance levels. You either meet the target or you don’t [Ed Note: Sound familiar??] And furthermore, we don’t need a target line. The target should be the end of the graph, so that when the actual bar hits the end of the graph, we know we have met our target.”

And you know what? That way of visualizing the bullet chart made So. Much. Sense. At least for me, in this case and several others I’ve worked with since then.

And BONUS: this method uses in-line graphing with Excel, so the graphs don’t move out of alignment or require extensive size and positioning, like the others I mentioned above. We’re using conditional formatting to make these, peeps (and it works in Excel 2010, too!).

Here’s how:

1. Copy your actual values into a column to the right of your metrics. Here we are graphing Kalamazoo’s Census facts, in comparison to Michigan’s average, which will serve as our target in this case. So I copied Kalamazoo’s column of data and pasted it again on the right. (I eventually filled these cells with a medium gray color too.) This is where the bullet charts will go.

ConditionalBullet1

2. Put your cursor in the first cell where you want the bullet chart. So here, that’ll be D3. Then click on the Conditional Formatting button in the ribbon at the top and pick New Rule. It’ll open up this dialogue box.

ConditionalBullet2By default, the highlighted drop down menu in the middle will say 2-color scale, so click the arrow and select Data Bar.

3. Adjust the settings in the dialogue box.

ConditionalBullet3Let’s walk through this bit-by-bit.

Check the box that says Show Bar Only, which will replace your copied data with just the viz.

Where it says Minimum, you’ll usually want to change the type from Automatic to Number. The Value box will populate with a 0. Generally, leave that be.

Where it says Maximum, you’ll want to point Excel to your target value, since that’s the way we are conceptualizing the bullet graph here. To do so, change the type from Automatic to Formula. In the Value box, type in the cell that contains your target value. Note that you need to have an equals sign (=) at the front and dollar signs before the letter and number ($).

Where it says Bar Appearance, you can change the fill color to match your brand. By default there will be no border but I actually gave it a gray border, which matches my cell background color, so the bar ended up looking a little skinnier.

Finally, under Bar Direction, select Left-to-Right. And click OK.

BOOM! Bullet graph! It’s embedded in the cell, so it won’t jump around on the screen like regular graphs can. It’ll copy into new spreadsheets with ease. And it’s pretty freaking easy to set up, compared to some of the other methods I’ve seen.

Optional 4. Handle negative actual values. I made up this data about Crime Rate at the bottom, but let’s pretend the rate since 2008 has decreased in Kzoo. In this situation, adjust the Minimum for the bullet graph by selecting Lowest Value.

ConditionalBullet4

Then click on this gray button at the bottom of the dialogue box that says Negative Value and Axis. It’ll bring up a new dialogue box that looks like this:

ConditionalBullet5Here is where you can change the color of the bars when they are negative (red is default but it was to bright for my branded color scheme so I chose a dark gray). Note it will make an axis line appear, too, which better visualizes that the value is negative.

From here, we can order from greatest to least (without having to reshuffle lots of graphs!), add more data and maybe other in-cell indicators like sparklines and icons, and make it ten thousand times easier for our viewers to get a sense of performance at a glance.

THE MAJOR CON, IN CASE YOU HADN’T NOTICED: when the actual value exceeds the target, we’d expect to see a blue bar that goes past the gray background but Excel has these stupid in-cell margins, so there’s always a tiny sliver of gray that shows up, such that it could look like the actual values haven’t met the target yet even when they have. You’ll see this in rows 6 and 10, as examples. That makes this visualization method a little crude but again, it might be the best solution for your client’s circumstances.

Now, this post accidentally published last week and in the 45 minutes it was available for viewing, my friend Jon Schwabish jumped on it, saying these can’t be called bullet charts. He pointed out that Stephen Few, inventor of bullet charts, said they must have shaded areas of performance in the background. Well, I say maybe the definition of a fairly new method of visualizing should be broadened. Or maybe they don’t need to be called bullet charts at all. That’s okay with me too. My point, no matter what we call it, is to visualize data so it is more useful.

I’m talking a lot about dataviz these days. See if I’m coming into your orbit in my Upcoming Events and make sure we high five in-person!

Posted in charts, Communicating Findings, dashboard Tagged with: , , , , , ,
8 comments on “Easy Bullet Charts in Excel
  1. Nic Bookman says:

    These are super easy! I usually represent the bars as a percentage of completion (actual/target), then set the maximum at 100%. I think of these as “sparkbars.” One option to counterbalance the cell margin issue would be to add a column with icon sets, such that a green dot appears when the value reaches 100%, indicating the goal was met. Another option would be to add another rule for a graded color set, with the minimum and maximum values both set at 100% (or whatever target). Then set the minimum color to white (or whatever the cell background is) and the maximum to whatever branded color fits. This essentially turns that cell margin into a highlight.

    In the past I’ve used the former solution when tracking calories. The sparkbar indicated how many of my daily calories I had consumed, and the icon set indicated if I exceeded my target (e.g., yellow for a little slip-up, red for ultimate pizza party smackdown).

  2. Stephanie, I think I (maybe?) solved part of the cell border issue.

    I went to format cells and added a border to the right and left sides of column D cells that is a white line. Now, my 100% (and 100+ %) bars both start at 0 and end at the end of the colored background.

    Thanks so much for the great tutorial! – Susan

  3. Dave Shellard says:

    Thank you, Stephanie. I was just thinking of using Bullet Charts for a project and this is a good interpretation.

    On cell margins, would turning off grid lines on the worksheet help?

    Dave

  4. I didn’t know about this function in Excel (thank you thank you!), and got to playing with it last night. I added another column, E, equal to B-C (thus the amount by which the indicator exceeded its target. Then I repeated the process you outlined, using a different color for the bar, without setting anything for negative values. This column then was blank for negative values and had a bar representing the proportion by which the indicator exceeded its target. This new column could then be hidden or shown depending on whether you wanted a more traditional bullet chart (although with only two levels – underperformance and overperformance) or simply an indicator of whether or not the goal was met.

    One question though, did you repeat the process for each cell (which would take a while for lots of indicators!)? The conditional formatting wouldn’t accept relative values and thus cutting and pasting didn’t work in any form for me to fill the conditional formatting to other cells.

    Thanks for any guidance,

    p.s. I think that your example might have something going funky on the row with the persons per household (wouldn’t the bar be 2/3 of the way across?).

    • Stephanie Evergreen says:

      Correct, you have to set it up for each cell. Still faster than making a billion tiny bar graphs.

      • Nic Bookman says:

        Can’t this be solved by setting the value of cell D3 to =B3/C3 and then copying down the column? You then set the data bar minimum and maximum as 0% and 100%, respectively.

        • Anna Roche says:

          I have been experimenting and, rather than setting it up for each cell, format painter works. Just double click on the format painter icon – you will get a little paint brush icon and click on the cells where you want the bar.

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Give me your email address (I'll protect it, I promise) and I'll send you the latest updates by email.

In Print!

My book is out! My book is out!

Presenting Data Effectively

Published by Sage, October 2013


I coedited something!

New Directions for Evaluation

Data Visualization Part 1

Released September 20


Out now!

New Directions for Evaluation

Data Visualization Part 2

Released December 21, 2013

We Tweet!

Evergreen Evaluation

Looking for Stephanie’s program evaluation services? Visit the sister company, Evergreen Evaluation