Another guest post from Kasper… our man in Amsterdam!

VincentVegaLaptop
Kasper Vega Crunching Numbers on His Laptop

Today we have another guest post from Kasper de Jonge, our man in Amsterdam!  He’s been experimenting with Sparklines, a feature that’s new in Excel 2010, and integrating it into his PowerPivot work.

Take it away Kasper…

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.

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, first we create a 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 using a pivottable 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 [link removed due to 404] 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:

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

You can download a sample file I created here.