Chapter 4
Sparking Inspiration with Sparklines
In This Chapter
Understanding the Excel Sparkline feature
Adding sparklines to a worksheet
Customizing sparklines
Working with groups of sparklines
This chapter introduces you to sparklines. These magically named visualizations are essentially mini word-size charts placed in and among the textual data in tables. Sparklines enable you to see, at a glance, trends and patterns within your data using minimal real estate on your dashboard.
Before getting into the nuts-and-bolts of using sparklines, you should understand exactly how they can enhance your reporting. This chapter introduces you to the concept of sparklines and then shows you how to customize and add them to tables.
Introducing Sparklines
As I mention in Chapter 3, much of the reporting done in Excel is table-based, in which precise numbers are more important than pretty charts. However, in table-based reporting, you often lose the ability to show important aspects of the data such as trends. The number of columns needed to show adequate trend data in a table makes it impractical to do so. Any attempt to add trend data to a table usually does nothing more than render your report unreadable.
In the example in Figure 4-1, the data represents a compact KPI (key performance indicator) summary designed to be an at-a-glance view of key metrics. Although the table compares various time periods (in columns D, E, and F), it does so only by averaging, which tells you nothing about trends over time. It quickly becomes evident that seeing a full-year trend would be helpful.
Figure 4-1: Although this KPI summary is useful, it cannot show a full-year trend.
Figure 4-2 illustrates the same KPI summary with Excel sparklines added to visually show the 12-month trend. With the sparklines added, you can see the broader story behind each metric. For example, based solely on the numbers, the Passengers metric appears to be up slightly from the average. But the sparkline tells the story of a heroic comeback from a huge hit at the beginning of the year.
Figure 4-2: Sparklines allow you to add trending in a compact space, enabling you to see a broader picture for each metric.
Again, it’s not about adding flash and pizzazz to your tables. It’s about building the most effective message in the limited space you have. Sparklines are another tool you can use to add another dimension to your table-based reports.
Sparklines are available only with Excel 2010-subsequent versions. When you open a workbook with sparklines using a pre-2010 version of Excel, the sparkline cells are empty. If your organization is not fully using Excel 2010 or greater, you may want to search for alternatives to the built-in Excel sparklines. Many third-party add-ins bring sparkline features to earlier versions of Excel. Some of these products support additional sparkline types, and most have customization options. Search the web for sparklines excel, and you’ll find several add-ins to choose among.
Understanding Sparklines
Although sparklines look like miniature charts (and can sometimes take the place of a chart), this feature is completely separate from the Excel chart feature (covered in Chapters 7, 8, and 9 of this book). For example, charts are placed on a worksheet’s drawing layer, and a single chart can display several series of data. In contrast, a sparkline is displayed inside a worksheet cell and displays only one series of data.
Excel supports three types of sparklines: Line, Column, and Win/Loss. Figure 4-3 shows examples of each type of sparkline graphic, displayed in column H. Each sparkline depicts the six data points to the left.
- Line: Similar to a line chart, the Line type of sparkline can appear with or without a marker for each data point. The first group in Figure 4-3 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 6-month period.
- Column: Similar to a column chart, the second group shows the same data with Column sparklines.
- Win/Loss: A Win/Loss sparkline is 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 4-3: Three types of sparklines.
Creating sparklines
Figure 4-4 shows some weather data that you can summarize with sparklines. To create sparkline graphics for the values in these nine rows, follow these steps:
-
Select the data range that you want to summarize. In this example, select B4:M12.
If you are creating multiple sparklines, select all the data.
- With the data selected, click the Insert tab on the Ribbon and find the Sparklines group.
-
On the Insert tab, select any one of the three sparkline types — Line, Column, or Win/Loss — from the Sparklines group. In this case, select the Column option.
Excel displays the Create Sparklines dialog box, as shown in Figure 4-5.
-
Specify the data range and the location for the sparklines. For this example, specify N4:N12 as the Location Range.
Typically, you put the sparklines next to the data, but that’s not required. Most of the time, you use an empty range to hold the sparklines. However, Excel does not prevent you from inserting sparklines into filled-in cells. The sparkline location that you specify must match the source data in terms of number of rows or number of columns.
-
Click OK.
Excel creates the sparklines graphics of the type you specified, as shown in Figure 4-6.
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 4-4: Data that you want to summarize with sparkline graphics.
Figure 4-5: Use the Create Sparklines dialog box to specify the data range and the location for the sparkline graphics.
Figure 4-6: Column sparklines summarize the precipitation data for nine cities.
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 selecting the cell range or by typing the complete sheet reference (for example, Sheet1!A1:C12). The Create Sparklines dialog box lets you specify a different sheet for the Data Range, but not for the Location Range.
Understanding sparkline groups
Most 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 other sparklines in the group.
You can, however, perform some operations on an individual sparkline in a group:
- Change the sparkline’s data source. Click the sparkline cell and go to the Sparkline Tools tab on the Ribbon. There, you can click 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.
- Delete the sparkline. Click the sparkline, click the Sparkline Tools tab on the Ribbon, and then select Design ⇒ Group ⇒ Clear ⇒ Clear Selected Sparklines.
Both operations — changing the sparkline’s data source and deleting the sparkline — are available from the shortcut menu that appears when you right-click a sparkline cell.
You can also ungroup a set of sparklines. Select any sparkline in the group and choose Design ⇒ Group ⇒ Ungroup from the Sparkline Tools tab. After you ungroup a set of sparklines, you can work with each sparkline individually.
You can add a new sparkline to an existing group by first selecting any sparkline in the existing group and then choosing Design ⇒ Edit Data ⇒ Edit Group Location & Data. This opens the Edit Sparklines dialog box. Simply edit the Data Range and Location Range to include the new data you want to add.
Customizing Sparklines
When you activate a cell that contains a sparkline, Excel displays an outline around all sparklines in its group. You can then use the commands on the Sparkline Tools ⇒ Design tab to customize the group of sparklines.
Sizing and merging sparkline cells
When you change the width or height of a cell that contains a sparkline, the sparkline adjusts to fill the new cell size. In addition, you can put a sparkline into merged cells. To merge cells, select at least two cells and choose Home ⇒ Alignment ⇒ Merge & Center from the Ribbon.
Figure 4-7 shows the same sparkline displayed in four sizes, resulting from changing column width and row height and from merging cells.
Figure 4-7: A sparkline at various sizes.
It’s important to note that a skewed aspect ratio can distort your visualizations, exaggerating the trend in sparklines that are too tall and flattening the trend in sparklines that are too wide. Generally speaking, the most appropriate aspect ratio for a chart is one where the width of the chart is about twice as long as the height. In Figure 4-7, the sparkline with the most appropriate aspect ratio is the one located in cell M4.
If you merge cells and the merged cells occupy more than one row or one column, Excel doesn’t let you insert a group of sparklines into those merged cells. Rather, you need to insert the sparklines into a normal range (with no merged cells) and then merge the cells.
You can also put a sparkline in non-empty cells, including merged cells. Figure 4-8 shows two sparklines that occupy merged cells alongside text that describes the graphics.
Figure 4-8: Sparklines in merged cells (E2:I7 and E9:I14).
Handling hidden or missing data
In some cases, you simply want to present a sparkline visualization without the numbers. One way to do this is to hide the rows or columns that contain the data. Figure 4-9 shows a table with the values displayed and the same table with the values hidden (by hiding the columns).
Figure 4-9: Sparklines can use data in hidden rows or columns.
By default, if you hide rows or columns that contain data used in a sparkline graphic, the hidden data does not appear in the sparkline. In addition, blank cells are displayed as a gap in the graphic.
To change these default settings, go to the Sparkline Tools tab on the Ribbon and select Design ⇒ Sparkline ⇒ Edit Data ⇒ Hidden & Empty Cells. In the Hidden and Empty Cell Settings dialog box, you can specify how to handle hidden data and empty cells.
Changing the sparkline type
As mentioned earlier in this chapter, 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 clicking the sparkline and selecting one of the three icons located under Sparkline Tools ⇒ Design ⇒ Type. If the selected sparkline is part of a group, all sparklines in the group are changed to the new type.
If you’ve customized the appearance, Excel remembers the customization settings for each sparkline type if you switch among different ones.
Changing sparkline colors and line width
After you create a sparkline, changing the color is easy. Simply click to select the sparkline, click to open the Sparkline Tools tab on the Ribbon, and select Design ⇒ Style. There, you find various options to change the color and style of the sparkline.
For Line sparklines, you can also specify the line width. Choose Sparkline Tools ⇒ Design ⇒ Style ⇒ Sparkline Color ⇒ Weight.
Colors used in sparkline graphics are tied to the document theme. If you change the theme (by choosing Page Layout ⇒ Themes ⇒ Themes), the sparkline colors then change to the new theme colors.
Using color to emphasize key data points
Use the commands under Sparkline Tools ⇒ Design ⇒ Show to customize the sparklines to emphasize key aspects of the data. These options are in the Show group:
- 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 can control the color of sparkline markers 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.
Adjusting sparkline axis scaling
When you create one or more sparklines, they all use (by default) automatic axis scaling. In other words, Excel determines the minimum and maximum vertical axis values for each sparkline in the group based on the numeric range of the sparkline data.
The Sparkline Tools ⇒ Design ⇒ Group ⇒ Axis command lets you override this automatic behavior and control the minimum and maximum values 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.
Axis scaling can make a huge difference in the sparklines. Figure 4-10 shows two groups of sparklines. The group at the bottom uses the default axis settings (Automatic For Each Sparkline option). Each sparkline in this group shows the 6-month trend for the product but not the magnitude of the values.
Figure 4-10: The bottom group of sparklines shows the effect of using the same axis minimum and maximum values for all sparklines in a group.
The sparkline group at the bottom (which uses the same data) uses the Same for All Sparklines setting for the minimum and maximum axis values. 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 on what aspect of the data you want to emphasize.
Faking a reference line
One useful feature that’s missing in 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.
One approach is to write formulas that transform the data and then use a sparkline axis as a fake reference line. Figure 4-11 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 6-month page data, but it’s impossible to tell who exceeded the goal or when they did it.
Figure 4-11: Sparklines display the number of pages read per month.
The lower set of sparklines in Figure 4-12 shows another approach: Transform the data such that meeting the goal is expressed as a 1, and failing to meet the goal is expressed as a –1. The following formula (in cell B18) transforms the original data:
=IF(B6>$C$2,1,-1)
This formula was copied to the other cells in the B18:G25 range.
Using the transformed data, Win/Loss sparklines are used to visualize the results. This approach is better than the original, but it doesn’t convey magnitude differences. For example, you cannot tell whether the student missed the goal by 1 page or by 500 pages.
Figure 4-13 shows a better approach. Here, the original data is transformed by subtracting the goal from the pages read. The formula in cell B30 is
=B6-C$2
Figure 4-12: Using Win/Loss sparklines to display goal status.
Figure 4-13: The axis in the sparklines represents the goal.
This formula was copied to the other cells in the B30:G37 range, and a group of Line sparklines displays the resulting values. This group has the Show Axis setting enabled and also uses Negative Point markers so that the negative values (failure to meet the goal) clearly stand out.
Specifying a date axis
By default, 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 4-14 shows data, by date, along with a sparklines graphic created from column B. Notice that some dates are missing but that the sparkline shows the columns as though the values were spaced at equal intervals.
Figure 4-14: The sparkline displays the values as though they are at equal time intervals.
To better depict this type of time-based 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 corresponding dates. In this example, specify range A2:A11.
Click OK, and the sparkline displays gaps for the missing dates, as shown in Figure 4-15.
Figure 4-15: After you specify a date axis, the sparkline shows the values accurately.
Autoupdating sparkline ranges
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 (Sparkline Tools ⇒ Design ⇒ Sparkline ⇒ Edit Data).
However, if the sparkline data is in a column within a table object (created using Insert ⇒ Tables ⇒ Table, as described in Chapter 2), the sparkline uses new data that’s added to the end of the table without requiring an update.
Figure 4-16 shows an example. The sparkline was created using the data in the Rate column of the table, which covers the range from January to August. If you were to add the new rate for September, the sparkline would automatically update its data range.
Figure 4-16: Creating a sparkline from data in a table.