Back in November I came across a new blog, called Data Driven Consulting. On this blog I found articles covering some of my favorite topics, including graphics and data visualization. I snooped around and discovered that the author of the blog, Alex Kerin, lives only 20 miles away from me here in the snow belt of Massachusetts. We got together over lunch to discuss our mutual interests, and the first outcome of our meeting is this post which Alex has prepared for the Peltier Tech blog.
Alex is founder of Data Driven Consulting, an independent consulting company that focuses on obtaining high quality data to facilitate effective business decisions. Data Driven Consulting specializes in a number of areas, including market research, dashboard and data presentation, and authoring of white papers and e-content.
One of the more anticipated functions of Excel 2010 is Microsoft’s implementation of sparklines (as defined by Edward Tufte – data intense, design-simple, word-sized graphics). Although the recent patent application made by Microsoft would seem to imply that they are the inventors of in-cell sparklines, both Edward Tufte and the software vendors who have offered Excel sparkline solutions would beg to differ.
One of these solutions, Sparklines for Excel (SFE), is favored by many, is free and open-source, and works well. There are versions for Excel 2003 (and before), and Excel 2007, with the latter version adding a new section to the ribbon. Below are screenshots of the implementations – click on these reduced images to open a new window showing the full size sparkline tabs.
Excel 14 Native Sparkline ribbon tab
Sparkline For Excel Add-In Ribbon Tab
Given the anticipation of Excel 2010’s sparklines it makes sense to compare the two offerings. This table compares the functionality of the two options.
|
Sparklines for Excel
|
Excel 2010
|
|||
| Types of cell charts | 14 and growing: Line, Pie, Pareto, Bullet, XY, Scatter, Bar, Column, Stripe… |
 |
Three: Line, Column, Win/Loss | |
| Overall look (sparklines) |  Some blocking, not noticeable at normal zoom levels |
|
 Smoother |
|
| Features on individual chart types | Excellent, whole range of markers like hi/low lines, max/min points |
|
Limited to some basic additional data indicators, but they work well | |
| Ease of deployment | Have to distribute macros with file, some VBA crashes, mostly during creation. Win today, as no built-in option | |
No extra package, but Excel 2010 needed. Win in 2-3 years when 2010 is ‘standard’. | |
| Ease of use | More complex, for example looking up meaning of formula variable, color chart | |
Easier, native to the product, so formatting is quick | |
| Code Customization and Stability | VBA macro based (non-locked) so customization possible (and legal) | |
Not VBA based – more stable, but no customization | |
| Grouping | Can set max and min so that multiple charts scale the same |
|
Adjacent charts are grouped automatically making for easy changes of formatting/scales | |
| Ease of editing | Charts are shapes created on the basis of formula in the cell | |
Effectively separate charts, edited with own section on ribbon | |
| Future additions | Constant addition of new chart types/upgrades to existing |
|
Maybe some fixes, but will likely have to wait until Excel 20XX for new chart types | |
| Cost | Free, but you need Excel of course |
|
Free, at least after you’ve paid any upgrade costs |
While there are equal numbers of ticks in both columns, there are clear winners depending on your required functionality and how you intend to share the information. For creating dashboards that benefit from different types of in-cell charts and line markers, SFE is still a clear winner – the range of chart types, the continued development, along with the fact that the sparklines are formula based makes for a powerful tool. Distribution is complicated by the need to load the add-in file, but for dashboards that shouldn’t be a barrier.
On the other hand, for a quick summary of data that is emailed around, the native version in Excel 2010 is clearly simpler. In the near term (until 2012 I would guess), SFE is still really the only option anyway as sparklines created in Excel 2010 simply won’t show on earlier versions.
It’s great that Microsoft has offered sparklines – hopefully end-users will appreciate their value more now they are a native part of the program, and become used to sparklines’ inclusion in their data lives.
Jeff Weir says
Another sparkline solution comes bundled with Excel 2007 (and probably 2010), and might possibly be superior than the sparklines options you mention. That is, you can put a cell sized chart in 2007 and align it to the cell so that it resizes if the cell resizes. Then you have access to all the formatting options and chart trickery you get with any normal 2007 chart.
For instance, here’s some screenshots showing how these might be implemented so that users can change what is displayed using the check boxes titled ‘Graph Controls’:
This shows just the YTD sales (using red bars)
This one shows YTD sales and overlays the year’s forcast (using a black line)
This one shows YTD, overlays the forecast, and shows how these compared to the previous year’s sales (using a blue background).
I’m not yet convinced that other implementations of sparklines come anywhere close to what a chart-savvy persot can do with a cell-sized chart in 2007 (and probably 2010 also). However, good luck with this using 2003 or earlier . As Jon points out in the comments at Sparklines and Data Bars in Excel 2010 “You can do it in Classic Excel if you give yourself some margin, by making the chart object no less than about four rows high, making the plot area one row high, and positioning the chart object so the plot area overlies the cell. Pain in the neck, but that’s why Bill Gates invented VBA. 2007 made this a bit easier by removing the margin, the several pixels all around the chart area that are inaccessible in Classic Excel, and by allowing the whole chart to shrink to a cell’s height and still show its contents.”
Jeff Weir says
I fogot to say that the above post is excellent. Keep ’em coming.
Alex Kerin says
First, thank you Jon for the opportunity – time to write some more content on my site..
Jeff, great comment – the trick that I had not seen before was aligning the chart to the cell – I think this is key to making this a very viable option for users prepared to create the charts. In a way it may have been easier (and better) for Microsoft to have just had an option of automatically creating small versions of all their charts and pinning them to a single cell. Do you have a link so that people can download the xls?
Jon Peltier says
Alex –
Thanks for the article, of course. Any time you have something interesting to say, and want to
wasteshare it on another blog, just let me know.As Jeff reminded me, I’ve dabbled with small versions of regular charts. The nice thing about many of the sparkline implementations is that an array of charts can be made using a single interaction with the user interface. I suppose VBA can be recruited for this. Oh no, I feel a project coming on…
Dimitri says
There is also a paid add-on for Excel that does good sparklines and other miniature charts:
It obviously uses VBA to buld charts, but the chart elements are constructed from special font characters, so one only needs a (free) set of fonts to see the charts in Excel. Not as seamless as Excel 2010, but it is easier to distribute and install fonts once, rather than send macros around and have security and debugging headaches. My team at work has constant issues with the same code running fine on one machine and crashing on another, almost identical one.
In my case, Excel 2010 will fit the bill perfectly.
Matt says
When I first became interested in using Sparklines in Excel I found some other sources of Sparklines functionality. So far I like Sparklines for Excel because I can customize it by modifing the VBA code. The addition of such charts to Excel 2010 will hopefully get the average user more interested in this feature. Hopefully there will still be room for the more customized versions.
Others (not necessarily exhaustive list) are:
Tiny Graphs: http://www.spreadsheetml.com/products.html
Sparkmaker:
Microcharts:
derek says
I’ve dabbled with small charts as sparklines in Excel, and my problems as a user have been:
a) no way to shrink a chart to a single cell of 10 points height before 2007. The two workarounds are, shrink the graph to more than one cell, or blow the text up to 20 points height and display the screen at 50% zoom. The former solution is less like a sparkline, and I believe Jon has deprecated the latter solution on compatibility grounds.
b) Hard to manage by hand. I freely admit this is my own fault for not acquiring VBA skills.
c) Each chart duplicates all the data in the spreadsheet, potentially bloating spreadsheets that are already large.
Jon Peltier says
Derek –
a) From the standpoint of someone observing the shrunken charts, this is exactly like a sparkline, even if the chart object has to be oversized to make it fit. From the perspective of the designer of the display, it’s not so elegant. However, the designer with a little practical VBA in his pocket makes it work without too much stress.
b) The designer with a little practical VBA…
c) Each chart need only have the data from a particular row or column, not all the data in the sheet. When the workbook is filled with data, links, and ugly formulas, the charts probably don’t overwhelm the system resources. Of course, through Excel 2003 there is a practical limit of 124 charts per worksheet (or 248 if you turn off autoscaling of font sizes). I am not sure what limits Excel 2007 has in terms of charts per worksheet, but experience shows that 2007 bogs down before reaching a hard limit like the autoscaling imposes on old Excel.
Alex Kerin says
Dimitri – the Bonavista Microcharts are nice – the formula grouping works really well and the chart options are spot on. For me however, the convert to font/shape isn’t that useful – dashboards that I work on with clients tend to be updated and dynamic – the conversion loses that and it becomes a static report (as far as I could understand).
So then I’m back to distributing VBA anyway, combined with a not insignificant license cost that I have to pass on. I’m not sure at that point, for my situation, I wouldn’t use SfE instead.
Alex Kerin says
Matt: I hadn’t seen the Tiny Graphs before – it is vba that creates tiny Excel graphs (who’d have thought with a name like that). The beauty of that is of course, no vba to distribute even with dynamic data, and that you have the full range of charting capabilities offered to you by Excel. It’s certainly an option (and free and open).
Bissantz is great – love the export to html ticker and the integration with other Office products – but it does cost, and it’s still vba, so not convinced that it is any better than SfE.
Fabrice Rimlinger says
@ Alex & Jon
Thanks so much for this constructive comparison. Little frenchy VBA amateur vs. almighty MSFT coder team … I am thrilled.
Lets review quickly your comparison table.
Number of charts : XL2010 also offers VariChart() in the conditional formatting menu… in the other hand, the win/loss is nothing but a column chart with 1 ; 0 or -1. This limited number of charts does not reflect MSFT’s Business Intelligence ambitions… at least when it comes to Excel. Let’s assume it’s a start.
Overall look : irrelevant from my point of view. Even w/ Zoom of 400% blocking is not noticeable, and specially not on printed reports.
Features : Even with tiny charts, we need some reference: a line to materialize a threshold or a target. XL2010 sparklines do not provide any reference and more important, do not offer the possibility to add a visual scale, a context … it looks good but you do not know what you are looking at. Clearly not enough to design usable dashboards.
Ease of use : SfE formulas are used like any other Excel formula… I agree however that the “formula wizard” offers limited features when UDFs are a bit complex. I could spend some time on SfE user interface to make the parameters more understandable…
Customization : The code is yours, it’s open-source… be fair, share your ideas and improvements on SfE’s blog, quid pro quo.
Stability : This is from my perspective SfE’s weak point, especially with XL2003. VBA events and many shapes to refresh are a cause of crash. SfE can also be painfully slow, inefficient coding and numerous shapes being the reason. I have been thinking of using .Net instead of VBA… but is it worth the effort with XL2010 at the corner ?
Grouping : True that XL2010 will make it “easier” (just quicker actually), but as a user, I prefer to see the formula and identify clearly the scale I am using. Using named ranges makes this so easy with SfE… your choice.
Ease of editing : Well, this is where I do not fully agree. SfE makes the formula easy to edit, plus you can use any nested formula inside SfE formula. I reckon however that only “advanced” excel users will fully appreciate this. Basic users (the ones that use Sum(a1:a2) and nothing else == > a vast majority) will prefer to tick a few boxes et voila.
Future additions : Except maybe a formula for creating quick Gantt chart, I would say that the toolbox is pretty complete. Improved “User Experience” would be the next priority (userforms, tick boxes, color picker, embedded help…). (well… less crash and more speed also !)
Price/cost : SfE is a great hobby.. priceless to me. Now, if Steve Balmer wants to include SfE features in XL2013… just let me know, we’ll talk $
As a bottom line, I would say that MSFT XL2010 is offering “Sparklines for the masses”, a simple solution that works for most users, but is definitely not enough for users who want to build more elaborated and comprehensive dashboards. But that’s maybe MSFT “SQL Server Reporting Services” job ?
SFE in the other hand, is a tool made by an “end user” with limited programming skills + great help from more advanced programmers, but with a much better understanding of what is required in a “decision making” process and dashboard construction (show a reference or target ; limited use of colors ; simple formulas if only mandatory parameters are used).
I am just frustrated that MSFT did not even consider looking at what SfE (and Bonavista and Bissantz) was offering before implementing their simplistic solution… serioulsy, the guys at MSFT France said “We don’t care about SfE, we have THE SAME in Office 2010” … “THE SAME”… LOL
Jeff Weir says
Hi Jon. Can you clarify on your comment re shrunken charts (as opposed to Sparklines) “From the perspective of the designer of the display, it’s not so elegant.” Do you mean that they take a bit more time to set up? That would definately be the case the first time you do it. But after that, it’s just a case of using your dashboard as a template for the next one.
Out of interest, I know nearly no VBA. If I was more profficient, I could probably have saved quite a bit of time using some VBA to make identical formatting changes across all the charts.
I think I used the great Tiny Graphs utility for the first graph I created, but now I just copy one small graph, paste it to where I need a new graph, and amend the series and formatting as appropriate manually.
I’ve got about 100 of these spread across three worksheets in the workbook, and I don’t notice any performance issues to speak of…even on my little puny notebook pc. I haven’t measured how much they bloat the worksheet, but in the era of blazingly fast connections and free storage, this isn’t a major consideration for internal use.
Jon Peltier says
Jeff –
I was referring to the oversized chart objects required to make these tiny chart sparklines in 2003. Inelegant because of all the overlap, making it hard to click on the chart you want to edit. The visual effect is actually reasonably good.
Fabrice –
It’s a real-life David and Goliath story. You are to be commended for your hard work and continuing innovation.
I’m sure Microsoft did consider existing sparkline approaches before implementing their own sparklines (and filing their patent LOL). Engineering an integral part of an enormous application like Excel is very complicated, and there are always concerns with unexpected interactions with other features. These concerns prevent taking large steps at a time. I bet the next few steps are already included in the next Office version.
Alex Kerin says
Fabrice: all great points, and I don’t think I made the point strongly enough – SfE is fantastic, especially considering the resources that are available to both parties…
Perhaps I could help you on some of the UI improvements (not the coding unfortunately, just the look). Thanks again, enjoy your new addition.
DaleW says
Clearly Microsoft is offering only a very limited implementation of Sparklines with Excel 2010.
As a happy BonaVista MicroCharts customer, I would have loved if this Excel Sparklines review could have included the two current premium commercial contenders as well: BonaVista and Bissantz. Both use freely distributable custom fonts to create their Sparklines, which seems like a very elegant solution to me. Both of these companies also could be considered Davids compared to Microsoft.
Fabrice clearly has created a very impressive open source implementation, worth a look by anyone interested in using Sparklines in Excel.
Nevertheless, I believe BonaVista still offers the best Excel Sparklines available, considering ease of use and functionality and stability (since under XL2003 I struggled with an earlier version of SfE — although others may have different experiences).
sam says
“Solid fill” data bars in 2010 can also be considered as “Sparklines” type.
Jon Peltier says
Sam –
Perhaps, but Tufte defined sparklines as “word-sized” graphs, and a bar by itself, though word sized, doesn’t make much of a graph.
If you mean it’s another type of graph integrated into the worksheet, sure.
Gerald Higgins says
A little off topic, but I had to laugh when Fabrice mentioned the users who use SUM(A1:A2).
What about those who use SUM(A1) ? There are plenty of those too ! :-)
Jon Peltier says
Gerald –
People are not trained on the use of Excel formulas. They get used to hitting the big Sigma button to enter a formula, with the result that all formulas are included in =SUM(). For example: =SUM(A1+A2).
Alex Kerin says
Something I didn’t pick up on before is that publishing Excel documents to the web through docs.com or Sharepoint Services causes shapes to not be shown. Therefore any add-in (S4E included) that creates shapes to depict the charts cannot be used in this way.
The Excel 2010 sparklines are not shapes, so do show in the online versions. If you are thinking about deploying to the web, then you may be limited to using 2010 sparklines, and creating other charts in the creative ways shown on this website.
paul says
@Jeff I’m a little bit of a chart newbie. Can you by any chance share that file. I like those what you did in those screenshots and I want to be able to do the same thing.
Appreciate your help !
Jon Peltier says
Paul –
Those examples are very simplistic. You can do the same just by selecting some data and using the command to build a sparkline.
Alex Kerin says
A quick comment to add here is that sharing Excel docs is now much easier with the Skydrive/Live Docs website that MS operates. You can even embed editable Excel sheets in blogs/web pages now:
Heather says
I’m not able to get my sparklines to display when I publish to SharePoint Excel Services. I have the trusted file location, select a named range when I publish. The publish option does not display any charts to select. I publish this file to Excel services frequently so it’s not a permission issue. However the Sparkline just doesn’t display. Any ideas? I’m using Excel 2010 and SharePoint 2007.
Jon Peltier says
Heather –
I don’t know whether SharePoint also has to be version 2010, since that’s when Excel introduced sparklines.
I don’t know anything about SharePoint, so you’ll have to look elsewhere for an answer.
Fabrice says
see Microsoft site : http://msdn.microsoft.com/en-us/library/ff595319.aspx
Shapes are not supported / rendered.
” In Office SharePoint Server 2007, Excel workbooks that contain unsupported features like VBA macros, form controls, and so on are not loaded in Excel Services.
In SharePoint Server 2010, to help users work with this limitation, Excel Services ignores certain unsupported features. In other words, rather than blocking the entire file from loading, Excel Services loads the file but you do not see the features that Excel Services does not support.
Following are features that do not prevent Excel Services from loading a file:
Cell comments.
Formula references to external books.
Query tables (also known as external data ranges).
Microsoft Visual Basic for Applications (VBA).
Any OfficeArt technology. For example, Shapes, WordArt, SmartArt, organization chart, diagrams, signature lines, ink annotations, and so on.
Note that these features continue to be unsupported. This means that they do not render, execute, or work in any way as they do on the client. “
Jeff Weir says
@Heather…can you use small charts instead like I’ve done in the very first comment above?
Heather says
Jeff, where are small charts and how do I create them? I don’t program in VBA. Is this a user interface function? If so any possibility you could share an example workbook? I love the example with the checkboxes, again not sure what would happen when publishing to SharePoint. This workbook is a sort of quasi-dashboard I’ve created for SharePoint Report Centre. What I’d read was that sparklines did work with SharePoint so it’s dissapointing, but I can do the extra work for a tiny chart if it’s not to difficult.Thanks for the help.
Jon Peltier says
Heather –
I think Jeff means use regular charts, which you have made small by dragging their corners with the mouse.
Paula Boan says
NEED HELP! My spark line is grayed out. What are the steps to turn it back on?
Thanks!
Jon Peltier says
Do you have a range selected? Is the sheet protected? Are you in compatibility mode? What if you open a new workbook?
nick a says
A totally unnecessary complication for most Excel users. after reading all this I still have no idea what a sparkline is and why I should want one. All I know is I am damned sure I DONT want them. I just want to make a nice simple graph, nothing more and now I cant because of these damned things. I hate Excel just for this.
Jon Peltier says
You can ignore sparklines, and make charts the same way as ever. Their presence in no way interferes with your ability to make regular charts.
You might also do a little Googling about sparklines to see what people use them for. In the right context they can be very useful.