Chapter 21. Creating Sparkline Graphics
An introduction to the new Sparkline graphics feature
How to add Sparklines to a worksheet
How to customize Sparklines
How make a Sparkline display only the most recent data
One of the new features in Excel 2010 is Sparklines graphics. A Sparkline is a small chart displayed in a single cell. A Sparkline allows you to quickly spot time-based trends or variations in data. Because they are so compact, Sparklines are often 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. See Chapters 18 and 19 for information about real charts.
This chapter introduces Sparklines, and presents examples that demonstrate how they can be used in your worksheets.
NOTE
Sparklines are new to Excel 2010. If you create a workbook that uses Sparklines, and that workbook is opened using a previous version of Excel, the Sparkline cells will be empty.
NOTE
All examples in this chapter are available on the companion CD-ROM. The filename is sparkline examples.xlsx.
21.1. Sparkline Types
Excel 2010 supports three types of Sparklines. Figure 21.1 shows examples of the three types of Sparkline graphics, displayed in column H. Each Sparkline depicts the six data points to the left.
Line: Similar to a line chart. As an option, the line can display with a marker for each data point. The first group in Figure 21.1 shows line Sparklines, with markers. A quick glance reveals that with the exception of Fund Number W-91, the funds have been losing value over the six-month period.
Column: Similar to a column chart. The second group in Figure 21.1 shows the same data displayed with column Sparklines.
Win/Loss: A "binary" type chart that displays each data point as a high block or a low block. The third group shows win/loss Sparklines. Notice that the data is different. Each cell displays the change from the previous month. In the Sparkline, each data point is depicted as a high block (win) or a low block (loss). In this example, a positive change from the previous month is a win, and a negative change from the previous month is a loss.
Figure 21.1. Three groups of Sparklines.
21.2. Creating Sparklines
Figure 21.2 shows some data to be summarized with Sparklines. To create Sparkline graphics, follow these steps:
With the data selected, choose Insert
Sparklines, and click one of the three Sparkline types: Line, Column, or Win/Loss. Excel displays the Create Sparklines dialog box, as shown in Figure 21.3.Specify the location for the Sparklines. Typically, you'll put the Sparklines next to the data, but that's not a requirement. Most of the time, you'll use an empty range to hold the Sparklines. However, Excel does not prevent your from inserting Sparklines into cells that already contain data. The Sparkline location that you specify must match the source data in terms of number of rows or number of columns. For this example, specify N4:N12 as the Location Range.
Click OK. Excel creates the Sparklines graphics of the type you specified.
The Sparklines are linked to the data, so if you change any of the values in the data range, the Sparkline graphic will update.
Figure 21.3. Use the Create Sparklines dialog box to specify the data range and the location for the Sparkline graphics.
Understanding Sparkline GroupsMost of the time, you'll probably create a group of Sparklines — one for each row or column of data. A worksheet can hold any number of Sparkline groups. Excel remembers each group, and you can work with the group as a single unit. For example, you can select one Sparkline in a group, and then modify the formatting of all Sparklines in the group. When you select one Sparkline cell, Excel displays an outline of all the other Sparklines in the group. You can, however, perform some operations on an individual Sparkline in a group:
Both operations are available from the shortcut menu that appears when you right-click a Sparkline cell. You can also ungroup a set of Sparklines by selecting any Sparkline in the group and choosing Sparkline Tools |
Design 
Sparkline 
. Edit Data 
Edit Single Sparkline's Data. Excel displays a dialog box that lets you change the data source for the selected Sparkline.
Design 
Group 
Clear 
Clear Selected Sparklines.
Design 
Group 
Ungroup. After you ungroup a set of Sparklines, you can work with each Sparkline individually.Figure 21.4 shows column Sparklines for the precipitation data.
Figure 21.4. Column Sparklines summarize the precipitation data for nine cities.
21.3. Customizing Sparklines
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.
21.3.1. Sizing Sparkline cells
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 21.5 shows the same Sparkline, displayed at four sizes resulting from column width, row height, and merged cells.
Figure 21.5. A Sparkline at various sizes.
21.3.2. Handling hidden or missing data
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 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 21.6), specify how to handle hidden data and empty cells.
Figure 21.6. The Hidden and Empty Cell Settings dialog box.
21.3.3. Changing the Sparkline type
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.
|
21.3.4. Changing Sparkline colors and line width
After you've created a Sparkline, changing the color is easy. Use the controls in the Sparkline Tools 
Design 
Style group.
NOTE
Colors used in Sparkline graphics are tied to the document theme. Thus, if you change the theme (by choosing Page Layout 
Themes 
Themes), the Sparkline colors will change to the new theme colors. See Chapter 6 for more information about document themes.
For Line Sparklines, you can also specify the line width. Choose Sparkline Tools 
Design 
Style 
Sparkline Color 
Weight.
21.3.5. Highlighting certain data points
Use the commands in the Sparkline Tools 
Design 
Show group to customize the Sparklines to highlight certain aspects of the data. The options are
High Point: Apply a different color to the highest data point in the Sparkline.
Low Point: Apply a different color to the lowest data point in the Sparkline.
Negative Points: Apply a different color to negative values in the Sparkline.
First Point: Apply a different color to the first data point in the Sparkline.
Last Point: Apply a different color to the last data point in the Sparkline.
Markers: Show data markers in the Sparkline. This option is available only for Line Sparklines.
You control the color of the highlighting by using the Marker Color control in the Sparkline Tools 
Design 
Style group. Unfortunately, you cannot change the size of the markers in Line Sparklines.
Figure 21.7 shows some Line Sparklines with various types of highlighting applied.
Figure 21.7. Highlighting options for Line Sparklines.
21.3.6. Adjusting Sparkline axis scaling
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 21.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.
Figure 21.8. The bottom group of Sparklines shows the effect of using the same axis minimum and maximum values for all Sparklines in a group.
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 apparent.
The axis scaling option you choose depends upon what aspect of the data you want to emphasize.
21.3.7. Faking a reference line
One useful feature that's missing in the Excel 2010 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, to transform the data and then use a Sparkline axis as a fake reference line. Figure 21.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 21.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)
Figure 21.9. Sparklines display the number of pages read per month.
Figure 21.10. Using Win/Loss Sparklines to display goal achievement.
I copied this formula to the other cells in 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 cannot tell whether the student missed the goal by 1 page or by 500 pages.
Figure 21.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.
Figure 21.11. The axis in the Sparklines represents the goal.
21.4. Specifying a Date Axis
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 aren't at equal intervals?
Figure 21.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.
Figure 21.12. The Sparkline displays the values as if they are at equal time 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 21.13).
Figure 21.13. After specifying a date axis, the Sparkline shows the values accurately.
21.5. Auto-Updating Sparklines
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 using Insert 
Tables 
Table), then the Sparkline will use new data that's added to the end of the table.
Figure 21.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.
Figure 21.14. Creating a Sparkline from data in a table.
21.6. Displaying a Sparkline for a Dynamic Range
The example in this section describes how to create a Sparkline that display only the most recent data points in a range. Figure 21.15 shows a worksheet that tracks daily sales. The Sparkline, in cell F4, displays only the seven most recent data points in column B.
Figure 21.15. Using a dynamic range name to display only the last seven data points in a Sparkline.
Need More about Sparklines?This chapter describes pretty much everything there is to know about Excel 2010 Sparklines. You may be left asking, Is that all there is? Unfortunately, it is. The Sparklines feature in Excel 2010 certainly leaves much to be desired. For example, you're limited to three types (Line, Column, and Win/Loss). It would be useful to have access to other Sparkline types, such as a column chart with no gaps, an area chart, and a stacked bar chart. Although Excel provides some basic formatting options, many users would prefer to have more control over the appearance of their Sparklines. If you like the idea of Sparklines — and you're disappointed by the implementation in Excel 2010 — check out some add-ins that provide Sparklines in Excel. These products provide many additional Sparkline types, and most provide many additional customization options. Search the Web for sparklines excel, and you'll find several add-ins to choose from. |
I started by creating a dynamic range name. Here's how:
Choose Formulas
Defined Names 
Define Name, specify Last7 as the Name, and enter the following formula in the Refers To field:=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 non-empty cells in column B. To display a different number of data points, change both instances of 7 to a different value.
Chose Insert
Sparklines 
Line.In the Data Range field, type Last7 (the dynamic range name). Specify cell E4 as the Location Range. The Sparkline shows the data in range B11:B17.
Add new data to column B. The Sparkline adjusts to display only the last seven data points.