Excel 2010: Sparklines, not too shabby

There's been some excitement in the data visualization world about Excel 2010's sparkline implementation (we're an easily excited bunch), and less excitement about a patent application that Microsoft has applied for concerning the sparklines (lots of prior art). Anyway, I've played with them a little - they are pretty robust and for a first implementation, far better than their first attempt at in-cell bar charts in Excel 2007.








There are your standard sparklines (if there is such as a thing), columns, markers on the sparklines, such as high, low, first, last, and a win/loss variant. They are missing banding showing a desirable range of values, but the options for controlling the axes are nice. Something which is very nice is that when you create a single sparkline, and then copy the cell down it treats it as a formula and adjusts the data, but the sparklines are then treated as a group, making editing and axes manipulation very easy.

It will certainly be nice to not have to include macros to allow clients to view sparklines that you've created, but there is still plenty of room for the excellent free and paid sparkline add-ins that exist today.

Excel 2010 in-cell bar charts (are much better)

I've posted before on my opinions about Excel 2007's in-cell bar charts, and mentioned that Excel 2010's are supposed to be much better. Well, having got my hands on the beta version yesterday, I can say yes, yes, yes, they are much better. The gradient option is still there, but is not a default. Zero, and very low numbers compared to other numbers in the range have no bar height (see examples A and B below), AND there's no option to turn on the fake bar height.

Negative numbers in a range result in an axis being drawn, and the bar appearing the other side of the axis, in a different color (D). You have full control of colors of the axis and bars. But, wait, there's more. You can control the max and min of the chart - great if you have multiple in-cell bar chart ranges that you want to be able to compare.

You can't have the bars offset from the data as they are a conditional format, but you could just slap in a formula where you want the bar: "=cell where the data is", and then click the option to show just the bar, not the data (E). Even the gradient option is better, as there is a border by default allowing you to see where the end of the bar is (C). Finally, the resolution of the bar (i.e. when a bar height goes to zero), depends on the width of the cell (B). Great job Microsoft.

Box plots

Let's say you have the scores from a test that 500 people spread across 5 departments took. How can we better understand this data to make sense of how different departments performed? Here are some ideas - easiest to most involved, but also least useful to most useful:

• Easiest: present a table of the mean scores. 
• Median score: By choosing this instead of the average, we are making less assumptions about the spread of the scores, so perhaps the median would be a better choice
• Sort the departments by median score
• Create a bar chart with this sorted data
• Create a box plot with this data

Most of us haven't created a box plot since high school. A quick reminder - a center line in a box showing the median value (typically), a box spanning this value,  showing the scores in the 25th to 75th percentile (or sometimes a standard deviation either side), and another set of bars showing the maximum and minimum, or maybe something like the 5th and 95th percentile. I've taken this standard representation of the box plot and decreased the "chart junk" - the extra lines and information that do nothing in aiding our interpretation of the data, while also improving the readability of the data (I think, anyway).

Here's the result for our 5 departments. The departments are sorted by highest to lowest median score. The median is marked in a thick red line, making it easy to quickly compare the scores. There is no axis line for the horizontal axis - no need for one. The gridlines are a very pale grey. The boxes marking the percentiles are filled, but in a light color, and the caps on the max/min have gone, but the line is a little thicker. Quickly you can begin to form opinions about the spread of the data and the comparative performance between departments.

Even better is that now this sort of chart is suitable for inclusion in a dashboard - you can reduce the size of it without sacrificing the comparisons it provides. Box plots are not standard in Excel - I'll post a tutorial on how to create ones just like these soon.

Old news

A few days ago I talked about the gradient effect on the Excel in-cell bar charts. Coincidently today I came across a post I made to the Dashboard Spy blog back in the heady days of May 2006 when Office 2007 was in beta release. I posted a quick review of some of the conditional formatting available, including criticizing the gradient effect, but liking the ability to shrink the charts to make effective sparklines.

I was clearly too blown away by Office 2007 (or too confused by the ribbon?) to notice that even in my example shown, the bar chart scale does not start at zero, giving a false impression of the variability of the data. Anyway, maybe I was one of the first to vocalize my dislike of the gradient...

On a related note, a commenter on another website in Sept 2006 gives this possible reason for Microsoft's decisions on the in-cell bar charts:

The default graphs were hideous at best, but now, thanks to a focus-group-tested and user-centric decision probably made by marketing drones without a brain: Microsoft Excel deliberately misrepresents data, because it turns out, users didn't like empty cells in bar-graphs; idiots :-)

Undeniably harsh and unfair, but the commentator has some points that I will touch on soon about user-groups and designing interactions.

Excel in-cell bar charts

Often you don't need to create a big chart to get your point across. The concept of having bar charts in a cell has been around for a while, and Microsoft picked it up for Excel 2007.

As soon as I saw them in a beta version of Excel, I started hunting around for the property formatting to get rid of the gradient on the bars - what's the point of having a comparison if you can't actually easily see where the bar ends? Unfortunately there is no way to change this - lots of washed out color options, but no option for just a solid color.

I'm certainly not the first to point this out, or that the zero has a default thickness of 10% (!?) making it hard to distinguish between zero and low numbers and making comparisons misleading between very high numbers and low numbers. Equally the bars are scaled by default between the lowest and highest number, not zero to the highest number.

Excel 2010 fixes these issues - allowing for solid fill (though the gradient fill is still the default option), defaulting to scale the bars from 0, and adding a few nice features. I'm still surprised that they didn't get it right first time - it is so obviously wrong in the current version.

As 2010 isn't out yet, and it will take a while for many companies to upgrade, the best way to do in-cell bar charts is using a formula to create the bar. You can even get fancy and have multiple bars in one cell. These methods aren't perfect - you have to choose your font carefully to get a solid bar, and the resolution to show differences in bar heights is limited because you're using characters instead of a graphic - they work well though. The example shown uses the formula "=REPT("|",cell ref to left), | is the pipe symbol. The font is Script, bolded, and I've toned the bars down to a light grey and added conditional formatting to show values 35 or higher