Sparklines

By utilizing icons in the previous section, we can easily see if each period had an acceptable increase or if we need to be alarmed. What if we want to look for a trend? Are we steadily increasing? Decreasing? It is for these questions that Sparklines can provide value. Imagine a tiny little chart next to our set of numbers. When the focus is the numbers themselves, adding these tiny little charts (aka Sparklines) can give enough visual information to know where you need to focus your attention. Let's add them now.

Preparing for Sparklines

We'll add some Sparklines to our revenue and net income data. First, let's set up the formatting.

1. Open the GP 2016 Dashboard.xlsx file that we've been working with.
2. Click on the Dashboard tab to open the worksheet.
3. Select cell I7. This should be the cell next to the header for period 4.
4. Type Quick Trend in the cell I7 and widen the cell to ensure that it fits.
5. Select cell H7.
6. Select the Home tab and click on the paintbrush icon to copy the formatting:
   
7. Click on cell I7 to apply the formatting:
   

Adding Sparklines

Now that we're set up, let's build a Sparkline using these steps:

1. Highlight cells E7 through H7. These are the amounts on the Revenue line.
2. Click on Insert to open the insert ribbon.
3. In the Sparklines section, click on Line.
4. The Create Sparklines box will open. The Data Range box will be prepopulated with the highlighted cells.
5. Select the Location box and click on the lookup button next to it.
6. Pick cell I7 and hit Enter:
   
7. Click on OK to create the Sparkline:
   

Adding a Sparkline to cell I7 creates a small line graph in that cell. It's a great way to get a quick read on how the data has changed. Let's add another Sparkline to the Net Income line. This time, we'll add a Win/Loss chart to show negatives.

To add a Sparkline to the Net Income row as shown in the screenshot, follow these steps:

1. Highlight cells E8 through H8.
2. Click on Insert to open the insert ribbon.
3. In the Sparklines section, click on Win / Loss.
4. The Create Sparklines box will open. The Data Range box will be pre-populated with the highlighted cells.
5. Select the Location box and click on the lookup button next to it.
6. Select cell I8 and hit Enter.
7. Click on OK to create the Sparkline.
8. Select the Sparkline in cell I8.
9. The tab for the Sparkline Tools Design ribbon lights up. At this point, you can opt to change the color scheme if you are so inclined (perhaps to match your logo):
   
10. Right-click and select Format Cells.
11. Pick the Border tab.
12. Click on the Outline and Inside boxes to put lines around the Sparkline cells.
13. Click on OK to finish.
14. Save the file:
    

Tip

The Sparkline Tools Design tab lights up when you select a Sparkline. It contains additional tools to improve the usability of Sparklines. Here is an example.

Go to Sparkline Tools Design | Edit Data | Hidden and Empty Cells. This will allow you to control blank data points. You can show a gap in your Sparkline, default that point to zero, or simply connect lines by skipping the gap.

With the options in the Show section, you can mark data points such as the high and low points, first and last points, and negative numbers.

Sparkline idiosyncrasies

Sparklines are an interesting combination of charts and cells. This combination creates some idiosyncrasies when working with them. As we wrap up Sparklines, we'll look at a few.

Deleting Sparklines

Since Sparklines reside in a cell, you might think that you can simply hit the Delete button and remove a Sparkline, but you can't. To remove a Sparkline, select the Sparkline, right-click, and go to Sparklines | Clear Selected Sparklines.

Changing Sparkline data

Sparklines are attached to a selection of cells in a row. In our case, we don't have them directly connected to a pivot table. Our revenue and income boxes are intentionally not dynamic, so putting a Sparkline to the right works. If we connect a Sparkline to a pivot table row, changes to the pivot table can cause it to grow beyond the location of the Sparkline. The pivot table overlaps the Sparkline, and the Sparkline appears behind the overlapping data. Usually, this isn't ideal. If you're connecting a Sparkline directly to a pivot table, consider adding it to the left or farther out to the right.

Since Sparklines are based on a range, adding data to a cell outside the range will not be picked up by a Sparkline. For example, if Sparkline data runs from cells E7 to H7, adding data in D7 will not change the Sparkline. Adding a cell in the middle of the range will update a Sparkline. Named ranges can be used when creating a Sparkline to help overcome this limitation.

Like any other cell, Sparklines can be cut, copied, and pasted to a different cell. The cells they are in can be formatted with backgrounds and borders.