Analyzing Data with Tables and Charts in Microsoft Excel 2013
- 6/15/2013
- Sorting out your data analysis options
- Using tables to organize and analyze data
- Sorting and filtering data
- Using conditional formatting to highlight cells based on their content
- Using sparklines to visualize trends within a range
- Turning data into charts
Using sparklines to visualize trends within a range
If you think of sparklines as tiny charts that fit in a single cell, you won’t be too far from the truth. This feature, introduced in Excel 2010, enables you to visualize a data series in a single cell. The following example shows quarterly results over a period of several years, with a sparkline at the end of each row that shows the up and down gyrations over each year.
The easiest way to apply sparklines to a range of data is to select the range and then open the Quick Analysis tool, as shown here.
Sparklines come in three varieties: Line and Column work almost exactly like their full-size chart counterparts, while Win/Loss shows an up or down marker depending on whether the associated data is positive (win) or negative (loss).
To add a sparkline for a selection other than the full data range, click Line, Column, or Win/Loss from the Sparklines group on the Insert tab. If you made a selection first, the Data Range box is filled in with that range; you just need to select the cell where you want the sparkline to appear.
To change an existing sparkline, use the Design tab under the Sparkline Tools heading (it’s only visible when you select one or more cells containing a sparkline). Figure 13-14 shows the commands available on this tab.
)
Figure 13-14 As with a full-size chart, you can use commands on this specialized tab to change the style of a sparkline, add data markers, and edit the source data.
Most of the options on the Design tab are self-explanatory. One that deserves special attention is the Axis command, which allows you to customize how each axis in the minichart is treated. Normally, each sparkline is treated as an independent series, with values charted using only the data in its source data range. If you want Excel to chart multiple sparklines using the same range of values, click Axis, and then change the selections under Vertical Axis Minimum Value Options and Vertical Axis Maximum Value Options to Same For All Sparklines.