Working with Sparklines Excel 2010

Introduction

Sparklines are miniature charts that fit into a single cell. Because they're so compact, you can place a large number of them in your worksheets. For example, you could place one sparkline on each row to show trends within that row. In this lesson, you will learn how to insert sparklines and change their type and appearance.

Creating sparklines

Video: Creating Sparklines in Excel 2010

Sparklines were introduced in Excel 2010 to be a convenient alternative to charts. Unlike a traditional chart, a sparkline is placed inside a cell, allowing you to easily create a large number of sparklines (for example, one on each row).

Optional: You can download this example for extra practice.

Types of sparklines

There are three types of sparklines: Line, Column, and Win/Loss. Line and Column work the same as line and column charts. Win/Loss is similar to Column, except it only shows whether each value is positive or negative instead of how high or low the values are. All three types can display markers at important points—such as the highest and lowest points—to make them easier to read.

Why use sparklines?

Sparklines are basically charts, so why would you want to use sparklines instead of charts? Sparklines have certain advantages that make them more convenient in many cases. Let's say you have 1,000 rows of data. If you place a sparkline on each row, it will be right next to its source data, making it easy to see the relationships between the numbers and the sparkline. If you used a traditional chart, it would need to have 1,000 data series in order to represent all of the rows, and you would probably need to do a lot of scrolling to find relevant data in the worksheet.
Sparklines are ideal for situations where you want to make the data clearer and more eye catching, and where you don't need all of the features of a full chart. On the other hand, charts are ideal for situations where you want to represent the data in greater detail, and they are often better for comparing different data series.

To create sparklines:

Generally, you will have one sparkline for each row, but you can create as many as you want in any location you want. Just like with formulas, it's usually easiest to create a single sparkline and then use the fill handle to automatically create sparklines for the remaining rows.

Select the cells you will need for the first sparkline. In this example, we are creating a sparkline for Kathy Albertson, so we'll select her sales data.
Click the Insert tab.
In the Sparklines group, select Line. A dialog box will appear.
Make sure the insertion point is next to Location Range.
Click the cell where you want the sparkline to be. In this example, we'll select the cell to the right of the selected cells.
Click OK. The sparkline will appear in the document.
Click and drag the fill handle downward.
Sparklines will be created for the remaining rows.

Changing the appearance of your sparklines

To show points on the sparkline:

Certain points on the sparkline can be emphasized with markers—or dots—making the sparkline more readable. For example, in a line with a lot of ups and downs, it may be difficult to tell which ones are the highest and lowest points, but if you show the High Point and Low Point it will be easy to identify them.

Select the sparklines you want to change. If they are grouped, you only need to select one of them.
Locate the Show group in the Design tab.
Hover over the different check boxes to see a description of each one.
Check each option you want to show. The sparklines will update to show the selected options.

To change the style:

Select the sparklines you want to change.
Locate the Style group in the Design tab.
Click the More drop-down arrow to show all of the available styles.
Select the desired style.
The sparklines will update to show the selected style.

To change the sparkline type:

Select the sparklines you want to change.
Locate the Type group in the Design tab.
Select the desired type (Column, for example).
The sparkline will update to reflect the new type.

Some sparkline types will be better or worse for certain types of data. For example, Win/Loss is best suited for data where there may be positive and negative values (such as net earnings).

Changing the display range

By default, each sparkline is scaled to fit the maximum and minimum values of its own data. This allows it to fill the entire cell, no matter how high or low the values are. However, it has a downside: If you are trying to compare several sparklines, you won't be able to tell at a glance which have higher or lower values. The solution is to make the display range the same for all of the sparklines.

To change the display range:

Select the sparklines you want to change.
In the Design tab, click the Axis command. A drop-down menu will appear.
Under Vertical Axis Minimum Value Options and Vertical Axis Maximum Value Options, select Same for All Sparklines.
The sparklines will update to reflect the new range.