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.”
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.
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.
How to Easily Add Sparklines to Your Worksheets
Place each Sparkline in a cell adjacent to the data.
To insert a Sparkline:
- Select the cell or cells (J4:J21) where the Sparkline(s) should be inserted.
This step populates the Location Range.
- Select Insert on the Ribbon, then select the Sparkline type.
The Create Sparklines window displays with the Location Range populated.
- Select the Sparkline’s data cells, (D4:I21) and click OK.
The Sparklines display in the selected Location Range cells.
- 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.
Easily customize how Sparklines appear in your worksheet, including the Sparkline’s markers, style, type, and more.
- Click on a Sparkline to activate the Sparkline Tools Design tab.
- Use the Edit Data button to:
- Adjust the location of Sparkline data
- Determine how to handle Hidden and Empty cells
- Identify Markers to display in the Show section.
- Select Line Styles, Sparkline and Marker colors and weight in the Style section.
- Choose axis options with the Axis button.
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.
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:
- Select a Sparkline.
- Hold the CTRL key down and select other Sparklines on the same worksheet.
- 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.
- To ungroup the Sparklines, select the Sparkline group and select Ungroup on the Sparkline Tools Design tab.
To delete a Sparkline:
- Select 1 or more Sparklines.
- On the Sparkline Tools Design tab, in the Group section, click the Clear button to delete the Sparklines:
Sparklines can be auto-filled
- Insert a Sparkline for the first set of values.
- Next, drag the Fill Handle to auto-fill the Sparklines based on the data in the adjacent cells.
Keep Markers to a Minimum
To avoid extra clutter on Sparklines, consider adding markers to the lowest or highest points only.
Adjust for Readability
Adjust the column width and row height of the Sparklines to make them easier to read.
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.
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!