An Excel Dashboard Widget

As I wrote in my last post, I’ve been spending a lot of time building out Excel-based dashboard structures and processes of late. I also wrote a few weeks ago about calculating trend indicators. A natural follow-on to both of those posts is a look at the “metric widget” that I use as a basis for much of the information that goes on a dashboard. Below is an example of part of a web site dashboard (not with real data):

I’ll walk through some of the components here in detail, but, first, a handful of key points:

• There is no redundant information — it’s not uncommon to see dashboards (or reports in general) where there is a table of data, and that table of data gets charted, and the values for each point on the chart then get included as data labels. This is wasteful and unnecessary.
• Hopefully, your eyes are drawn to the bold red elements (and these highlights should still pop out for users with the most common forms of colorblindness — I haven’t formally tested that yet, though) — this is really the practical application of the vision I laid out in my Perfect Dashboard post.
• I have yet to produce a dashboard solely comprised of these widgets — there are always a few KPIs that needs to be given more prominent treatment, and there are other metrics that don’t make sense in this sparkline/trend/current format
• I do mix up the specific measures on a dashboard-by-dashboard basis. In the example above, showing the past two years of trends by month, and then providing quarterly totals and comparisons, makes the most sense based on the planning cycle for the client. But, that certainly is not a structure that makes sense in all situations.

And now onto the explanation of the what and why of each element, working our way from left to right.

Metric Name

This one hardly warrants an explanation, but I’ll point out that I didn’t label that column. That was a conscious decision — the fact that these are the names of the metric is totally obvious, and Edward Tufte’s data-ink ratio dictates that, if it doesn’t add value, don’t include it!

Past 12 Months Sparkline

The sparkline is another Tufte invention, and it’s one that has really taken off in the data visualization space. That’s good, because sparklines are darn handy, and the more people get used to seeing them, the less there will need to be any “training” of dashboard users to interpret them. Google Analytics has been using sparklines for a while, even, so we’re well on our way to mass adoption!

One tweak on the sparkline front that I came up with (although I’m sure others have done something similar): I add a second, gray sparkline for either the target or the prior reporting period. I like that this gives a quick, easily interpretable view of the metric’s history over a longer period — has it been tracking to target consistently, consistently above or below the target, or bouncing back and forth? Is there inherent seasonality in the metric (signified by both the black and gray sparklines having similar spike/dip periods)?

One limitation of sparklines is that they don’t represent magnitude very well. If, for instance, a particular metric is barely fluctuating over time, then, depending on how the y-axis is set up, the sparkline can still show what looks like a wildly varying value. It’s a minor limitation, though, so I’ll live with it.

4-Month Trend Arrow

The 4-month trend is the single icon that results from a conceptually simple (but a little hairy to calculate) assessment of the most recent four data points. That was the punchline of an earlier post on calculating trend indicators. Whether the basis of the trend is months, weeks, or days can vary (not within one dashboard, generally, but as a standard for the dashboard overall), as well as whether it’s 4, 5, 6, or more data points. It’s a judgment call for both driven by the underlying business need that the dashboard supports.

I promise, promise, promise to make a simplified example of this arrow calculation and post it in a future post — check the Comments section for this post to see if a linkback exists (I’ll come back and update this entry as well once it’s done)

Current

Typically, when sparklines are used, the exact value of the last point in the sparkline is included. In the example above, I’ve done something a little different, in that I actually provide the sum of the last three data points. This is a quarterly dashboard, but the sparkline has a monthly basis to it to show intra-quarter trends. If the current value is sufficiently below the target threshold, then the value is automatically displayed as bold and red.

There are certainly situations where “Current” would actually be the last point on the sparkline. Like the trend arrow calculations, it’s a judgment call based on the business need that the dashboard supports.

YOY

In the example above, there is a comparison to the prior year. But, this could be a comparison to the target instead. Target-based comparison is even better — straight period-over-period comparisons tend to feel like something of a cop out, as prior periods really are more “benchmarks” than true “targets.” Now, setting a target as something like “15% growth over the prior year” has some validity! That would then impact both the gray sparkline, the “when does Current go bold red,” and this %-based calculation.

28 Data Points

In the version of the widget above, there are 28 unique pieces of data presented for each metric: the metric name (1), the black sparkline (12), the gray sparkline (12), the trend indicator (1), the current value (1), and the year-over-year growth percentage (1). And that’s not counting the conditional formatting that highlights values as bold and red when certain criteria are met. That’s a key aspect of the widget design. 28 sounds like a lot of data to represent for a single metric. Yet, they seem pretty digestible in this format, don’t they?

Let me know what you think. Does this work? What doesn’t work?