Sparklines for Excel

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:

image

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

image

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.

21 thoughts on “Sparklines for Excel

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

  2. 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?

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s