Sparklines in Excel 2010

Continuing the content delivered at March’s UG…

According to Wikipedia, a sparkline is “a type of information graphic characterised by its small size and data density.”  More simply put, it is a very small line chart, generally small enough to be included inline with text.

This sounds too small to be of any use, but their inventor Edward Tufte and data visualisation theorists such as Stephen Few make the point that charts should really be used to give an overview of a data series which can easily be consumed  – they are not usually good for delivering really accurate understanding of numbers, but for a quick visual comparison of data.  Sparklines can therefore be used in tandem with a table of figures to combine both detail and overview in a single place.

There’s an excellent addin available for earlier versions of Excel which actually creates better and more flexible sparklines in my opinion, available direct from the developer.  But in 2010 there is native functionality for them.

Sparklines are added very simply by selecting one of the types from the Sparklines section of the Insert ribbon.  You then choose the cell range on which the sparkline is to be based and cell it will occupy – a sparkline fills just one cell.

Once added, the sparkline gets its own ribbon with additional formatting options.  These are mostly cosmetic but there are a couple of options you may find enhance them a little. such as adding various point types.

Because a sparkline is contained within a cell, changing the horizontal or vertical size of the cell will stretch or compress the sparkline accordingly.  You can also place them in a range of merged cells if you find you need a little extra space for them.

Of course, Sparklines aren’t part of the pivot table itself, so if a user drills into the pivot table or changes its setup in any way, the sparklines are likely to lose their validity somewhat.  They will work well for less interactive reports, though.

So, after conditional formatting, slicers and sparklines, what sort of dashboard have we come up with?  Something like the image below.

We could add as many more elements to this as we wished, of course – pivot tables and pivot charts, KPI lists, dials and charts (if you really have to!).  All this functionality will work fine when published to Excel Web Services in SharePoint 2010.

Pivot Table Enhancements in Excel 2010

I presented at the London meeting of the SQL Server User Group in March, here’s a walkthrough of the first part of what we covered.  The data I used in the presentation and this blog is the AdventureWorks sample data for SQL Server 2008 R2 available on Codeplex.

Pivot tables are Excel’s way of browsing and querying Analysis Services cubes, and a pretty fine job they make of it mostly.  Things were seriously improved in Excel 2007 but there were still some rather confidence-shaking omissions.  Excel 2010 makes the experience a bit more logical and gives a better result.

For a start, the conditional formatting has been improved considerably.  Conditional formatting is something that helps give worksheets a “dashboard” feeling. To apply in a Pivot Table, you just need to select any one cell in the data area then click the Conditional Formatting button in the Home ribbon.  Select the format type you want to apply which will fill that cell and give you an auto-format button. Selecting this gives you the choice of how far you want to expand the formatting.  The key option is to apply it at the hierarchy levels you’re currently viewing.

 

Now when you expand the hierarchy, the formatting hasn’t been applied to the child level.  In most situations this is likely to be correct, as the values for child members shouldn’t really be compared to members of their parent level.

They can be compared to one another, though.  So you can add more conditional formatting at this level.  It’ll be best if the formatting applied uses at least different colours to the parent in order to make it quite clear that there is a difference between the levels.

The neat thing about adding conditional formatting at a level is that it is then context sensitive to the displayed members.  So if you expand a different parent node, you’ll see all the formatting adjust to match.

The actual behaviour of some of the conditional formats has been improved too.  If you apply formatting to a measure which has both positive and negative values, the negatives will be displayed as a negative.

Slicers

A navigation tool which further enhances the experience of using Excel as a dashboard tool, Slicers are a selection tool.  They come into their own with dashboards which are to be published to other users, as they provide a quick and easy way of selecting data.

Inserting a slicer launches a dialog box from which you can choose dimension levels to slice on. If you select a hierarchy rather than a level, the dialog automatically selects all the child levels.  This will then cause Excel to add a slicer for each of the levels and they will be linked to one another.

 

 

 

 

 

 

Grouping slicers in this way means they cascade the selections the user makes.  So if in the AdventureWorks samples you create a slicer on product categories, choosing “Bikes” will limit the available selections in the subcategory slicer.

Of course, you can still add further slicers which are not part of a group but operate independently. You can also format slicers to match the look and feel of the rest of the sheet.

One final feature of slicers – each can be linked to multiple pivot tables or charts, providing they are based on the same data set.  The slicer ribbon contains a button which allows configuration of these connections.

I’ll post some details on sparklines tomorrow.

Why is this here?

For a couple of reasons really.  Firstly my old blog at http://my.opera.com/duncans is difficult to interact with and doesn’t provide the kind of features. Secondly I have resolved to take a bit more involvement in the MS BI and SQL Server community so I wanted something that I could update from my BlackBerry as well as Windows Live Writer and the web.

I’ll migrate some of the posts from my old blog to here when I get a chance, but I’m going to start by posting a walkthrough of the Excel 2010 business intelligence features that I gave at the SQL Server user group in March.