Skip to main content

How to use Sparklines in Excel 2010

If you have a certain kind of brain, you may be able to look at rows of numbers, spot trends and visualise the information they convey. For the rest of us, a little assistance is welcome. Excel 2010 includes a new facility, Sparklines, which helps a lot in seeing the 'shapes' of the numbers.

Put simply, Sparklines are miniature graphs and charts, which fit within single cells of a spreadsheet. They don't have the sophistication of Excel's full charts and graphs, but they're not nearly as involved to put together, either. Not much harder than using AutoSum, in fact.

Take this sample spreadsheet, which breaks down the sales in a fictional company, by region. How easy do you find it to look at the trends in any region over the last five years? To make it easier, try this, on the row for Northern sales. Click in a cell at the end of the row, G3 in the example, open the Insert tab and click Line in the Sparklines group. A dialog opens and if you drag across cells B3 to F3 and click OK, a simple Sparkline, an unadorned line graph, is drawn in G3. At the basic level, that's all there is to it.

It's easy to make the Sparkline clearer to read and more attractive to look at. In the Sparkline Tools Design tab, start by clicking on Markers, High Point and Low Point. This puts blobs at each node on the line and marks the highest and lowest node. To make them a bit clearer, click on Marker Color and select High Point and Low Point in turn, to give the nodes colours.

You can replicate Sparklines in the same way you can formulas. Drag down from cell G3 to cover the sales rows from the other regions and values from the relative cells are automatically used to create new Sparklines.

The second type of Sparkline is the Column, and this can be created in exactly the same way as the line variant. Select the cell to take it - G17 in the screenshot, as we added some blank rows to avoid the ribbon pull-down hiding it - and Insert Column Sparkline. The result is a small column graph, using a default colour scheme.

There's a selector in the ribbon for colour styles, which highlights the high and low points, as in the Line version. This makes it very quick and easy to select a colour scheme, which helps these features stand out.

The third Sparkline is the Win Loss, which shows up positive and negative values very clearly, using blocks either above or below the axis in the graph. If you go to the Axis options menu in the Sparkline Tools Design tab, you can choose to show the axis, to accentuate this plus/minus distinction.

Also in this menu is an option for Date Axis. This is useful if you have time-based data, where some days, months or years have no corresponding data value. With Date Axis selected these 'empty' periods will be shown as spaces, giving a more faithful view of the information.