The following is a guest post written by Bill Dean. After a recent workshop, Bill shared with me his affinity for bullet graphs. I've never used one before—though can see the potential—so invited him to share his views and an example approach here. Bill leads an engineering and data science team at Microsoft that enables groups across the company to analyze and act on customer feedback at scale. He loves (and makes) both BBQ and data visualizations but hasn’t yet mixed the two. For more on Bill or to connect with him, check out LinkedIn or Twitter.

While some chart series stand on their own, it sometimes makes sense to allow them to stand alone-ish. That is, you want it to stand near some benchmark (e.g., closest competitor, last year’s performance, goals) without giving up too much of the spotlight. Enter “Bullet Graphs.” Bullet graphs were seemingly developed by Stephen Few, and provide a great way to gain information density without the cognitive load of some graph options. Bullet graphs have been around a while but are utilized much less frequently than simple 2-series bar charts or even malformed eye-candy gauges.

For example, take a typical water bill (Figure 1, below, roughly replicated from my personal bill), which does a decent job at drawing your eyes to the current year’s data via a two-series bar chart. This is actually a good starting point as I’ve seen data like this represented as two pie charts. There’s a confusion factor lurking for users to misinterpret the chart due to its two-month groupings. At first glance, one might think that January is the white bar, February the black bar, and so on in some sort of tick-tock visual joke. Instead, we’re billed two months (e.g., January-February Unit) at a time and each period is the total for combined months in each year’s data. I’m going to start with the water bill as-is, and will adjust it towards a bullet graph through a path that’s familiar…especially for Cole’s readers.

While it’s a pretty good start, the white bars representing the “previous year’s consumption” series make my eyes bleed and I might want to help it stand out less by making those bars gray and removing the border. In Figure 2, both series still have sufficient contrast ratios from the background and each other, which is good for everyone…especially “low-vision” users. It will also help to move the legend up towards the top of the chart to inform users ahead of the visual.

If the exact numbers matter, I could consider adding the data labels on the inside end of the bars. Only do this if you can ensure there’s always enough vertical bar space for a contrasting text and sufficient width to accommodate reasonably large numbers.

Figure 3: Updated Water Bill with Inside Labels

Figure 3 feels like a natural improvement over the original chart (even though I want to remove the y-axis, I’ll leave it here for continuity). I could leave this alone, but I’m going to transform it into a simple bullet graph. Next, I’d like to put the previous year’s series behind the current year’s series so the direct comparison is more obvious. I can do this by clicking once into my first data series and making it “Secondary.” Because this will move them on top of each other, I’ll want the ensure the context (previous year series) is in the background, lighter in color (it already is), and wider.

Now, my chart looks different because both the primary and secondary axis are using ranges that make sense for each series independently. I’ll need to intervene and ensure they’re both set to be the same. Do this by right-clicking on each axis and the “Edit Series” icon to set the scale (in this case it’s 0 to 3500). I can also delete the axis on the right (or both, if I add the value as a data label). Dealer’s choice, really. For additional decluttering, I’ve opted to remove the line at the base of the bars.

Figure 5: Simple Bullet Graph with Consistent Axes Range

Now, I’ve got a chart that draws my attention to this year’s trend with the context of what my family consumed last year (Figure 5). There’s also a side benefit that the two-month format no longer confusing as they’re grouped and centered under both bars. It’s just as easy to have made this a horizontal bar chart instead (often better for longer labels and non-time groups). This is how that would look (also used data labels in lieu of the axis).

Figure 6: Horizontal Simple Bullet Graph

If my water company wanted to add a little more information and a little peer pressure, they might add a more complete bullet graph with zones of guilt and a small marker that represents my local neighborhood, a goal they set for me, or (in this case) last year’s consumption for the same period).

A full series with this information would be more informative.

We can start with the data table and walk through how to do it. We’ll start with some reference ranges that will add up to the full range of the chart you want to see and will create zones in the background for context.

Table 1: Monthly Consumption Data (in percent)

Start, by highlighting the entire table in Excel and use the Insert tab to add a 100% stack chart. It will be hideous!

Excel draws the table in reverse order (the top cell value is at the bottom of each chart column with each subsequent value piled on top). This is important to know so you start with what you want in the background at the top of the table.

The blue, orange, gray sequence for each period in Figure 8 is the Conservative, High, Extremely High data from my table, respectively. You’ll want to right click the bottom series and color that a dark gray, the orange series should be a noticeable bit lighter than that, and the gray series should be a few notches lighter than the previous value. Set the outline to “None” (or make it white to have the borders pop a little).

From here, you’ll want to right-click one of the series and adjust the gap width to 40% or so to ensure the bars are nice and wide. When you hover over untouched series, you’ll see that they are the current and previous year. Let’s go ahead and make those “secondary” by right- clicking on “Change Series Chart Type.” Check the box for both Current and Previous years’ data. For the current year, we’ll change that to the “stacked column” chart while the previous year will be set to a “stacked line with markers.”

Figure 10: Stack Chart Changing Chart Type and Secondary Axes

While we’re at it, we can eliminate the chart border and the axis lines. We’re civilized, after all.

Figure 11: Stack Chart with Secondary Axes and Mixed Chart Types

Formatting the Current Year Consumption to Black and removing the ‘Previous Year’ connecting lines gets us REALLY close to the final chart.

Figure 12: Stack Chart Starting to Look Like a Bullet Graph

Here we can play with the Previous Year Consumption marker to ensure it can be seen a bit more clearly. Click on the series by right-clicking on the dots and selecting “Format Data Series” (Figure 13). Select the Marker, Built-in and click on the wide dash. The default size is 5, but it will look much better around 18.

It’s not quite done because you’ll need to ensure both axis are set to 0-100%. Do this then delete one of them (most delete the right).

On the home stretch as now you can adjust the colors, add a title, and size it appropriately to fit your dashboard, report, etc. There’s a really helpful feature that might help down the line so that you can consistently format all similar charts EXACTLY the same way. Right-click your chart and click “Save as Template,” name it, and save it.

Next time you have a set of data like this, you can start with whatever chart you want, right-click, and select “Change Series Chart Type” > “Templates” > Chart Template.

Figure 16: Bullet Graph with Title, Lighter Ranges, Less Clutter

It’s probably more likely that each month pair has a different idea of what Conservative Water Usage, High, and Extremely High Usage so I’ve made each zone differently sized in Figure 17.

Figure 17: Bullet Graph with Variable Ranges

...and with a navy bar.

...and another, even bluer option in Figure 19 (in honor of “Towel Day”).

The following GIF (Figure 20) demonstrates the process described above and should keep your cats busy, if not teach them how to make bullet graphs.

Jon Peltier also has a variety of instructions if you’d like to see a variety of ways to make bullet charts in both horizontal and vertical varieties.

Huge thanks to Bill for this thorough and informative post! You can download the Excel file that contains his graphs.

the bullet graph

read

Listen

Attend

Participate