Chapter 22: Creating Sparkline Graphics
In This Chapter
Introducing the Sparkline graphics feature
Adding Sparklines to a worksheet
Customizing Sparklines
Making a Sparkline display only the most recent data
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.
See Chapters 19 and 20 for information about real charts.
This chapter introduces Sparklines and presents examples that demonstrate how they can be used in your worksheets.
Note
Sparklines were introduced in 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.
On the Web
All examples in this chapter are available at this book's website. The filename is sparkline examples.xlsx.
Sparkline Types
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.1
Three groups of Sparklines.
• 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 22.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 22.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.
Creating Sparklines
Figure 22.2 shows some data to be summarized with Sparklines. To create Sparkline graphics, follow these steps:
Figure 22.2
Data to be summarized with Sparklines.
1. Select the data that will be depicted (data only, not column headings); if you're creating multiple Sparklines, select all the data. In this example, start by selecting B4:M12.
2. With the data selected, choose Insert ⇒ Sparklines, and click one of the three Sparkline types: Line, Column, or Win/Loss. The Create Sparklines dialog box, shown in Figure 22.3, appears.
Figure 22.3
Use the Create Sparklines dialog box to specify the data range and the location for the Sparkline graphics.
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 doesn't 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.
4. 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. Often, you'll want to increase the column width or row height to improve the readability of the Sparklines.
Tip
Most of the time, you'll create Sparklines on the same sheet that contains the data. If you want to create Sparklines on a different sheet, start by activating the sheet where the Sparklines will be displayed. Then, in the Create Sparklines dialog box, specify the source data either by pointing or by typing the complete sheet reference (for example, Sheet1A1:C12). The Create Sparklines dialog box lets you specify a different sheet for the Data Range, but not for the Location Range. Or, you can just create the Sparklines on the same sheet as the data, and then cut and paste the cells to a different worksheet.
Figure 22.4 shows column Sparklines for the precipitation data.
Figure 22.4
Column Sparklines summarize the precipitation data for nine cities.
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.
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 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.
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 (an empty cell) is displayed as a gap in the graphic.
Figure 22.5
A Sparkline at various sizes.
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.
Figure 22.6
The Hidden and Empty Cell Settings dialog box.
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.
Tip
If you've customized the appearance, Excel remembers your customization settings for each type if you switch among Sparkline types.
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. Therefore, 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.
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 can't change the size of the markers in Line Sparklines.
Figure 22.7 shows some Line Sparklines with various types of highlighting applied.
Figure 22.7
Highlighting options for Line Sparklines.
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.
Note
Sparklines don't actually display a vertical axis, so you're essentially adjusting an invisible axis.