In my previous post (link here) I outlined what a Sparkline was and how to insert one into your worksheet. In today’s post I’d like to look at how to customise a sparkline.
Once you have inserted a Sparkline into your worksheet, you will see a new group of tools appear on the Ribbon. The Sparkline Tools > Design tab will allow you to edit various aspects of any Sparkline.
This tab is referred to as a “contextual tab” which means it will only appear on the Ribbon when a cell containing a Sparkline is selected, or in other words, it will appear only in the context of what it’s used for. As soon as you select a different cell the tab will disappear from view.
It’s important to be aware that if you have used the AutoFill technique to create Sparklines for an entire column or row (referred to as a Sparkline group), any changes to various aspects of one sparkline will automatically fill through to those subsequent sparklines within the group. Later in this post I will show you how to Ungroup Sparklines to allow you to edit each individually.
Edit the data included in a Sparkline
If you wish to add or remove data being included in a particular Sparkline, you can easily do that using the Sparkline Tools > Design tab.
- From the Sparkline Tools > Design tab, click the Edit Data button
- The Edit Sparklines dialog box will appear
- Make the appropriate changes to either the Data Range or the Location Range as needed
- Click OK
Change the type of Sparkline
If you wish to change the type of sparkline you have included, you do not need to delete the existing sparkline and create a new one, you can simply edit the type using the Sparkline Tools > Design tab.
- From the Sparkline Tools > Design tab, locate the Type group
- Change the type to your preferred option
- The Sparkline will update within the cell(s)
Highlight various aspects of a Sparkline
Excel allows you to highlight various aspects of a Sparkline to make the data stand out and appear more defined. Use the Show group to highlight aspects including High Point, Low Point, Negative Points, First and Last Point and all Markers.
- From the Sparkline Tools > Design tab, locate the Show group
- Place a tick in each option you wish to enable and you will see the Sparkline update automatically
Change the Style of a Sparkline
By default any Sparkline you insert will use the default colour schemes, but let’s be honest, they can be a bit boring. To “jazz up” your Sparkline use the Style gallery to choose a different colour scheme.
- From the Sparkline Tools > Design tab, locate the Style group
- You can scroll through the Style gallery using the arrows or use the Sparkline Color to choose a custom colour
- You can also customise the colour of the markers which are displayed from the Show group using the Marker Color drop down gallery
- Your Sparkline will update once you have selected a new style/colour
Adjust the Group settings
The Group objects give you the ability to adjust multiple settings within the sparkline.
- From the Sparkline Tools > Design tab, locate the Group group
- Click the Axis button to see the options available
- By default any new Sparklines will be set to General Axis Type
- If you are using data based on dates then select the Date Axis Type so that your Sparkline will demonstrate any gaps in your data based on dates. You will be prompted to select the data range containing the date values. In the example below you can see the dates are 1, 2, 5, 6, 10, 11, 12 January. The data has gaps within the dates and by using the Date Axis Type you can now see corresponding gaps in the Sparkline.
- If you have any negative values in your data then you can click Axis > Show Axis so that it is easy to identify zero in the values
- If you wish to change the direction of the plotted data, click Axis > Plot Data Right to Left
- If you wish to adjust the scale of values shown in the Sparkline, you can do this using the Custom Value options for both the Vertical Axis Minimum Value Options and Vertical Axis Maximum Value Options
Group or Ungroup Sparklines
If you have created a sparkline and then used the AutoFill tool to copy the sparkline to other cells you will find that the sparklines are now grouped together. Any formatting changes made to a Sparkline within the group will see the change applied to all sparklines within the group. Whilst this does have its advantages you may need to be able to edit sparklines individually. You can do this by ungrouping sparklines from the main group. Vice versa, if you’ve created sparklines individually and wish to edit them as a group, you can easily perform this.
- Select the cell containing a sparkline within the group
- From the Sparkline Tools > Design tab, click the Ungroup button
- You will see a blue line appear around the grouped Sparkline’s while the cell you had selected will now be separated from the rest of the grouped Sparkline’s
Delete a Sparkline
To delete a Sparkline you will need to use the Clear button. Selecting a cell containing a Sparkline and pressing Delete on the keyboard does not remove
- Select the cell(s) containing a Sparkline you wish to delete
- From the Sparkline Tools Design tab select the Clear button and choose between Clear Selected Sparklines or Clear Selected Sparkline Groups
I hope these tips and been helpful for utilising the Sparkline feature within Excel. If you have any questions please do not hesitate to contact me.