Small Charts in Excel: Beyond Sparklines, but Still Economical

I’m a fan of Stephen Few; pretty much, always have been, and, pretty much, always will be. When developing dashboards, reports, and analysis results, it’s not uncommon at all for me to consciously consider some Few-oriented data visualization principles.

One of those principles is “maximize the data-pixel ratio,” which is a derivation of Edward R. Tufte’s “data-ink ratio.” The concept is pretty simple: devote as much of the non-white space to actually representing data and as little as possible to decoration and structure. It’s a brilliant concept, and I’m closing in on five years since I dedicated an entire blog post to it.

Another Tufte-inspired technique that Few is a big fan of is the “sparkline.” Simply put, a sparkline is a chart that is nothing but the line of data:

Sparklines are not meant to provide the quantitative precision of a normal line graph. Their whole purpose is to provide a quick sense of historical context to enrich the meaning of the measure.

When Few designs (or critiques) a dashboard, he is a fan of sparklines. He believes (rightly), that dashboards need to fit on a single screen (for cognitive processing realities that are beyond the scope of this post), and sparklines are a great way to provide additional context about a metric in a very economical space.

Wow! Sparklines ROCK!

But, still…sparklines are easy to criticize. In different situations, the lack of the following aspects of “context” can be pretty limiting:

What is the timeframe covered by the sparkline? Generally, a dashboard will cover a set time period that is displayed elsewhere on the dashboard. But, it can be unclear as to whether the sparkline is the variation of the metric within the report period (the last two weeks, for instance) or, rather, if it shows a much longer period so that the user has greater historical context.

What is the granularity of the data? In other words, is each point on the sparkline a day? A week? A month?

How much is the metric really varying over time? The full vertical range of a sparkline tends to be from the smallest number to the largest number in the included data. That means a metric that is varying +/-50% from the average value can have a sparkline that looks almost identical to one that is varying +/-2%.

How has the metric compared to the target over time? The latest value for the metric may be separately shown as a fixed number with a comparison to a prior period. But, the sparkline doesn’t show how the metric has been trending relative to the target (Have we been consistently below target? Consistently above target? Inconsistent relative to target?).

So, sparklines aren’t a magic bullet.

So, What’s an Alternative?

While I do use sparklines, I’ve found myself also using “small charts” more often, especially when it comes to KPIs. A small chart, developed with a healthy layer of data-pixel ratio awareness, can be both data-rich and space-economical.

Let’s take the following data set, which is a fictitious set of data showing a site’s conversion rate by day over a two-week period , as well as the conversion rate for the two weeks prior:

If we just plot the data with Excel’s (utterly horrid) default line chart, it looks like this:

Right off the bat, we can make the chart smaller without losing any data clarity by moving the legend to the top, dropping the “.00” that is on every number in the y-axis, and removing the outer border:

The chart above still has an awful lot of “decoration” and not enough weight for the core data, so let’s drop the font size and color for the axis labels, remove the tick marks from both axes and the line itself from the y-axis, and lighten up the gridlines. And, to make it more clear which is the “main” data, and to make the chart more color-blind friendly in the process, let’s change the “2 Weeks Prior” line to be thinner and gray:

Now, if the fact that the dates are diagonal isn’t bugging you, you’re just not paying attention. Did you realize that you’re head is cocked ever so slightly to the left as you’re reading this post?

We could simply remove the dates entirely:

That certainly removes the diagonal text, and it lets us shrink the chart farther, but it’s a bit extreme — we’ve lost our ability to determine time range covered by the data, and, in the process, we’ve lost an easy way to tell the granularity of the data.

What if, instead, we simply provide the first and last date in the range? We get this:

Voila!

In this example, I’ve reduced the area of the chart by 60% and (I claim) improved the readability of the data! The “actual value” — either for the last data point or for the entire range — should also be included in the display (next to or above the chart). And, if a convention of the heavy line as the metric and the lighter gray line as the compare is used across the dashboard or the report, then the legend can be removed and the chart size can be farther reduced.

That’s Cool, but How Did You Do Just the First and Last Dates?

Excel doesn’t natively provide a “first and last date only” capability, but it’s still pretty easy to make the chart show up that way.

In this example, I simply added a “Chart Date” column and used the new column for the x-axis labels:

The real-world case that actually inspired this post actually allows the user to change the start and end date for the report, so the number of rows in the underlying data varied. So, rather than simply copying the dates over to that column, I put the following formula in cell D3 and then dragged it down to autofill a number of additional rows. That way, Excel automatically figured out where the “last date” value should be displayed:

=IF(AND(A3<>””,A4=””),A3,””)

What that formula does is look in the main date column, and, if the current row has a date and the next row has no date, then the current row must be the last row, so the date is displayed. Otherwise, the cell is left blank.

Neither a Sparkline Nor a Full Chart Replacement

To be clear, I’m not proposing that a small chart is a replacement for either sparklines or full-on charts. Even these small charts take up much more screen real estate than a sparkline, and small charts aren’t great for showing more than a couple of metrics at once or for including data labels with the actual data values.

But, they’re a nice in-between option that are reasonably high on information content while remaining reasonably tight on screen real estate.

Thanks, Julien. In this case, the formula is pretty “dumb,” in that it counts on the dates — whatever the increment — to be populated in a steady set of rows. The dates could be based on the Krikkit lunar calendar as translated to Dolphin and then represented phonetically — as long as they’re listed in contiguous rows, the formula should work.

But, I’m all about figuring out the last days of weeks and months with convoluted Excel formulas. Hmmm… I’ve seen various posts on the subject, but you’ve given me an idea for a new one!

I agree with everything here, am not a fan of sparklines myself but agree need to minimise the space used on charts. I take similar actions to you except removing the dates, understand the logic there. My additional tip would be to remove half of the gridlines by adjusting the maximum unit for the chart.

However my alternative approach is to create a drop-down so one chart on a dashboard can be used with multiple metrics. Can even display two at the same time if one is on the secondary axis. Need to do some smart work with Formula Name Manager but the end result is much less space being used on the dashboard.

Thanks, Peter. Excel is pretty unreliable when it comes to their “major unit” selection, so it’s always a tradeoff as to whether to leave that automatic or hard code a value. If the metric is reasonably stable, I definitely will hardcode it.

But, that’s not something I’d do if I had a dropdown to change metrics, right?

I was a little disheartened by Stephen Few’s latest post (http://www.perceptualedge.com/blog/?p=1466) — his version of the “students” dashboard. Among other things, it really did go overkill with sparklines, IMHO. And, I don’t think I see myself using “bandlines.”

I fully agree that sparklines are just another tool in the toolbox and should be used wisely (small multiples work great when you need to show correlations or compare items in lists).
S. Few idea is however not new. I created in 2006 (no typo) an add-in for Excel called “Sparklines for Excel”. In 2008 I added what I called a “Normality band” for line charts (see http://sparklines-excel.blogspot.fr/2008/05/linechart-specifications.html) and decided that one of those bands should be enough if you want to add some context without killing the readability of these tiny charts. Following the same logic (adding context without disturbing too much the main message), SfE can add vertical bands behind a line (to show week ends or help identify month change) see : http://sparklines-excel.blogspot.fr/2009/11/cross-tab-charts-with-sfe.html.
I spent hours testing different shades of grey before finding the right One, that would not interfere with the main message.

I use sparklines a lot. For example, I have a spreadsheet that tracks the incidents by symptom.
For each “symptom” (rows) I have a sparkline that represents the number of incidents on each week (cols). This results in a column of sparklines, each representing the trend of incidents by symptom.
The problem is that there is no common way to have all the “symptoms” playing on a level playing field.
The sparkline is automatically adjusted to be relative to the largest number in the count of incidents for the list of weeks. To remedy this, for every symptom, in the first column, I create a dummy entry with the high count for a week. This levels the playing field but creates one high bar for each sparkline.

Ignore my last comment. I see now that the sparklines are customizable under the Design menu. There it is possible to configure the minimum and maximum of the axis to be the same for a group of sparklines.
This resolves the issue from my previous post. Now my sparklines are giving much better looking results.