Suppose you work for a large bank with 1,000 branches. Each week you need to summarize the daily customer counts for each branch. You could create a line chart with 1,000 lines (one for each branch), but that would be cluttered beyond belief. Fortunately, beginning with Excel 2010, Excel allows you to create sparklines, which summarize a row or column of data with a chart in a single cell. The workbook Sparklines.xlsx illustrates the use of sparklines.
Figure 1 (see the worksheet “Line”) shows an example of sparklines.
Figure 1: Line sparklines
In rows 8-14, columns D-H show the daily customer count for 7 bank branches. The line sparkline charts in I8:I14 summarize the daily customer counts for each branch. You can see that for each branch, Friday is the busiest day and either Tuesday or Wednesday is the least busy day of the week. To create these line sparklines proceed as follows:
1. Select the range I8:I14 and from the insert tab select line from the sparklines group.
2. Select the data range for the sparklines by filling in the create sparklines dialog box as shown in Figure 2.
Figure 2: Creating line sparklines.
3. When your cursor is in any cell containing a sparkline, the sparkline option will appear on the ribbon. Selecting sparkline from the ribbon brings up the menu shown in figure 3.
Figure 3: Sparkline menu
From this menu, we checked the high Point and low Point to ensure that for each row the busiest and least busy day were highlighted. You can also change the color of the sparklines and/or the color of the high and low point markers.
Column sparklines
In the worksheet “Column,” we created Column sparklines (see Figure 4) and highlighted the high and low Points. To create the column sparklines choose I9:I14 and then choose column from the sparkline group and then choose the high and low points to be marked.
Figure 4: Column sparklines
Watch your scaling!
In the worksheet “Wrong Scaling” (see Figure 5) we reduced the number of customers visiting the New York branch. Our column sparklines don’t reflect the reduction. For example, the New York sparkline indicates that on Friday, New York has roughly the same number of customers as the other branches, and this is not the case.
Figure 5: New York customers reduced.
The problem is that the default setting for sparklines is that each sparkline is scaled against only the data used to create that sparkline. We want each sparkline to be scaled against all the data. To accomplish this goal, from the sparkline menu (see figure 6) select axis and change the vertical axis minimum and maximum value settings to same for all sparklines.
Figure 6: Settings to scale all sparklines the same
As shown in worksheet “Right Scaling” (see figure 7), our sparklines now correctly show that New York is not nearly as busy as the other branches.
Figure 7: Correctly scaled sparklines
Read as we discuss win loss sparklines, which are a great way to summarize how to compare your organization’s actual and targeted performances.
