Excel Sparklines: Weapons of Mass Visualization

Data Visualization

Today, the phrase “Data visualization” evokes mental images of beautiful images, works of art, really, that convey information about our world. But we’ve all seen other beautiful images that seem to have a point, but it’s difficult to discern what that point actually is. The heart and soul of data visualization is the quick, effective and accurate communication of information present in large sets of data to a human being. When used properly, Excel sparklines do just that. While no one would claim they are great beauties in the world of data visualization, Excel sparklines communicate, and that’s the most important job.

What are Excel Sparklines?

Sparklines are nothing more than tiny charts that fit within a single worksheet cell, in contrast with regular charts which are separate objects pasted onto a worksheet. Their most important function is to aid an analyst in getting a global feel for the trends present in large numbers of worksheet rows. The analyst can then make informed decisions about where in the data to probe more deeply. Let’s look at two examples to see how to use Excel sparklines

Excel Sparkline Example One: Economic Data

Our first example represents the most common use of sparklines. Our data, which can be downloaded here, represents gross domestic product (GDP) data for over 200 nations.

We start by inserting a column where we would like to see our sparklines. Going to Excel’s Insert ribbon tab we click the Lines icon in the Sparklines group.

Create Excel Sparklines

On the new Sparklines Design tab which has now appears in the Excel ribbon, we can drop down the Marker Color menu and assign colored points to the highest and lowest values occurring in the range for that particular sparkline. (If you don’t see the sparklines design tab, be sure to select a cell in the sparklines column.)

Excel Sparklines Marker Color

Excel Sparklines for Annual GDP Data
Sparklines for Annual GDP Data

Now when we look at the sparklines, the high point is clearly evident, and we immediately observe that many of the sparklines hit this peak for 2008 and then decline. We also notice that the shape of the curve for Argentina is different than the shape of ,any of the other curves, indicating economic difficulties during this period not reflected by the global economy as a whole. This is why sparklines are so valuable; such trends would be almost impossible to observe in the numbers. A cluttered worksheet with over 200 conventional charts would not be much better.

Example Two: An Analysis of Variance

While the approach we have taken, creating a sparkline for each row of a dataset, is the most common scenario for using sparklines, we can use sparklines anywhere we would like multiple charts in a relatively small space. For example, we might want to graphically show the trends for different treatment trends in a scientific experiment. We’ll illustrate this using experimental data in a second workbook (download here) that contains an analysis of variance performed using one of the functions in Excel’s Analysis Add-In.

Unlike the previous example, we will want six cells into which to place column sparklines. Rather than disrupt the worksheet by making some rows and columns wider, we will just merge some cells.

Anova-MergeCells

We can insert a column sparkline into each of these six cells, but when the Create Sparkline dialog box appears, the location range will refer to all of the cells that have been merged, and this will not work. We modify the location to refer to just the first cell in the merged range. Of course, for the data range we will provide the mean and variance for the experimental conditions.

Creating a data range for Excel sparklines
The entire range for merged cells will not work for sparklines
Editing data range for excel sparklines
We must use only the first cell in the merged range.

 

Excel sparklines example
This can’t be right!

Whoa! Something’s Gone Horribly Wrong!

Let’s reconsider the GDP sparklines we saw in the first example. Sparklines for Finland and France look similar though the GDP of France is roughly ten times greater. Sparklines are designed to indicate trends, not absolute values. Therefore, the default display for sparklines is for Excel to select a vertical range that reflects the difference between the highest and lowest values. As a result, if we are only displaying two values, as is the case with the ANOVA example, one column will be the full height of the cell and the other will be a flat line, essentially zero. Making matters worse is that if we have six cells containing sparklines, the highest and lowest values will be different for each cell.

We would like all six sparklines to have the same vertical range for the columns, and this is easy to do be combining the individual sparklines into a group. As before, the Sparkline Tools tabe will not be visible until after you have selected cells containing sparklines. Once the group is defined, we can manually set the minimum and maximum values for the columns. We do this from the Axis menu. The default is “Automatic”; we must select :Custom Value…”.

Grouping in excel sparklines Excel sparklines Custom Value

In many cases, including this example, the minimum should be 0.0 so that the smaller column actually shows some height, and the maximum value should be just higher than any of the values to be displayed. In this example we will choose 8.0.

Excel Sparkline Matrix
Resulting Sparkline Matrix with Appropriate Scaling

Now this is more like it!

Conclusion

Sometimes we would like the advantages of having graphics to call our attention to interesting details, but don’t need a full-fledged chart. Indeed, as in the case of the GDP data, too many charts would actually hinder out goal. In such cases, sparklines provide an easy and functional means for quickly identifying and communicating trends in data.

For more on Excel sparklines, have a look at Learning Tree’s new 1-day course – Excel PivotTables: Building Dashboards.  In it you will learn how to visualize data changes with conditional formatting and sparklines.  Plus you can attend online from the convenience of home or office!

Type to search blog.learningtree.com

Do you mean "" ?

Sorry, no results were found for your query.

Please check your spelling and try your search again.