Reveal Hidden Data Trends with Excel Sparklines

Reveal Hidden Data Trends with Excel Sparklines

By Marie McCooey

Do you have a worksheet of Excel data, for example, 10 years of company sales or student test scores where you’d like to quickly view the data’s trends?

Let me introduce you to Sparklines.

What is a Sparkline?

Edward Tufte coined the term “Sparkline” and defined it as “intense, simple, word-sized graphics.”

Sparklines are mini cell charts

It’s much easier to view Sales data trends with Sparklines. Compare and contrast the Sparklines to a standard chart with the same data shown below.

Standard Excel Chart

3 Sparkline Options

Excel provides three Sparkline types to display your data trends over time: Line, Column, and Win/Loss.

Click the Insert tab and select the desired Sparkline type.

Sparkline Types

How to Easily Add Sparklines to Your Worksheets

Place each Sparkline in a cell adjacent to the data.

To insert a Sparkline:

  1. Select the cell or cells (J4:J21) where the Sparkline(s)  should be inserted.
    This step populates the Location Range.
  2. Select Insert on the Ribbon, then select the Sparkline type.
    The Create Sparklines window displays with the Location Range populated.Select Sparkline Data
  3. Select the Sparkline’s data cells, (D4:I21) and click OK.
    The Sparklines display in the selected Location Range cells.Sparkline Data Displayed

Notes:

  • Your data and the associated Sparklines can be located on different worksheets in the file.
  • If you forget to select the Sparklines Location range first, you can select the range after you’ve selected the data.

Formatting Sparklines

Easily customize how Sparklines appear in your worksheet, including the Sparkline’s markers, style, type, and more.

  1. Click on a Sparkline to activate the Sparkline Tools Design tab.
  2. Use the Edit Data button to:
  • Adjust the location of Sparkline data
  • Determine how to handle Hidden and Empty cells
  1. Identify Markers to display in the Show section.
  2. Select Line Styles, Sparkline and Marker colors and weight in the Style section.
  3. Choose axis options with the Axis button.

Grouping Sparklines

If you select a range of cells before you insert the Sparklines, the Sparklines are automatically grouped.

To determine if your Sparklines are grouped, select a single Sparkline.
If the group displays a blue border as shown below, the Sparklines are grouped.

Sparkline Group

Warning: When Sparklines are grouped any formatting change you add is applied to all the Sparklines in the group.

To group Sparklines on a worksheet:

  1. Select a Sparkline.
  2. Hold the CTRL key down and select other Sparklines on the same worksheet.
  3. On the Sparkline Tools Design tab, select Group.
    Any formatting applied to one Sparkline in the group is applied to ALL Sparklines in the group.
  4. To ungroup the Sparklines, select the Sparkline group and select Ungroup on the Sparkline Tools Design tab.

Deleting Sparklines

To delete a Sparkline:

  1. Select 1 or more Sparklines.
  2. On the Sparkline Tools Design tab, in the Group section, click the Clear button to delete the Sparklines:

Clear Sparklines

Sparkline Tips

Sparklines can be auto-filled

  1. Insert a Sparkline for the first set of values.
  2. Next, drag the Fill Handle to auto-fill the Sparklines based on the data in the adjacent cells.

AutoFill Sparklines

Keep Markers to a Minimum

To avoid extra clutter on Sparklines, consider adding markers to the lowest or highest points only.

Sparkline Marker Tip

Adjust for Readability

Adjust the column width and row height of the Sparklines to make them easier to read.

Sparkline Row Adjustment

Add Sparklines to Tables

Add Sparklines to a column in a Table.
When you add new rows to the Table, the Sparklines copy down automatically.

Sparklines in Table


An Added Bonus
: Sparklines are included in worksheet printouts, unlike charts which you must print separately.

The next time you’re looking for trends in your data, add a Sparkline!