3 Easy Ways to Customize Your Sparklines in Excel

A sparkline is a tiny chart in the background of a cell. When you use a sparkline, you can easily see the relationships between it and its underlying data. And, you can copy sparklines to adjacent cells by using the Fill Handle. Sparklines are printed as-is when you print a worksheet that contains them, unlike charts that may require special print handling.

Insert a Sparkline

• Select the cells that contain the data you want in the sparkline chart.
• Select the type of sparkline you want from the Sparklines group [A] on the Insert tab.
• In the Create Sparklines dialog box, the cell or range you have selected will be filled in the Data Range: text box [B].
• Type where you want your sparkline to go in the Location Range text box [C], or click the destination cell. A selection box will appear around it.
• Click the OK button.

• The sparkline will appear in the position you designated [D] and the Sparkline Tools highlighted ribbon [E] will become available when the cell is selected.

• Copy the sparkline to additional rows or columns using the Fill Handle.
• Select the sparkline you want to copy, then hover over the Fill Handle until your curser turns to a cross [F].
• Drag the handle over the cells you want to fill. Sparklines will appear in each of the cells [G].

Pro Tip: Use the Quick Analysis tool to quickly add a sparkline to your data.

Customize Excel Sparklines

After your sparklines have been created, Excel offers several tools for you to control which value points are shown, set options on the vertical axis, and define how empty values are displayed. You can also change colors, apply styles from Office’s pre-loaded style gallery or apply a custom style that you have saved.

Change Value Points

1. Click on any cell that contains a sparkline the click on the sparkline tab.
2. In the Show group [A], check the points you would like to add to your line.

·        High Point and Low Point: Adds color-coded markers to your sparkline at the highest and/or lowest data point.

·        First Point and Last Point: Adds color-coded markers to the first and last data points.

·        Negative Points: Adds color-coded markers to negative values.

·        Markers: Adds a marker at every data point.

Note that the style options in the Style group [B] are updated when you make changes in the Show group.

1. Review your results [C].

Customize the Style or Format of Sparklines

1.     Select the sparkline or sparkline group you wish to change.

2.     In the Style group [D] of the Design tab, choose the style you want. To see more styles, click the more button [E] in the bottom right corner of the selection box.

3.     To customize how High and Low Points, First and Last Points, Markers and Negative Points are displayed, click on the Marker Color button [F] in the Style Click on the marker you want to change [G], then choose a color from the fly-out menu.

4.     To change the color of the line in a line graph, click Sparkline Color [H] and make your selection from the pull-down menu. To change the thickness of the sparkline, click on Weight [I] and select the point size you want.

5.     Review your changes [J].

Delete a Sparkline or Sparkline Group

What Microsoft Calls It: Clear sparkline

1. Select the sparkline or sparkline group you wish to delete.
2. In the Group section on the Design tab, click the Clear combo button [K] to remove the selected sparklines.
3. To remove an entire sparkline group, click on the Clear dropdown button, then select Clear Selected Sparkline Groups [L].

Caution: If you select a single cell that is part of a sparkline group, ALL of the sparklines within that group will be affected when you make formatting changes.

Hot Tip: If your sparklines are too small to see detail, adjust the row height or Column width.