How to Show Trends Visually with Excel 2010's Sparklines

Excel 2010 introduced sparklines, a new type of information graphic that represents trends or variations in collected worksheet data. Sparklines are tiny graphs (miniature charts) that fit within a single cell in the worksheet. Sparklines can be any one of the following three chart types:

  • Line that represents the relative value of the selected 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 and red squares (representing the losses) that appear below.

To add sparklines to the cells of your worksheet:

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

  2. On the Insert tab, click the chart type you want for your sparkline (Line, Column, or Win/Loss) in the Sparklines group.

    Excel opens the Create Sparklines dialog box, which contains two text boxes: Data Range, which shows the cells you selected with the data you want to graph; and Location Range, where you designate the cell or cell range where you want the sparkline to appear.

  3. Click the Location Range text box, select the cell or range of cells where you want your sparkline to appear, and then click OK.

    When creating a sparkline that spans 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 sparklines can depict the data visually and enhance meaning while being an integral part of the table.

The figure below shows a worksheet table after adding sparklines to the table's final column. These sparklines depict the variation in the sales over four quarters as tiny line graphs. When you add sparklines to your worksheet, Excel 2010 adds a Sparkline Tools Design tab to the Ribbon. This Design tab contains buttons that you can use to edit the type, style, and format of the sparklines.

In this worksheet, sparklines represent data variations as tiny line charts.
In this worksheet, sparklines represent data variations as tiny line charts.
blog comments powered by Disqus

Inside Sweepstakes

Win $500. Easy.