Chris Webb's BI Blog

Analysis Services, MDX, PowerPivot, DAX and anything BI-related

Sparklines for Excel

with 21 comments

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.

Written by Chris Webb

November 30, 2011 at 5:52 pm

Posted in Excel, Visualisation

21 Responses

Subscribe to comments with RSS.

  1. Nice Tool. Thanks for the info.

    Evolex

    December 1, 2011 at 10:41 pm

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

    Fabrice

    December 2, 2011 at 1:35 pm

    • Hi Fabrice,

      Thanks for the tips, and for the great tool!

      Chris

      Chris Webb

      December 2, 2011 at 2:46 pm

    • Fabrice,

      Thanks for all your work in this area, where can I donate?

      Thanks,
      GEL

      Perry Perrister

      June 20, 2012 at 9:47 pm

  3. Very useful samlpes. Thank you for link!

    Alexander

    December 3, 2011 at 3:38 pm

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

    cemuney

    December 3, 2011 at 7:26 pm

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

      Chris Webb

      December 3, 2011 at 8:46 pm

      • I thought so. Thanks for your answer and the link.

        cemuney

        December 4, 2011 at 6:42 pm

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

    Bas Kroes

    December 4, 2011 at 10:47 pm

  6. While the effort is commendable, the coding is exceedingly poor.

    Colin Banfield

    December 8, 2011 at 5:34 pm

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

    Shamim

    March 10, 2012 at 3:28 pm

  8. [...] for BI are obvious: new apps for data visualisation (along the lines of what’s available in  Sparklines for Excel maybe; perhaps also the long-lost decomposition tree from Proclarity?), analysis, importing and [...]

  9. 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 Eckert

    July 29, 2013 at 7:26 pm

    • Douglas, you’re better off asking these questions on the Sparklines for Excel site itself.

      Chris Webb

      July 29, 2013 at 10:37 pm

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

        Douglas Eckert

        July 30, 2013 at 12:56 pm

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

      Fabrice

      July 29, 2013 at 11:05 pm

  10. Oh,…Sparklines for EXCEL. Very clever.

    (!)

    Douglas Eckert

    July 30, 2013 at 1:01 pm


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

Follow

Get every new post delivered to your Inbox.

Join 3,082 other followers

%d bloggers like this: