Stephen Few has long proposed his bullet chart as a replacement for gauge-type reports. Steve argues that bullet charts are more efficient (take up less space) and encode more and more accurate information than a gauge or speedometer type chart. I’m still unconvinced, especially since I’m not a fan of a visualization that requires retraining of the end consumer. I’d much rather use something that the end consumer ‘gets’ right away.

That being said, I haven’t written a visualization post in a while and just because I’m not convinced on bullet charts doesn’t mean that you aren’t and that you might not be looking for a way to create them in Excel. My version is a combo-chart using both a scatter plot and stacked horizontal bars so the order in which we add data and chart elements is important.

BTW, I am going to assume that you have a passing familiarity with Excel and its charting utilities so don’t be surprised when I outline the steps to take (insert a scatter plot) rather than give detailed instructions (select data in cells A1:B2, go to ‘Insert’, click on the Scatter Plot drop down and select the scatter line with no point markers).

Step 1: In this first step, we will add the data for the scatter plot and create the default scatter plot graph. In this example, we are using the scatter plot to set a goal line for the overall bullet chart.

So, when we create the initial scatter plot, you’ll see that the x-coordinates are ‘80′ - this is my target. In this case, I happen to be charting the performance of the Homepage Retention Rate (1-Abandonment Rate) and I am shooting for 80%. I happen to be using whole numbers for simplicity’s sake but it could just as easily be percentages. Don’t worry about the vertical alignment of the line just yet, we can adjust it - if needed - later when we are closer to the completed graph. Notice that I haven’t removed any of the chart junk yet either, that will happen in a later step.

Step 2: Now we need to add the data for the horizontal stacks and the stacks themselves. We will have 3 bars (Top, Middle, Bottom) with 4 sections each.

When working with combination charts, adding the new data points and chart types can be tricky. In the screen shot to the left, we’re looking at the completed process, I’ve excluded the intermediary step of what the data looks like when we first add it.

In order to add the stack data to chart we first select the data from all four of the stack columns and copy it. Then we select the chart and use ‘Paste Special’ to paste the data as ‘New Series’ with ‘Values ‘Y’ in columns’. This will paste our stack data in as new scatters. We then select each of the stack series and change it’s chart type to ’stacked bar’.

Ok, now that we’ve talked about how to add the stacked data to the chart, let’s talk about the data itself. In the top row, the first data point is ‘50′ - this is our floor (minimum acceptable value). The second data point for the top row is always zero. We need this second stack element for visual handling on the center stack (see the little green block in the center bar) when it is less than our minimum. Oh, I’m also not going into excruciating detail on the formulae used in the data set, they are fairly straight forward and you can take a look at them when you download the spreadsheet at the end of this post.

Step 3: This is easy, set the gap width on the bars to ‘0′ so that they are slammed up against each other.

Step 4: Time to take out the chart junk.

Remove the vertical axes, and the secondary horizontal axis.

Remove the horizontal lines.

Remove the legend.

Remove the plot edge lines

Set the scaling on the primary horizontal axis, set the axis-line to none as well as the major tick marks to none.

Step 5: Apply consistent and valuable color-coding to chart elements.

The blue stack becomes our representation of ‘Actual’ (e.g. the bullet). The background elements are encoded in shades of gray.

Up to 50% is nearly black - remember, this is our floor

From 50% to 75% is about 50% gray - this is our marginal performance section

And finally, from 75% to 100% is light gray (20%) for acceptable performance. I’ve also changed the target line to full black, it stands out nicely against both the light gray and the blue.

Step 6: Resize the chart.

So far, our chart hasn’t look much like a bullet chart has it? Well, that’s pretty simple to take care of. In this example, I’ve held the horizontal length the same but compressed the vertical height. Now we have something that very definitely is looking like a bullet chart.

Step 7: Add a text label and center align the chart to the text label.

Ok, so what I’m really doing is adding a text description, and the actual value of my metric in the two cells to the left of my bullet chart. Then, I’m massaging the bullet chart into alignment (hopefully) with the text. Notice that the value I’ve put in the label (86) doesn’t match the value in the chart, we’ll fix that in the next step. You might have also noticed that in this step, I removed the final piece of chart junk from my chart - the chart area border. Up until now, it was helpful to have the border for moving the chart around, but now that I have it correctly situated, I no longer need it.

Step 8: Synchronize label value to chart value.

Yep, you guessed, that’s a fancy way of saying set the first value of the center stack equal to the text label value (=D89). Now, I can enter a new value on the label, and the chart will update automatically.

Step 9: Obscure the chart data.

We had to jump through a lot of hoops to get to this graphic, but we probably don’t want the report consumer to be thinking about those hoops, so set the text color of your chart configuration data to ‘white’, effectively hiding it from the consumer.

Step 10: Rinse & Repeat

 Ok, so there’s my step-by-step on how to create Bullet charts.

What? You want the spreadsheet? Well, since you asked so nicely…

Download the Excel 2007 version or Download the Excel 2003 version

First of all, thank you to Avinash and the rest of the Google Analytics team for getting me into the new version of G.A. while I was at Emetrics. Avinash, in particular seemed interested in my comments on the new visualizations, so without further ado, here is some feedback for the team.

I’d really love to start off by talking about the addition of sparklines, but I just can’t because I am too distracted by the big trend chart.

Here’s what that looks like:

Let’s take a quick moment to compare that to a Google Finance chart:

Notice that they are both ‘web 2.0-ey’ and ‘ajaxey’. However, the GA chart seems to have gone a little over the top with the very thick trend line and large diameter data point markers.

It’s worth noting that the Google Finance chart offers multiple ways to traverse (interact) with this data. I can both traverse and zoom (or unzoom) the chart at the bottom as well as zoom the chart from the top-left text links. In the GA chart my only option for interacting with the data is through the pull down menus at the top right (one for date(s) and one for dimension which are kind of kludgey if you ask me - they don’t respond -hhmmm- smoothly and they rearrange the page whereas the controls in the GF charts don’t require the page to be reordered and react both quickly and smoothly.

None of these are my biggest criticism, the simple fact is that the GA chart with the heavy line and large radius markers makes me feel like it’s a kid’s chart, that is a chart designed for a kid.

I find it very odd that the GA Dashboard chart is liquid - that is it stretches to fit the browser width. This changes the horizontal scale of the chart and can either obscure important trend details or create artifacts in the visualization - a fixed width chart that is appropriately scaled would be better.

Maybe it’s just me but I actually feel patronized by the Google Analytics chart like I’m so clueless that I wouldn’t notice the trends or the data points if they weren’t pessimistically obvious. I *think* that I actually like the roll-over data labels because it puts the detailed data into the chart as opposed to how the GF chart updates the information way up in the right hand corner is you hover over the map. But, again the heavy black border just screams ‘MAKE SURE YOU LOOK AT ME AND IF YOU DON’T THEN YOU DON’T KNOW WHAT’S GOING ON HERE!’.

It’s not that I’m totally in love with the GF charts, as noted above I’d really like to see the detail data down next to the chart in a data label and while I theoretically like the idea of marking key news events on the chart, the implementation can get quite messy and distracting. Unfortunately I don’t have a good solution in mind yet but I’d really like to see it cleaned up.

For the GA chart, I’d recommend:

1. Don’t talk down to the user, move towards a more elegant presentation like that of Google Finance
2. Build in interactive controls like the ones used in GF
3. Make the chart fixed-width, in this case liquidity is bad
4. Potentially allow for multiple dimensions to be charted (within reason)

Ok, that’s enough about the dashboard chart, sparklines will have to wait for another day.

By the way, overall I like the redesign of GA - for me it is generally more intuitive to navigate and use.

I’d love to hear what you think…

Or, boy I am I oblivious.

Eric Peterson lobbed this my way a week ago and I just noticed it a few days ago (I’ve been hard at work on a response).

His basic question, if I may restate it, is “What do I think about using a ‘tachometer‘ to display KPI data?”

The short answer is, I love to do this - when it’s practical.

If you work in Excel, it is often not practical because the level of initial work and maintenence needed to operate a custom chart element like this violates the efficiency rule (remember, that in order for reports to be truly effective, they need to be easily repeatable so that they can be delivered efficiently and on time). In other words, I’m lazy and it is, generally speaking, too much work for not enough return.

That being said, I’ve spent the past couple of days approximating the dashboard that Eric refers to (via Dashboard Spy and courtesy of Subaru) in Excel, to see if it could be done - with the proper wherewithal.

Here’s a screen grab of the finished product:

Ok, so it can be done.

Here’s a list of resources you’ll need to pull it off:

• Andy Pope’s Gauge Work Around
• John Peltier’s Speedometer Work Around
• Apps Pro X/Y Chart Labeler
• Excel Camera Tool

Scared yet?

Ok, here’s my file for the above picture, you’re welcome to try it out (no commercial use, please), but I make no warranty as to the accuracy or stability of it.

Long story short, custom dashboard elements like the above are cool, sexy and can effectively communicate important information such as KPIs, but hopefully you have a better platform than Excel to create them in (this is where something like Xcelsius becomes helpful).

I’ve spent a lot of time in life reading. In fact, back in my college days I dedicated a significant amount of effort in studying literature and my favorite genre (obviously) is Science Fiction.

Why’s that relevant? Well, I find myself thinking about visualization and dashboards (again) and then I got to thinking about Science Fiction (no it’s not stream-of-consciousness). Good Science Fiction is not just escapism and if anyone thinks otherwise I challange you to read Frank Herbert’s Dune in the context of the Middle East today.

Good Sci Fi takes contemporary issues, problems or ideas and puts them in an unfamiliar context so that those issues stand out. In the unfamiliar context of the future, psychological distance is created between the reader and the issues so that not only do they stand out but the issues are open to be dealt with by the reader because they are no longer ‘too close to the problem’.

Dashboards and dashboard elements tend to do the same thing - but inside out. They take the data and put it into a familiar context. Taking the data into a familiar (but not obviously related) context makes the information the data is trying to communicate stand out.

So what? You ask again. When you’re designing a report and looking for ways to make the information ‘pop’ try to consider visual cues that are familiar to your audience and see if you can’t bring the data to life in that (those) familiar item(s).

By the way, it’s NOT easy, it’s a big hard IF, but when it happens, the report will absolutely sing.