Excel Sparklines

Put some spark in your chart!

Excel Sparklines were introduced in Excel 2010 and are great for displaying the trend of data over time and making sense of a sea of numbers. Just take this before and after example:

Before

A sea of numbers that would take a long time to analyse and compare:

After

Instantly we can get a feel for the numbers without having to read each row and compare them against one another. In fact we don't even need to see the month values, just a total and an average per Salesperson would be enough to support the Sparklines.

And because Sparklines fit in a single cell they’re ideal for dashboards, which have limited space.

The term Sparkline was coined by Edward Tufte and he describes them as "intense, simple, word-sized graphics".

Download the Workbook

Excel Sparklines - Types

There are 3 Sparkline types to choose from including Line, Column and Win/Loss, which you’ll find on the Insert tab of the ribbon:

Inserting Sparklines

Excel Sparklines occupy a cell so the first thing you should do is select the cell, or cells, you want them inserted into (this will prepopulate the Location Range in the dialog box) > Insert tab > choose the Sparkline type you want.

This will open the ‘Create Sparklines’ dialog box and you can select the range of cells containing the data for your Sparklines:

Notes:

1. The data can be on different sheet to your Sparkline.
2. You can change the Location Range if you forget to first select the cells where you want your Sparklines to go.
3. Your Data Range can be organised horizontally or vertically, although I find it clearer if it’s arranged horizontally like in the example file.

Formatting Sparklines

Clicking on a Sparkline will activate the contextual Sparkline Tools tab for Design:

Here you can edit the location and source data, add markers for various points, choose from pre-set Styles, modify colours and axis settings.

To activate the Sparkline: Design contextual tab simply select a cell containing a Sparkline.

Grouping

When you select a range of cells before inserting them they are automatically grouped. You can tell they’re grouped because when you select one, those in the group have a blue border around them.

When they’re grouped any formatting changes are automatically applied to all Sparklines in the group.

Sparkline Tips

• Less is more; While Sparklines occupy a single cell you can still use that cell to enter data, apply Conditional Formatting and other things you’d typically use a cell for, but don’t get carried away. Too much in the one cell could result in the message being lost.
 
• Use adjacent cells for more detail as opposed to using the cell which contains the Sparkline. Instead, I think you’re better off using adjacent columns for any additional data like the YTD total or average etc.

  

• Use Markers sparingly; try highlighting the lowest or highest points if you think it will add value, but be careful it doesn't end up looking like a Christmas tree!

  

• Give them space – adjust the row height to give them more space and make it easier to read.

  

• Fix Axes – particularly with column Sparklines where comparisons from one set of sparklines to the next can be misleading if they all start from a different point. I recommend setting them to start at zero (assuming there aren’t any negative values in the data).

  

• Sparklines can also occupy columns in Tables, and when new rows are added to the Table the Sparkline also gets copied down automatically.

  

• Copy & Paste; you can copy or cut and paste a Sparkline to other cells, and you can use the Fill Down tool to add more Sparklines.

Please Share

If you liked this or know someone who could use it please click the buttons below to share it with your friends on LinkedIn, Google+, Facebook and Twitter.