Small Charts in Excel: Beyond Sparklines, Still Economical

Posted on January 1, 2013February 12, 2024 by Michele Kiss

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:

Small Charts: Sparkline

In Few’s words (from his book, Information Dashboard Design: The Effective Visual Communication 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:

Small Charts: Sample Data

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

Small Charts: Default Excel

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:

Small Charts: Smaller Step 1

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:

Small Charts: Smaller Step 2

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:

Small Charts: Smaller Step 3 (Too Far)

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:

Small Charts: Smaller Final

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:

Small Charts: Sample Data with First and Last Date Column

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.

Analytics Strategy, Excel Tips, Presentation

Data Visualization Tips and Concepts (Monish Datta calls it "stellar")*

Posted on September 26, 2010October 25, 2019 by Eric Peterson

Columbus Web Analytics Wednesday was sponsored by Resource Interactive last week, and it was, as usual, a fun and engaging event:

We tried a new venue — the Winking Lizard on Bethel Road — and were pretty pleased with the accommodations (private room, private bar, very reasonable prices), so I expect we’ll be back.

Relatively new dad Bryan Cristina had a child care conflict with his wife…so he brought along Isabella (who was phenomenally calm and well-behaved, and is cute as a button!):

I presented on a topic I’m fairly passionate about — data visualization. The presentation was well-received (Monish Datta really did tweet that it was “stellar”) and generated a lot of good discussion. I had several requests for copies of the presentation, so I’ve modified it slightly to make it more Slideshare-friendly and posted it. If you click through on the embedded version below, you can see the notes for each slide by clicking on the “Notes on Slide X” tab underneath the slideshow, or you can download the file itself (PowerPoint 2007), which includes notes with each slide (I think you might have to create/login to a Slideshare account, which it looks like you can do quickly using Facebook Connect).

Data Visualization Tips and Conceptshttp://static.slidesharecdn.com/swf/ssplayer2.swf?doc=wawsep2010datavisualizationwithnotes-100926155559-phpapp02&stripped_title=waw-sep2010-datavisualizationwithnotes&userName=tgwilson

View more presentations from Tim Wilson.

I had fun putting the presentation together, as this is definitely a topic that I’m passionate about!

* The “Monish Datta” reference in the title of this post, while accurate, is driven by my never-ending quest to dominate search rankings for searches for Monish. I’m doing okay, but not exactly dominating.

http://b.scorecardresearch.com/beacon.js?c1=7&c2=7400849&c3=1&c4=&c5=&c6=

Excel Tips, Presentation

An Excel Dashboard Widget

Posted on November 16, 2009October 25, 2019 by Eric Peterson

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):

Sparkline Widgets

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!

Google Analytics Sparkline

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?

analytics