How to Use Sparklines in Excel 2016 - dummies

How to Use Sparklines in Excel 2016

By Greg Harvey

Excel 2016 supports a special type of information graphic called a sparkline that represents trends or variations in collected data. Sparklines are tiny graphs generally about the size of the text that surrounds them. In Excel 2016, sparklines are the height of the worksheet cells whose data they represent and can be any of the following chart types:

  • Line that represents the relative value of the selected worksheet data

  • Column where the selected worksheet data is represented by tiny columns

  • Win/Loss where the selected worksheet data appears as a win/loss chart; wins are represented by blue squares that appear above red squares (representing the losses)

Sparklines via the Quick Analysis tool

In Excel 2016, you can use its Quick Analysis tool to quickly add sparklines to your data. All you have to do is select the cells in the worksheet to be visually represented and click the Quick Analysis tool followed by Sparklines on its options palette. This displays buttons for the three types of sparklines: Line, Column, and Win/Loss. To preview how your data looks with each type, highlight the button in the palette with the mouse pointer or Touch Pointer. Then, to add the previewed sparklines to your worksheet, simply click the appropriate Sparklines button.

This figure shows the sample Mother Goose Enterprises worksheet with the first quarter sales for 2016 after I selected the cell range B3:D11 and then opened the Sparklines tab in the Quick Analysis tool’s palette. Excel immediately previews line-type trendlines in the cell range E3:E11 of the worksheet. To add these trendlines, all you have to do is click the Line option in the tool’s palette.

Previewing sparklines to visually represent the trends in the three-month sales for each company in
Previewing sparklines to visually represent the trends in the three-month sales for each company in the Quick Analysis tool’s Sparklines palette.

Sparklines from the Ribbon

You can also add sparklines with the Sparklines command buttons on the Insert tab of the Ribbon. To manually add sparklines to the cells of your worksheet:

  1. Select the cells in the worksheet with the data you want to represent with sparklines.

  2. Click the chart type you want for your sparklines (Line, Column, or Win/Loss) in the Sparklines group of the Insert tab or press Alt+NSL for Line, Alt+NSO for Column, or Alt+NSW for Win/Loss.

    Excel opens the Create Sparklines dialog box containing two text boxes:

    • Data Range: Shows the cells you select with the data you want to graph.

    • Location Range: Lets you designate the cell or cell range where you want the sparklines to appear.

  3. Select the cell or cell range where you want your sparklines to appear in the Location Range text box and then click OK.

    When creating sparklines that span more than a single cell, the number of rows and columns in the location range must match the number of rows and columns in the data range. (That is, the arrays need to be of equal size and shape.)

Because sparklines are so small, you can easily add them to the cells in the final column of a table. That way, the sparkline graphics (as shown) can depict the data visually and enhance meaning while being an integral part of the table.

Formatting sparklines

After you add sparklines to your worksheet, Excel 2016 adds a Sparkline Tools contextual tab with its own Design tab to the Ribbon that appears when the cell or range with the sparklines is selected.

This Design tab contains buttons that you can use to edit the type, style, and format of the sparklines. The final group (called Group) on this tab enables you to band a range of sparklines into a single group that can share the same axis and/or minimum or maximum values (selected using the options on the Axis drop-down button). This is very useful when you want a collection of sparklines to share the same charting parameters so that they represent the trends in the data equally.

You can’t delete sparklines from a cell range by selecting the cells and then pressing the Delete button. Instead, to remove sparklines, right-click their cell range and select Sparklines→Clear Selected Sparklines from its context menu. If you’ve selected a grouped collection of sparklines to remove, select the Clear Selected Sparkline Groups option instead.