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:
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".
Download the Workbook
Enter your email address below to download the sample 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:
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.
If you liked this or know someone who could use it please click the buttons below to share it with your friends on LinkedIn, Facebook and Twitter.
Any ideas on how to construct an “inverted” sparkline in which ascending and descending values are reversed? I have a spreadsheet with Billboard Top 100 music chart data by week, and I’m trying to figure out whether it’s possible to have the “maximum” value be 1 and the “minimum” value be 100. It doesn’t seem like that’s possible with the built-in function, but maybe I’m missing something. Or maybe there’s a chart-based workaround that can be quickly applied to thousands of rows…
You can’t reverse the Sparkline, but you can multiple all your values by -1 so that they display the correct way in the Sparkline. You can also hide the sign using a custom number format that formats positive and negative values without a sign e.g. 0;0
Hope that helps.
Really like the sparklines.
Just a question. I’m running a macro in my spreadsheet that goes through a slicer and prints a pdf for every slicer item. The thing is that the sparklines are not changing, in every pdf the’re the same like in the first one. Is there a way to get this fixed?
Thank you in advance.
Are you able to share your file with me so I can see the set up? You can post your question and file on our Excel Forum. Anonymize the data if necessary.
I added a new topic (Sparklines pdf printing with vba) in VBA and Macros, and attached a short version of my file.
Really great tutorial. Appreciate the effort. I did not know that is so simple. Thanks
Thanks, Biji. Glad you liked it.
I totally agree on your second tips – Use adjacent cells for more detail
Normally, I will put the sparkline in-between the starting and ending points so that reader can spot the trend as well as where do we come from quickly, without asking. 🙂
Cheers, MF 🙂
Mynda, as I was reading this article about sparklines, I began to understand the level of effort you put into doing your articles. I want to thank you for that effort, as I will use your suggestions in my daily work. I just want to give acknowledgement to the level of help you give to your readers.
Thank you, and keep up your excellent work!
Thanks, Chris. It’s nice of you to take the time to say so 🙂 I appreciate it.