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:
A sea of numbers that would take a long time to analyse and compare:
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”.
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:
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:
- The data can be on different sheet to your Sparkline.
- You can change the Location Range if you forget to first select the cells where you want your Sparklines to go.
- Your Data Range can be organised horizontally or vertically, although I find it clearer if it’s arranged horizontally like in the example file.
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.
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.
- 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.
Learn to present data beautifully via Charts ; Dashboards here.