SK BIZ SOLUTION: Showing Data As A Graphic

Unlike charts on an Excel worksheet, sparklines are not objects — a sparkline is actually a tiny chart in the background of a cell. The following picture shows a column sparkline in cell F2 and a line sparkline in F3. Both of these sparklines get their data from cells A2 through E2 and display a chart inside a cell that shows the performance of a stock. The charts show the values by quarter, highlight the high value (3/31/08) and the low value (12/31/08), show all the data points, and show the downward trend for the year.

Adding Sparklines

Select an empty cell or group of empty cells in which you want to insert one or more sparklines.
On the Insert tab, in the Sparklines group, click the type of sparkline that you want to create: Line, Column, or Win/Loss.
In the Data box, type the range of the cells that contain the data on which you want to base the sparklines.

Note: You can click to temporarily collapse the dialog box, select the range of cells that you want on the worksheet, and then click to restore the dialog box to its normal size.

About the Sparkline Tools Tab

When one or more sparklines are selected, the Sparkline Tools appear, displaying the Design tab. On the Design tab, you can choose one or more of several commands from among the following groups: Sparkline, Type, Show/Hide, Style, and Group.

Use these commands to create a new sparkline, change its type, format it, show or hide data points on a line sparkline, or format the vertical axis in a sparkline group. These options are described in detail in the following section.

Editing Sparkline Data

Control which value points are shown

You can highlight individual data markers (values) in a line sparkline by making some or all of the markers visible.

In this sparklines, the high-value marker is green, and the low-value marker is orange. All other markers are shown in black.

To show all values, select the Markers check box.
To show negative values, select the Negative Points check box.
To show the highest or the lowest values, select the High Point or Low Point checkboxes.
To show the first or the last values, select the First Point or Last Point checkboxes.

Change the style of or format sparklines

Use the Style gallery on the Design tab, which becomes available when you select a cell that contains a sparkline.

Select a single sparkline or a sparkline group.
To apply a predefined style, on the Design tab, in the Style group, click a style or click the arrow at the lower right corner of the box to see additional styles.
To apply specific formatting to a sparkline, use the Sparkline Color or the Marker Color commands.

Show or hide data markers

On a sparkline that has the Line style, you can show data markers so that you can highlight individual values.

Select a sparkline.
In the Show/Hide group, on the Design tab, select any of the checkboxes to show individual markers (such as high, low, negative, first, or last), or select the Markers checkbox to show all markers.

Clearing a check box hides the specified marker or markers.

Show and customize axis settings

You can select Date Axis Type (in the Group group, click Axis) to format the shape of the chart in a sparkline to reflect any irregular time periods in the underlying data.

In a line sparkline, applying the Date Axis type can change the slope of a plotted line and the position of its data points in relation to each other.
In a column sparkline, applying the Data Axis type can change the width of, and increase or decrease the distance between the columns.

In the example shown here, there are two column sparklines that use data from the same range. The sparkline with the “Trend” label uses the General Axis type, and the sparkline with the “Trend (Data Axis Type)” label uses the Date Axis type. In each sparkline, the first two data points are separated by two months, and the second and third are separated by seven months. By applying the Date Axis type, the space between the three columns changes proportionally to reflect the irregular time periods.

You can also use these Axis options to set minimum and maximum values for the vertical axis of a sparkline or sparkline group. Setting these values explicitly helps you control the scale so that the relationship between values is shown in a more meaningful way.

With the sparkline or sparkline group selected, in the Group group, click Axis.
Under Vertical Axis Minimum Value Options or Vertical Axis Maximum Value Options, click Custom Value.
Set minimum or maximum values that you feel will best emphasize the values in the sparklines.

You can increase the height of the row that contains the sparkline to more dramatically emphasize the difference in data values if some are very small and some are very large.

You can also use the Plot Data Right-to-Left option to change the direction in which data is plotted in a sparkline or sparkline group.

If there are negative values in your data, you can emphasize this by showing a horizontal axis in your sparkline.

With the sparkline or sparkline group selected, in the Group group, click Axis.
Under Horizontal Axis Options, click Show Axis. Any sparklines that contain negative data will display a horizontal axis at 0.

Handle empty cells or zero values

You can control how a sparkline handles empty cells in a range (and thus how the sparkline is displayed) by using the Hidden and Empty Cell Settings dialog box.