PowerPivot and Sparklines .. the easy way

By | March 19, 2010

In Excel 2010 MS introduced Sparklines, as described on the Excel blog:

For Excel 2010 we’ve implemented sparklines, “intense, simple, word-sized graphics”, as their inventor Edward Tufte describes them in his book Beautiful Evidence.  Sparklines help bring meaning and context to numbers being reported and, unlike a chart, are meant to be embedded into what they are describing.
blogimage1

Too bad the sparklines aren’t really tightly integrated with PowerPivot  as are other charts. In this post i’ll describe an easy way to implement sparklines on PowerPivot data, with use of slicers.

Of course we start by loading data into PowerPivot, next  we create the following workbook in PowerPivot:

To gain more insight in sales per country per month I would like to use the sparklines per country. Lets start by adding data in a new sheet

Make sure you connect this Pivottable to the slicers from my other sheet by using the connect slicers option as posted in this blog post.

Ok next we are going to add the country’s to the original workbook. Select the country’s and copy them. Paste hem into the original sheet:

Now for the sparklines, go to insert and click Line. we now have to select the source for the sparklines, in our case this is the pivottable in our newly created workbook, and select all data in the months:

next we have to select where we want to put the sparklines:

This will result in the sparklines being showed:

We want them to be a little wider so we select the cell with the sparkline and the adjacent cell and click Merge and center to let our sparkline span two cells, next we select another sparkline design, I also like to use the markers.

This results in the following workbook:

We can even use the slicers, as we can see here when we select 2008 we only have 8 months, this will show in the sparklines as well:

I love sparlines, and they aren’t that hard to implement, for more information on sparklines see:

http://blogs.msdn.com/excel/archive/2009/07/22/formatting-sparklines.aspx and http://www.msofficegurus.com/post/Excel-2010-Sparklines.aspx

  • Have tested the Excel add-in “Sparklines for excel”. It offers much more than the MSFT solution. (and it’s free / open source)

    http://sparklines-excel.blogspot.com/

    Cheers

  • Shuwi

    Wow, great stuff !! Thanks!

    PS: Kasper, the first reference link results in a 404

  • Pat

    Are copies of your strikline examples available on line anywhere

  • Like the combination of slicers and sparklines – creates a very meaningful and user friendly interface.

  • Leon Carpay

    in combination with dynamic ranges, as the source for the sparklines, the solution of Kasper works code free and gives the right info when slicing.

    very nice indeed, Leon

  • Sourav Majumder

    1. How does PowerPivot stores data? Once import data from different database and saved the excel file, where does it gets stored?

    2. Is there any API(Java) to read/access the PowerPivot data?

    Please reply to my email id, I will be really grateful. Thanks.