What are some of the options that can be customized with sparklines?

In our previous blog post we have learned to insert Sparklines in excel. Now, we will go little advanced in this blog post and learn to customize the Sparklines. If you have not visited our blog post on inserting Sparklines in excel then please click here before proceeding for this blog post, as we are going to use the same data which we have used previously.

We have crated 03 types of Sparklines: Line, Column and Win/Loss as shown in below image.

To customize Sparklines in excel, we have to first enable the “Design” tab on the ribbon. Design tab will get enabled if and only if we click on the Sparklines. If we click on any portion where Sparklines are not created then Design tab will get disabled.

Once we click on the “Design Tab” then we can see the commands which are divided in various groups. In this blog post of “Customizing the Sparklines”, we will see all the commands “Show Group” which are highlighted with Pink color and the commands to change the “Sparkline Color” and “Marker Color” as highlighted with Brown color in below image.

Changing Sparkline Color:

1. To change the color of Sparklines, select the already crated Sparklines. This will enable Design tab which contains command “Sparkline Color” as highlighted in below image.

2. Click on “Sparkline Color” command and select the color of your own choice. In this example, we have selected color “Orange, Accent 6, Darker 25%”.

3. You can see the difference as compared to original data. Once we check the checkbox for the Marker, it will highlight each point in the selected group of Line Sparklines with Purple color.

Excel’s Sparklines were introduced in Excel 2010, and are a neat way to add mini data visualisations, which sit within a cell or range of cells.

Examples above show (1) a basic sparkline in a single cell, (2) a line with markers across two cells, (3) a column sparkline and (4) a win-loss chart, both spread across 3 cells

Links to topics on this page

Reasons to use Sparklines

• Quick to set up;
• Easy to select line, win/loss or column sparkline;
• Aligned with other cell content;
• Several charts on one page with no-fuss alignment;
• Create a group of charts which can be quickly customized together;
• Small, clean design giving a quick overview;
• Highlighting key elements, such as min and max values
• Viewing and comparing data trends;
• Sparkline graphs can fill a single cell, or be easily resized to fill multiple cells.

Method — create a sparkline (see video below)

• Enter the data for the line, win/loss or column sparkline into the worksheet (the sparkline can be created first, but it’s easier to start off with some data already entered).
• Select the cell to contain the sparkline.
• From the ‘Insert’ tab on the ribbon, in the ‘Sparklines‘ section, select ‘Line‘, ‘Column‘ or ‘Win/Loss‘.
• In the ‘Create Sparklines‘ pop-up dialog box, select the cell range for the data, and click OK.
• The mini chart will appear in the cell.
• To change between chart types, simply select the sparkline, and choose another option from Line, Column or Win/Loss.

Add a column sparkline in Excel and increase the sparkline chart size by spreading across multiple cells, using ‘Merge & Center’

---

• Excel will only allow a sparkline to be first created in a single cell — if a range of cells is entered into the ‘Location Range‘ field in the ‘Create Sparklines‘ dialog, an error message will be triggered.
• So, , create a sparkline in a single cell.
• Select the sparkline cell and drag to cover additional cells to expand the sparkline into — across columns and also down rows if required. Don’t drag by the right-hand corner as this will copy the sparkline to another cell(s).
• Click on ‘Merge and center‘ (from the ‘Home‘ tab, or from the context menu when the selected range is right-clicked), and the chart will now span the new cell selection.
• To change the size again:
  1. select the sparkline
  2. click on ‘Merge and center‘ (which will unmerge the cells)
  3. make the new selection of cells for the chart to span
  4. click on ‘Merge and center‘.
• To keep a sparkline within one row and to increase its depth, simply increase the row height (as shown in the video above).

Customize sparklines — colors and line widths

• The colors of sparkline columns, lines and markers are customizable.
• Select the sparkline and from the ‘Sparklines‘ section on the ribbon, choose color options from the ‘Sparkline Color‘ menu.
• Different colors can be chosen for high-low, first-last points for line markers, and bars.
• Line color and width are selected from the ‘Sparkline Color‘ dropdown menu.

Amend data values and data range

• To amend the cell range of the data loaded into the chart, select the sparkline and either:
  • right-click and select ‘Sparklines | ‘Edit Single Sparkline’s data‘, or
  • from the ribbon select ‘Edit Data‘.
• Adjust the cell range that contains the data for the sparkline.
• To amend values in the data range, simply amend the data and the sparkline will refresh with the new data, as for other chart types in Excel.

Sparkline axis options

• Sparkline axis options include:
  • setting a date type scale;
  • show axis (will show if the minimum value of the axis is set to zero, or there are negative as well as positive values in the single or group of sparklines);
  • plot data right-to-left
  • set minimum and maximum values for the vertical (y) axis, and set for all sparklines in a group or for individual sparklines.

Sparkline groups (see video below)

---

• A group of sparklines can be created, as well as single sparklines. The main benefit is to customize all the charts together.
• The video above starts with a block of data, and with the first sparkline already created, spanning two columns (using the ‘Merge & Center’ command).
• To add additional sparklines, drag the right-hand corner of the sparkline down to fill the cells below.
• Each sparkline picks up the data in its row.
• When one of the sparkline charts is selected, a (vertical) boundary box highlights the grouped sparklines.
• Choose axis, marker and color options which will be applied to all the charts.

Delete sparklines — how to clear selected sparklines from a worksheet

• There’s a specific command to delete a sparkline — you can’t just select the sparkline chart and press ‘Delete‘.
• Select the sparkline to delete.
• From the ‘Design‘ tab, select ‘Clear‘ and then either ‘Clear Selected sparkline‘, or ‘Clear Selected Sparkline Groups‘.

Limitations of Sparklines

• Sparklines can’t have annotations like data values or other labels;
• The size of markers on line sparklines is determined by the line width, so not customizable by size or shape;
• The axis color isn’t customizable and will only display if there are negative as well as positive values.

What are the different types of sparklines in Excel?

What are sparklines and what are they used for?

What type of data can be used to create a single sparkline?

Which two methods can you use to specify where the sparklines should appear on a worksheet?