IN THIS CHAPTER
A Sparkline is a small chart that's displayed in a single cell. A Sparkline allows you to quickly spot time-based trends or variations in data. Because they're so compact, Sparklines are almost always used in a group.
Although Sparklines look like miniature charts (and can sometimes take the place of a chart), this feature is completely separate from the charting feature. For example, charts are placed on a worksheet's draw layer, and a single chart can display several series of data. A Sparkline is displayed inside a cell and displays only one series of data.
This chapter introduces Sparklines and presents examples that demonstrate how to use them in your worksheets.
Excel supports three types of Sparklines. Figure 22.1 shows examples of each, displayed in column H. Each Sparkline depicts the six data points to the left.
Figure 22.2 shows some data to be summarized with Sparklines.
To create Sparkline graphics, follow these steps:
The Sparklines are linked to the data, so if you change any of the values in the data range, the Sparkline graphic will update. Often, you'll want to increase the column width or row height to improve the readability of the Sparklines.
Figure 22.4 shows column Sparklines for the precipitation data.
When you activate a cell that contains a Sparkline, Excel displays an outline around all the Sparklines in its group. You can then use the commands on the Sparkline Tools Design tab to customize the group of Sparklines.
When you change the width or height of a cell that contains a Sparkline, the Sparkline adjusts accordingly. In addition, you can insert a Sparkline into merged cells.
Figure 22.5 shows the same Sparkline, displayed at four sizes resulting from column width, row height, and merged cells. As you can see, the size and proportions of the cell (or merged cells) make a big difference in the appearance.
By default, if you hide rows or columns that are used in a Sparkline graphic, the hidden data does not appear in the Sparkline. Also, missing data (an empty cell) is displayed as a gap in the graphic.
To change these settings, choose Sparkline Tools Design Sparkline Edit Data Hidden and Empty Cells. In the Hidden and Empty Cell Settings dialog box that appears (see Figure 22.6), specify how to handle hidden data and empty cells.
As I mentioned earlier, Excel supports three Sparkline types: Line, Column, and Win/Loss. After you create a Sparkline or group of Sparklines, you can easily change the type by selecting the Sparkline and clicking one of the three icons in the Sparkline Tools Design Type group. If the selected Sparkline is part of a group, all Sparklines in the group are changed to the new type.
After you've created a Sparkline, changing the color is easy. Use the controls in the Sparkline Tools Design Style group.
For Line Sparklines, you can also specify the line width. Choose Sparkline Tools Design Style Sparkline Color Weight.
Use the commands in the Sparkline Tools Design Show group to customize the Sparklines to highlight certain aspects of the data. The options are
You control the color of the highlighting by using the Marker Color control in the Sparkline Tools Design Style group. Unfortunately, you can't change the size of the markers in Line Sparklines.
Figure 22.7 shows some Line Sparklines with various types of highlighting applied.
When you create one or more Sparklines, they all use (by default) automatic axis scaling. In other words, the minimum and maximum vertical axis values are determined automatically for each Sparkline in the group, based on the numeric range of the data used by the Sparkline.
The Sparkline Tools Design Group Axis command lets you override this automatic behavior and control the minimum and maximum value for each Sparkline or for a group of Sparklines. For even more control, you can use the Custom Value option and specify the minimum and maximum for the Sparkline group.
Figure 22.8 shows two groups of Sparklines. The group at the top uses the default axis settings (Automatic for Each Sparkline). Each Sparkline shows the six-month trend for the product, but there is no indication of the magnitude of the values.
For the Sparkline group at the bottom (which uses the same data), I changed the vertical axis minimum and maximum to use the Same for All Sparklines setting. With these settings in effect, the magnitude of the values across the products is apparent, but the trend across the months within a product is not.
The axis scaling option you choose depends upon what aspect of the data you want to emphasize.
One useful feature that's missing in the Excel implementation of Sparklines is a reference line. For example, it might be useful to show performance relative to a goal. If the goal is displayed as a reference line in a Sparkline, the viewer can quickly see whether the performance for a period exceeded the goal.
You can, however, transform the data and then use a Sparkline axis as a fake reference line. Figure 22.9 shows an example. Students have a monthly reading goal of 500 pages. The range of data shows the actual pages read, with Sparklines in column H. The Sparklines show the six-month page data, but it's impossible to tell who exceeded the goal and when they did it.
Figure 22.10 shows another approach: transforming the data such that meeting the goal is expressed as a 1
and failing to meet the goal is expressed as a –1
. I used the following formula (in cell B18) to transform the original data:
=IF(B6>$C$2,1,-1)
I copied this formula to the other cells in the B18:G25 range.
Using the transformed data, I created Win/Loss Sparklines to visualize the results. This approach is better than the original, but it doesn't convey any magnitude differences. For example, you can't tell whether the student missed the goal by 1 page or by 500 pages.
Figure 22.11 shows a better approach. Here, I transformed the original data by subtracting the goal from the pages read. The formula in cell B31 is
=B6-$C$2
I copied this formula to the other cells in the B31:G38 range and created a group of Line Sparklines, with the axis turned on. I also enabled the Negative Points option so that negative values (failure to meet the goal) clearly stand out.
Normally, data displayed in a Sparkline is assumed to be at equal intervals. For example, a Sparkline might display a daily account balance, sales by month, or profits by year. But what if the data isn't at equal intervals?
Figure 22.12 shows data, by date, along with a Sparklines graphic created from column B. Notice that some dates are missing, but the Sparkline shows the columns as if the values were spaced at equal intervals.
To better depict the data, the solution is to specify a date axis. Select the Sparkline and choose Sparkline Tools Design Group Axis Date Axis Type. Excel displays a dialog box, asking for the range that contains the dates. In this example, specify range A2:A11. Click OK, and the Sparkline displays gaps for the missing dates (see Figure 22.13).
If a Sparkline uses data in a normal range of cells, adding new data to the beginning or end of the range does not force the Sparkline to use the new data. You need to use the Edit Sparklines dialog box to update the data range. (Choose Sparkline Tools Design Sparkline Edit Data.) But if the Sparkline data is in a column within a table (created by choosing Insert Tables Table), the Sparkline will use new data that's added to the end of the table.
Figure 22.14 shows an example. The Sparkline was created using the data in the Rate column of the table. When you add the new rate for September, the Sparkline will automatically update its Data Range.
The example in this section describes how to create a Sparkline that displays only the most recent data points in a range. Figure 22.15 shows a worksheet that tracks daily sales. The Sparkline, in merged cells E4:E5, displays only the seven most recent data points in column B. When new data is added to column B, the Sparkline will adjust to show only the most recent seven days of sales.
I started by creating a dynamic range name. Here's how:
=OFFSET($B$2,COUNTA($B:$B)-7-1,0,7,1)
This formula calculates a range by using the OFFSET
function. The first argument is the first cell in the range (B2). The second argument is the number of cells in the column (minus the number to be returned and minus 1 to accommodate the label in B1).
This name always refers to the last seven nonempty cells in column B. To display a different number of data points, change both instances of 7
to a different value.