Excel 2010: sparklines v small multiples
Sparklines are defined within Tufte’s Beautiful Evidence (p 47) as:
“small high-resolution graphics usually embedded in a full context of words, numbers, images. Sparklines as datawords: data-intense, design-simple, word-sized graphics.”
The most common application of sparklines within business intelligence departs from this idea a bit, as the images are rarely embedded within text. Their use in the context of dashboards and reports could better be conceptualized as an extreme form of the more general concept of small multiples.
If one adopts this perspective, the introduction of sparklines in Excel 2010 without improved support of small multiples in general is a disappointing and rather narrow improvement. For example, sparklines have the following formatting options:
- highlight the highest and/ or lowest data point
- highlight the first and last points in the chart
- color negative points using a different color.
As far as I know, these options do not exist for normal charts, though they could clearly be done manually. Conversely, sparklines do not offer anywhere near the level of customization of normal charts. For this reason, I intend to continue creating sparklines the same way I did in earlier versions of Excel, namely by shrinking down a graph to a few cells in size and deleting the axes. Using this method I would not be able to replicate the win loss sparkline easily, but I do not have much use for that anyway.
The following explains how to create small multiples and the advantages of this approach over the sparklines feature or a single large graph. In this example, I created dotplots for a series of 9 values. The first set of plots is created using a single graph, the second set of plots is created using a graph for each value, i.e. 9 graphs.
The advantage of the small multiples in this case is that they remain synchronized with the text in accompanying cells when the values are sorted or filtered. Filtering and sorting are important features that most business intelligence tools should support. The following illustrates problem with sorting when a single graph is used.
The small multiples are created by plotting a dummy value, unhidden in this version. More specifically, the rows are not ‘hidden’, as this would remove the data points from the graph. Instead, the column widths are set to a fraction of an inch.
To create the individual small multiples, the vertical axis is set so the data point is centered.
The axes are deleted. Then the plot area is stretched to match that of the chart area and both the plot area and the chart area are set to have no fill. The small multiple is then copied 8 times, each one set to refer to a different data point. The small multiples are snapped to the grid to assure proper alignment and size.
Sparklines do not offer dot plots, so they are of no use for this type of application. Moreover, two of the graph types supported by sparklines can be created with far greater customization using this small multiples method. For example, these line graphs highlight a specific value which is not the highest, lowest, first, or last data point. The highlighting is done with a cross as a symbol. And tickmarks indicate precise data points. None of this can be done with sparklines.
Clearly, creating custom sparklines from small multiples takes a bit longer, but the difference is a matter of minutes. When creating dashboards or reports that have a wide audience or will be re-used, I prefer to spend the few extra minutes to create the visualization that best represents the data. Another advantage to creating sparklines the old-fashioned is better backward compatibility; I do not expect widespread adoption of Excel 2010 among my users for a few more years.
In sum, in many cases I do not anticipate adopting the sparklines feature and will instead rely on a small multiples approach to achieving a similar, yet far more flexible visual display. However, for something like exploratory data analysis or prototyping, sparklines may be useful.
Hi there. Nice to stumble across a new excel blog in town.
I just commented on your comment over at Jon Peltier’s blog, and thought I’d paste it here also.
I use shrunken charts as sparklines a lot in my work, and agree they are very helpful. If you’ve got excel 2007 or later, they far surpass what you can do with the Excel Sparklines functionality introduced in 2010. (I commented on one of Jon’s posts at
http://peltiertech.com/WordPress/sparklines-for-excel-vs-excel-2010-sparklines-guest-post/ regarding this, if you want to see an example of how I’m using them)
But for a solution such as what I posted in the comments at http://peltiertech.com/WordPress/stacked-bar-chart-alternatives/ I’d prefer to whip up 160 incell formula-based graphs, rather than shrink lots of fully-fledged graphs down to one cell and then spend all that time pointing each graph to the appropriate series. Although excel will certainly let you do it.
In regards to your comment above about excel charts not natively having dot markers for the first and last points only of the graph series only like sparklines do, I’ve just started using some array formulas that feed off my graph series to whip these up on the fly.
Here’s a screenshot by way of explanation: http://screencast.com/t/ZTc4ZjQxNWIt
For this particular chart, new data is added to the spreadsheet every quarter, but I only ever want to graph data for the last 10 years. As you can see, I added markers just for the first and last entries as well as a line horizontal to the first point so that you can easily judge how the series has moved over time (10 years) relative to that first point.
Normally I’d add 2 more series on my data sheet for these below where the original graph series sits, and reference them with a dynamic formula to take into account additional data additions. For the markers, I’d use one with a whole heap of =NA() formulas except for the first and last data point which would point to their corresponding data for the first and last dates. Then I’d format that series so that it had round markers, and no line. For the horizontal line, I’d put an absolute reference to the first data point and just copy it along.
But when you’re using lots of graphs with lots of data points, adding 2 extra ‘helper’ series per graph is a pain. And if your ranges change size, the formulas for the marker series would have to be respecified so that they are the correct length with the correct first and last data points specified.
So the other day I created some dynamic arrays to do this. First I defined a dynamic name called Employment_Series that returns the last 10 years data from the spreadsheet.
Then for the markers series I then define another dynamic named range called Employment_Series_Markers that creates a new series the same length as the Employment_Series, but with #N/As for every number except for the first number and last number in the series. This range has the formula:
=(IF(- – (Employment_Series = OFFSET(Employment_Series,0, MATCH(Last_Cell,Employment_Series)-1,1,1)) + – - (Employment_Series=OFFSET(Employment_Series,0,0,1,1)) =0,NA(),1))*Employment_Series
…with the name Last_Cell defined as the constant =9.99999999999999E+307
This creates an appropriately sized series on the fly. If the Employment_Series data reference expands, contracts, or moves, my Employment_Series_Markers array adjusts accordingly.
Then for the horizontal line series (horizontal from the first point of the Employment_Series series) I use the following formula:
=(–(Employment_Series=Employment_Series)*OFFSET(Employment_Series,0,0,1,1))
So if my range Employment_Series is an array such as {10,5,6,7,8,0,2} then the Employment_Series_Markers array is {10,#N/A, #N/A,,#N/A, ,#N/A, #N/A, 2} and the Employment_Series_Line array is {10,10,10,10,10,10,10}
And if the Employment_Series array expands, contracts, or moves, the helper series do the same.
However, Jon Peltier rightly pointed out to me that a down side of this is that the approach is a bit obscure to anyone else who tries do decypher your approach, or even to yourself a year later.
Looking forward to reading more from this blog, now I’ve added you to my RSS feeds. Keep up the great posts!