Comment from Jeff Weir
Time: Thursday, February 4, 2010, 4:51 am

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.”

Comment from Jeff Weir
Time: Thursday, February 4, 2010, 5:25 am

I fogot to say that the above post is excellent. Keep ‘em coming.

Comment from Alex Kerin
Time: Thursday, February 4, 2010, 3:01 pm

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?

Comment from Dimitri
Time: Thursday, February 4, 2010, 7:56 pm

There is also a paid add-on for Excel that does good sparklines and other miniature charts: http://www.xlcubed.com/en/mc.html
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.

Comment from Matt
Time: Thursday, February 4, 2010, 9:49 pm

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: http://www.bissantz.com/sparkmaker/index_en.asp
Microcharts: http://www.bonavistasystems.com/Products_SparkLiner_Overview.html

Comment from derek
Time: Friday, February 5, 2010, 5:22 am

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.

Comment from Alex Kerin
Time: Friday, February 5, 2010, 8:42 am

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.

Comment from Alex Kerin
Time: Friday, February 5, 2010, 9:44 am

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.

Comment from Fabrice Rimlinger
Time: Friday, February 5, 2010, 6:12 pm

@ 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

Comment from Jeff Weir
Time: Friday, February 5, 2010, 7:31 pm

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.

Trackback from uberVU – social comments
Time: Saturday, February 6, 2010, 1:37 am

Social comments and analytics for this post…

This post was mentioned on Twitter by Jon_Peltier: New on the PTS Blog: http://zz.gd/eb5dc9 Sparklines For Excel vs. Excel 2010 Sparklines (Guest Post)…

Comment from Alex Kerin
Time: Saturday, February 6, 2010, 8:55 am

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.

Comment from DaleW
Time: Saturday, February 6, 2010, 5:34 pm

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).

Comment from sam
Time: Sunday, February 7, 2010, 6:24 am

“Solid fill” data bars in 2010 can also be considered as “Sparklines” type.

Pingback from What are Excel Sparklines & How to use them? – Introduction to Sparklines in Excel
Time: Tuesday, May 18, 2010, 3:59 am

[...] 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. [...]

Comment from Gerald Higgins
Time: Thursday, May 20, 2010, 6:43 am

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 ! :-)

Comment from Alex Kerin
Time: Sunday, May 30, 2010, 10:38 am

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.

Pingback from Excel Sparklines – Office 2010 | DataSafeXL Blog
Time: Monday, June 21, 2010, 4:21 pm

[...]  Sparklines For Excel vs. Excel 2010 Sparklines Comparison var a2a_config = a2a_config || {}; a2a_config.linkname="Excel Sparklines"; a2a_config.linkurl="http://blog.datasafexl.com/excel-articles/excel-sparklines/"; Excel Articles   excel sparklines, microsoft office 2010   [...]