Or, let the beatings begin.

I started a new gig just about six months ago now and one of the goals I set for myself was to not fall into the trap of using bad visualizations. Have I been successful? Hard to tell yet, but it has taken an interesting turn - which could easily be defined as a failure ;). I’ve been proselytizing the use of Bullet Charts as a responsible replacement for gauges and dials. I’ve also been fostering the use of sparklines and clean charts in general. I’ve done really well with instituting sparklines and have raised the charting game of my co-workers.

However, Bullet charts have just not taken to the water.

I’ve said before that bullet charts faced a tough road. Basically, they are different enough to require end-user training which violates my the rules for effective chart design.

That being said, I’ve campaigned heavily for their use over the past 6 months but ultimately, the report users just weren’t groking the chart and continually made mistakes in interpreting them. That’s not good. Misinterpretation can lead to the wrong insight or answer which leads to the wrong action.

So my boss says “Look, just give them a gauge - how about a speedometer chart?”

-ABSOLUTELY NOT-

Then I explained all that was wrong with using speedometers in a business context and beyond that how hard they are to create and maintain.

That still left me with a problem though. the bullets weren’t hitting the target (har, har, har) and I needed a replacement. So, I grumbled a bit. Sighed and starting thinking about a gauge chart.

First, what are the typical problems with gauges as implemented in business dashboarding?

1. They take up too much space (bad information-to-ink ratio)
2. Low fidelity (data is not displayed with a high level of detail/resolution)
3. A tendency to be visually imprecise - easy to tell if it’s bigger than a breadbox or smaller than a Volkswagen but assessing the actual value with any accuracy can be difficult
4. May suffer from visual effects that create inaccuracies in interpretation (e.g. perspective, shadowing etc. create visual lies)
5. All the other ones that I couldn’t think of - off the top of my head

OK, so to solve this correctly I needed to:

A. Maintain a (relatively) high information-to-ink ratio

B. Show the highest possible fidelity in the data

C. Show data precisely

D. Eliminate/Minimize distorting visual effects

Inspiraton: The picture in my head (hazy as it was) looked like some kind of decibel meter but this picture of an old pyrometer is also pretty close.

Photo Courtesy of RightBrainPhotography (c)

This old gauge is

• highly precise (to 1 degree Farenheit)
• relatively compact
• contains enough meta information to ward off imprecision in reading it

What’s missing?

• can’t get away from red, yellow & green in a business dashboard
• related marker values - minimum, acceptable, target and goal

Toolbox:

So what will we need to get the job done?

• Excel 2007
• Horizontal Bar Chart
• X/Y Scatter Chart
• XY Chart Labels Addon

There are many, many steps involved, which are covered in the xlsx download at the end of the post. The instructions assume a medium-to-expert level knowledge of charting in Excel. So, in order to keep this post relatively short, I won’t go through the steps here and leave that up to the download. Instead let’s take a look at the completed graph.

So here it is and it has only the vaguest relationship to the image above - right? About the only thing they have in common is the pointer.

BTW, I kinda like how Excel 2007 lets you use line ends (don’t remember that being in 2003/XP).

Alright, so what do we have going on here?

The chart is bounded by a box in a Dashboard. This box, by its title is “Page Views - Month to Date” so we know what we’re looking at. Immediately below the title we have the current count of page views (yeah, not a KPI [yet]) at 5 million in big, big text.

We can see that we’re in the red but closing on yellow. However, if we stopped here with no other cues it would look like page views are more than 50% along because our eye sees that the pointer has traversed most of the red.

But, we’re not done! There’s a custom X-Axis (courtesy of an XY Scatter) that the pointer points to and it shows that page views have traversed exactly 50% to the overall goal.

Of course, we could do the match in our heads (5 million is 50% of what?) but we’ve also got custom labels on the bar chart (our gauge background) so we know that our goal is 10 million page views, the minimum is 7 million and the target (minimum ‘acceptable’) is 9 million page views. Each of these markers in the gauge background are tied to a point on the x-axis so that we always know, with a high degree of accuracy, where we are, and where we want to get to. And notice how our non-KPI measurement of 5 million page views turns into a simple KPI as we measure it in terms of a goal - we’re 50% of the way there!

Lastly, this chart is no harder to maintain than a bullet chart is (actually, it’s just a bit easier/simpler).

So here are some questions for you:

Do you like the chart? Why/Why Not?

Is it a good chart? Why/Why Not?

What solutions have you used in the stead of something like a bullet chart because users just couldn’t get it?

Oh and here’s the file for you.

Update: After multiple requests, here’s some side-by-side screen shots of the line-gauge and a bullet chart based on a bullet chart I have blogged about previously.

As a bonus here is Peter Walker’s quick mockup

Now you have visual comparisons…

W

orking with Custom Number Formats

If you’ve worked with Excel very long, you have probably gone through all of the number formats available to you and seen some custom ones that you wish you knew how to do.

BTW, just to be clear we’re talking about Format Cells –> Number –> Custom

I’m going to cover a couple of my current favorites just to get the conversation going but it would be great if others chimed in with their favorite custom number formats or with their questions!

In science we have scientific notation, which Excel handles but it’s ugly and somewhat mysterious. Ten Billion (10,000,000,000) would be displayed as 1.00+E10 (one times 10 to the tenth power). In business we often shorten big numbers too right? 10 billion, 300K, 15M(illion), etc and it would be helpful if we could shorten really big numbers in a similar fashion because we often don’t need the full degree of accuracy to be shown (it will still be there but we want to shorten the number to a reasonable bite-size). So here’s the trick that I learned recently…

We’re probably all familiar with this number format:

#,##0.00 - this is Excel’s default number format (in the US) and displays the number with commas separating thousands, millions etc. and two significant digits e.g. 10,000,000,000.00. Well we get a lot of business speak around here and don’t need that level of accuracy - in most cases - so why not have the same shorthand in the reports that we do in language?

By adding successive commas to the custom number format, we can “divide” the original number by a thousand.

For instance, take our 10,000,000,000 and apply the custom number format of #,##0.00,

Now our number is 10,000,000.00 thousands I can even indicate this by adding the following to my number format: #,##0.00,”T” (or “M” for you Romans) and the number is displayed as 10,000,000.00T

Two commas (#,##0.00,,”M”) reduces it to millions as in 10,000.00M

Three commas (#,##0.00,,,”B”) reduces to billion - 10.00B

Effectively, we’ve taken a very large number and reduced the space it takes to display it - higher data density. We’ve given up a bit of the precision, from a display perspective, but when working with very large numbers, the significant digits can be effectively reduced and still convey the important information.

My other example today relates to a pet-peeve of mine - consistency. When working with currency - $1,000.00 - one of the choice formats is to display negative values in red and in parenthesis. Apparently people who spend all their time in ledgers appreciate this format. However, there is no matching format for percentages and I’m always calculating percent change. So, I want my negative percent changes in revenue to match my negative revenue numbers.

Again, the default currency format for negative currency is

($1,000.00) which is the equivalent of the custom format $#,##0.00_);[Red]($#,##0.00)

Ok, first an admission I don’t know what some of the above means - specifically the _) just before the semicolon, if someone wants to explain that would be awesome. But I do know that everything before the semicolon deals with positive numbers and everything after it deals with negative numbers. So [Red] in brackets tells Excel to display the text in red and the parentheses surrounding #,##0.00 tells Excel to surround it in parentheses.

So it’s pretty easy to map the same formats to percentages. First we take the custom format equivalent of Excel’s standard percentage display: 0.00%. Notice that it doesn’t do anything with thousands or with negative, so we just plug it into the custom format from above thusly:

#,##0.00%_);[Red](#,##0.00%)

1,000.00% | (1,000.00%)

Effectively, all we did was add the percent sign (%) after the significant digit declaration and we now have percentages that will be similarly formatted to currency.

What about you? What’s your favorite number formatting trick in Excel?

Ok, so about ten days ago I challenged my readers to solve the following charting problems with the Google API - Bullet Chart, Sparkline and Square Pie. I provided a sample bullet chart but asked if anyone could improve upon it. Unfortunately, only one brave soul, Jeff Donnici, took up the challenge and provided two solutions for Sparklines.

I am inviting Jeff to write his own discourse on how to create sparklines with the Google API, so Jeff, please email me (my email is at the bottom of the right-hand nav) with your description of how to create the sparklines.

First, let’s cover some basics of the API.

The API is pretty simple, it’s just a URL with a bunch of query parameters attached to define the chart and its elements. It can also be a bit annoying when you’re encoding the data sets and their associated information. My guess would be that to really get the power out of the API you would want some sort of programmatic interface to it as building the chart request ‘by hand’ can be laborious.

So, components that are used in these examples:

• Base API URL: http://chart.apis.google.com/chart?
• Required Elements
  • Chart Type Parameter: cht - Allowable Chart Types
  • Chart Size: chs - Chart Size in pixels [w]x[h] (chs=250×250)
  • Chart Data: chd - Chart data encoding methods (simple, text, extended)
• Optional Elements Used
• Chart Title: chtt - Text Title for the Chart
  • Chart Title Text Size: chts - Title font size and color
  • Bar Chart Height: chbh - Bar width
  • Chart Colors: chco - data series colors
  • Chart Background Fill: chf - Chart fill options

For me, let’s start with the Bullet Chart. The stumbling blocks here are that

1. The Google Chart API does not support combination charts, or at least not a bar chart / scatter plot combo, which is how I create Bullet Charts in Excel.
2. The API does not seem to support single data point properties. In other words, I can set the color and width properties for the stack (e.g. series) but not for data points in the stack.

If you remember the Excel example of the Bullet chart, you will recall that we have 3 data series (top, middle, bottom) each with 4 data points to create the basic visualization of the Bullet chart. We then combine into that chart a scatter plot to create the target line. Well this API doesn’t support coloring for a single data point (which is required in the Excel solution) so what does Google offer as a replacement? Well, with the background fill, we can go a linear stripe that will take up some percentage of the chart space - ok, I think I can work with that:

But first things first. Let’s define the chart type:

http://chart.apis.google.com/chart?cht=bhs

• cht=bhs tells Google that we are working with a horizontal bar chart

Next, let’s set the chart size

http://chart.apis.google.com/chart?cht=bhs&chs=250×40

• Remember, chs wants [width]x[height] in pixels and it’s required

While we’re at it, lets go ahead and set the bar width (or height if you prefer)

http://chart.apis.google.com/chart?cht=bhs&chs=250×40&chbh=10,25

• chbh lets us define that bar width and optionally the spacing between bars. In this example the gap is set to 25 to make sure that the chart fill area shows up around the bar itself.

Ok, for the last of the easy bits, let’s go ahead and set the Chart title

http://chart.apis.google.com/chart?cht=bhs&chs=250×40&chbh=10,25&chtt=Homepage+Retention+Rate:+90%

• Note the plus (+) symbols in the Chart Title text, this tells the API to use a blank space when creating the title

On to the data. As noted above, there are 3 options for encoding data - simple, text, and extended. I haven’t really figured out why you would use simple because it’s basically a substitution scheme that feels more annoying than anything. However, in this example we are using text encoding because it allows us to use regular numbers with up to 1/10th precision.

http://chart.apis.google.com/chart?cht=bhs&chs=250×40&chbh=10,25&chtt=Homepage+Retention+Rate:+90%&chd=t:90.0

• when adding the chart data, you must first tell chd how you are encoding the data (chd=t) and then supply the data itself (:90.0)

The above gives us a basic bar chart:

Now we need to add the elements that will make this look like a Bullet chart and we’re going to get it all done with chart fill!

http://chart.apis.google.com/chart?cht=bhs&chs=250×40&chbh=10,25&chtt=Homepage+Retention+Rate:+90%&chd=t:90.0&chf=c,ls,0,000000,0.50,cccccc,0.25,000000,0.005,efefef,0.245

chf (chart fill) takes the following arguments:

• fill area: background (bg) or chart area (c), in the Bullet chart, we use the chart fill method
• fill type: solid (s), linear gradient (lg), and linear stripes (ls)
• angle: angle of the stripe between 0 (horizontal) and 90 (vertical)
• stripe color and width pairs: color hex value and length expressed as a percentage

If you look at the URL snippet above in bold, you will see that we have defined

• the background element as "chart fill" (c)
• the fill type as "linear stripe" (ls)
• the "angle" as zero
• The first section is black and extends to 25% of the length
• The second fill section is the same as the first
• the third section is also 25% but is a dark gray
• the fourth section, only 1/2 of 1 percent in width is black - It’s a trick, this is the target line!
• The last section is 24 & 1/2 percent of the length and is a very light gray

That’s it, now we have a functioning bullet chart

I sure wish that I could remove the axes lines, and add data labels to the bar and target line, but oh well.

Ok, I need to take a break as this is quite a long post. In part 2, we will walk through the pie chart solution (which is not that great),