While the introduction of native support for sparklines and other microcharts in Excel 2010 was welcome, Excel is still lacking more advanced visualisation features. I came across Sparklines for Excel – a free Excel addin that gives you a lot of extra charting options, not just sparklines – a while ago but I’ve only just got round to playing with it and I have to say it’s a lot of fun. I’m not much of a data visualisation expert (I’ll leave that to the likes of Jen) but it’s a subject that every BI professional needs a passing knowledge of and in any case it’s a shiny new toy to play with, so it’s worth a blog post.
What I like most of all about Sparklines for Excel is that everything is driven from Excel formulas, and no VBA is required. That means you can make every aspect of the charts you create data-driven, and this holds a fundamental appeal for the data geek in me. Let’s take creating a treemap as an example, and start with an Excel 2010 worksheet hooked up to the Adventure Works cube using some Excel cube functions plus some thresholds telling us whether the values for Gross Profit Margin are good or bad:
We can then simply click on an empty cell and then click on the Treemap button in the ribbon, fill in some ranges, and we get the following formula:
=Treemap(D5:D10,C15:I29,,,E5:E10,G5:H9,C5:C10)
And this treemap in the worksheet (I won’t even try to apologies for the colour scheme):
Cool, eh? And of course, as soon as you change the dropdown filter to select another year, or change any of the threshold values, the treemap updates too. Even the position, length and width of the treemap itself can be parameterised.
You can see the full list of chart types – including heat maps, cascade charts and Pareto charts – in the manual here. It’s definitely worth checking out if you’re an SSAS or PowerPivot user who’s into data visualisation and on a tight budget.
Nice Tool. Thanks for the info.
Thanks for the post about SfE
Your treemap will look much better if you sort your data (internet sales amount) from higher to lower amount…
In order to avoid black blocks, your colorscale should start at 0, not 0.4..
Regards¨
Fabrice
PS : SfE is free, but a donation is always welcome 😉
Hi Fabrice,
Thanks for the tips, and for the great tool!
Chris
Fabrice,
Thanks for all your work in this area, where can I donate?
Thanks,
GEL
Hi Perry, you can go to http://sparklines-excel.blogspot.com and click on the big red button on the right, this will bring you to a Paypal page… Thx
Very useful samlpes. Thank you for link!
Hi Chris,
You said that no VBA is used.Does it mean that it is supported in SharePoint 2010 Dashboard.
Is it possible to use it in PerformancePoint Dashboard Designer as a dashboard part?
No, it’s still an Excel addin and I guess that means it won’t work. What I meant was that no VBA is needed to configure the charts.
I thought so. Thanks for your answer and the link.
Pretty cool chris. In a quick search I didn’t find any information on Excel Services support. So probably a nice add-on for Excel only users?
regarding Excel Services, SfE will probably not work w/ SharePoints as Sparklines are “Shapes” objects.
See : http://support.microsoft.com/kb/928721 & http://msdn.microsoft.com/en-us/library/ff487973.aspx.
However, I am currently looking for some developper who would be able to port SfE in VB.net and find a way to make it compatible w/ Excel services… let me know if you are interested.
While the effort is commendable, the coding is exceedingly poor.
Hi Chris,
It is about three months or more i was searching for an add-in to create a tree map, and i just found it the day before yesterday. I was so excited i am almost sitting in front of my lap top for last two days. I made it but, may be I did not understand color scale and color code. Though i followed your code here comes only two colors. Please help me understand COLOR SCALE and COLOR CODE and how to put it in the command. I want different colors for different cells.
Thanks from my heart to both of you and Mr. Fabrice for democratizing the power of information visualization for the mass.
I think Fabrice is better placed to help you on this than I am…
1. Is there a way to adjust the scale on sparklines?
2. Is there a way to sort by sparklines, to emphasize scores which drop off over time?
Douglas, you’re better off asking these questions on the Sparklines for Excel site itself.
I am trying to register now. The last box is a problem, “Sparklines for Quizzical Smiley Face, Question Mark, Question Mark”. I do not understand what it is asking.
1. Is there a way to adjust the scale on sparklines?
Yes. Usually I include 2 parameters (Min and Max) so scale can be adjusted and shared among several sparklines.
2. Is there a way to sort by sparklines, to emphasize scores which drop off over time?
You cannot sort by a chart. but you can have a formula like : if (average goes up) then (draw a red sparkline) else (draw a blue barchart).
“Sparklines for Excel” is a set of formulas, which behave like any Excel formula, except that the result is a chart, not a value.
You can register on SfE’s forum (http://www.rimlinger.eu/sparklines/) for more questions and download example files.
Thank you, Chris.
Doug in York PA
Oh,…Sparklines for EXCEL. Very clever.
(!)