Another guest post from Kasper… our man in Amsterdam!
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 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
You can download a sample file I created here.
Hi, great article.
I think I’m stuck unable to use your system, because you have a fixed number of rows and don’t have the + buttons that expand to show the detail rows.
I don’t know how many Salesmen I’d have – something between 6 and 20, and each of them might be opened to reveal up to 30 clients.
On a second sheet I have created a table of monthly sales. It’s easy to have sparklines there because the data is in the table.
It would be great to be able to vlookup the customer and return the sparkline!
Am I hoping for too much?
Chrs,
T.