What are Excel Sparklines & How to use them? [Excel 2010]
As part of Excel 2010, Microsoft has introduced an exciting and new charting feature called as “sparklines”. Edward Tufte coined the term sparkline and defined it as,
intense, simple, word-sized graphics
I think adding sparklines to Excel’s pack of visualizations is a huge step in the right direction. Sparklines (often called as micro-charts) add rich visualization capability to tabular data without taking too much space. While MS’ implementation of Sparklines in Excel 2010 leaves a lot to be desired, it provides a solid platform and introduces this powerful charting idea to masses.
What is a sparkline?
A Sparkline is a small chart that is aligned with rows of some tabular data and usually shows trend information.
Here is an example of sparklines in a project team status report.
How to create sparklines in Excel 2010?
Creating sparklines in excel 2010 is very easy. You follow 3 very simple steps to get beautiful sparklines in an instant.
- Select the data from which you want to make a sparkline.
- Go to Insert > Sparkline and select the type of sparkline (you have 3 options – line, column and win-loss chart)
- Specify a target cell where you want the sparkline to be placed
- Optional: Format the sparkline if you want.
Here is a short screen-cast showing you how a sparkline is created.
Types of Sparklines in Excel 2010:
There are 3 basic types of sparklines in Excel 2010. They are,
- Line chart
- Column chart
- Win-loss chart (useful for showing a bunch of wins & losses denoted by 1s and -1s)
Sparkline Formatting and Options – Explored
In excel 2010, you will find a new ribbon called as “Sparklines – Design” ribbon. This is where all the formatting options for sparklines are included. Some of the key formatting / customizations you can do are,
- Change the sparkline type
- Change the source data / target cells of sparkline
- Set different colors for first point, last point, highest & lowest points (applicable for column and line chart types)
- Set axis options (show / hide axis, set min and max value for vertical axis, set axis type to date axis etc.)
- Group / un-group a bunch of sparklines (you can change formatting options, axis settings en-masse when you group sparklines)
- Remove sparklines
Sparklines & Missing Data – How does it work?
- If the sparkline source data contains non-numeric data, they are neglected while plotting the sparklines.
- If data has some #NA values, they are neglected
- If data has blanks, sparkline shows blanks too
- If data has zeros, zero value is plotted
- If data has some hidden rows / columns, the values are neglected (unless you enable “Show data in hidden cells” option)
Sparklines in Tables & Pivot Tables
You can add sparklines to tables and pivot tables too. Adding them to pivot tables is a bit tricky (I will write about that when I master the trick) but adding sparklines to tables is fairly straightforward and scales nicely.
Sparkline Tips & Tricks
Here is a bunch of quick tips & tricks for those of you starting on sparklines.
- You can auto-fill sparklines. Select the first set of values and add a sparkline. Now copy and past sparklines to auto-fill them based on data in adjacent cells.
- When you adjust row-height or column-width of the cell containing sparkline, the size of sparkline changes too.
- Juxtapose sparklines with conditional formatting icons to create stunning charts and dashboards.
- If you want to copy a sparkline over to a ppt or document, you can use “copy as picture” option.
- The sparklines feature is disabled whenever you open a workbook in “compatibility mode”.
What happens when someone opens a file with sparklines in Excel 2007?
When you open a file with sparklines in excel 2007, the sparklines are not shown.
How does Sparklines compare with other alternatives?
Sparklines vs. SfE:
One of the most popular alternative to Excel Sparklines is a free and open source add-in called as Sparklines for Excel or SfE. SfE is a really powerful and adds a ton of more features than what is supported by Excel 2010′s Sparklines. However, since it is an add-in, one should install it before enjoying the features. Here is a detailed comparison between SfE and Sparklines in Excel 2010.
Sparklines vs. In-cell Charts
In-cell charts are a powerful and lightweight way to create bite-sized visualizations. The main technique is to use REPT formula to repetitively show a bunch of symbols (usually | symbol) to create a small chart. The advantage of this approach is that they work in any version of Excel. But the dis-advantage is that we can make only few types of charts (bar charts, column charts by rotating cell text, dot plots). Also, incell charts require some knowledge of excel formulas and creativity.
This is where Excel Sparklines shine, as they are very easy to create and maintain.
Sparklines vs. Conditional Formatting
In Excel 2007, MS introduced a bunch of useful Conditional Formatting options like icons, heat maps that effectively create small visualizations of underlying data. These features are further improved in Excel 2010. While conditional formatting based visualizations are easy to implement and scale very well, there are only few options (a bunch of traffic lights, data bars etc.). This could leave you high and dry if you are looking for rich visualization options. these new features require the actual data to be present in underlying cells (which is a head-ache).
Again, sparklines shine as a simpler and easier alternative.
Sparklines vs. Shrinking an actual chart
We can take an actual chart, strip it of all the clothing (remove gridlines, axis, legend, titles, labels etc.) and resize it so that it fits nicely in a cell [example]. This is the easiest and cleanest way to get sparklines in earlier versions of excel. However this approach has one problem. It doesn’t scale. (ie if you want to get 2 sparklines, you had to do twice the work). Of course, we can write some macros to take care of that, but if you are open to macros, you might as well use SfE and save a lot of trouble. But this approach of shrinking a real chart is better as it gives you full power to customize the underlying chart (add multiple series etc.) which is not available in excel sparklines.
Conclusions on Sparklines
The latest sparklines in Excel 2010 is certainly a great step forward in the world of data visualization. It brings ease and consistency to most users who want better visualizations but do not know how to create them. That said, the implementation still leaves a lot to be desired (atleast bullet graphs and may be box plots should have been added) . But I think MS wanted to test the waters and build a solid foundation based on which they can improve the sparklines.
On a lighter note, Kudos to Office Team at MS for not adding any 3D capabilities to these sparklines. That would have unleashed a fresh dose of chart monsters.
I am really looking forward to using the sparklines in some of the real world visualizations or dashboards (May be I will include them in the next version of project management templates).
What about you? What are your thoughts on sparklines? Have you used them? What is your experience like? Please share your ideas, impressions and tips thru comments.
You can win a copy of MS Office 2010 – Home & Student Edition by leaving a comment on this post.
Additional Resources on Sparklines:
- Sparklines vs. SfE Free Add-in – a detailed comparison [PTS Blog]
- Formatting Sparklines [MSDN Blog]
- Sparklines for Excel – a fantastic Add-in for generating sparklines in all versions of Excel
- Sparkline Mkr – a dead simple way to get sparklines [Chandoo.org]
- In-cell charting – resources, examples and download [Chandoo.org]
|What is new in Microsoft Excel 2010? [Office 2010 Week]||New Features in Excel 2010 Conditional Formatting|