Chapter 4
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.
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-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.
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.
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.
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.
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.
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:
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.
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.
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.
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.
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.
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).
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.
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.
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.
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:
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.
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.
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.
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.
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
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.
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.
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.
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.
18.118.217.124