PowerPivot and Sparklines .. the easy way

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

7 Replies to “PowerPivot and Sparklines .. the easy way

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

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

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.