You may have noticed from my recent posts regarding Excel that I have a keen interest in the upcoming release of Office and in particular I’m interested in the capabilities of the Excel web app. One new feature that piqued my interest was that pivot tables and slicers are now displayed in the Excel Web App on SkyDrive and, even better, they are interactive (i.e. even if you don’t have permission to edit the Excel workbook you can still slice’n’dice the pivot table). I wanted to throw together a workbook that enabled me to check out these new features so I headed to my favourite free data resource, the London Datastore, and browsed through the data that they hold on the 2011 UK census, much of which is made available in Excel workbooks.
Its great that London Datastore makes this data available however I dislike the fact that, in many cases, that data is not presented in its rawest form. For example here is a screenshot of a worksheet that shows Number of non-UK short-term residents by sex, local authorities in England and Wales where the data has already been pivoted on Gender and displayed hierarchically by region yet it is not actually presented in an Excel pivot table:
Moreover, the raw unpivoted data is not available. We have effectively been barred from viewing the data in its rawest form. This effectively means I cannot easily interact with the data, we can’t even use that most basic of Excel features – filtering!
To make matters worse, some of the sub-totals are wrong. The sum for Females in Tyne and Wear (Met County) (i.e. Gatehead, Newcastle upon Type, North Tyneside, South Tyneside & Sunderland) is 2000 yet the subtotal given on the spreadsheet is 2100:
So, not only can the data not be analysed it cannot be trusted either!
I figured this could be a lot better so I pasted the leaf-level data into a new worksheet and manually manipulated it so that so that I had the raw data in a table, including Gender as a column:
From here its much easier to build a pivot table and, even better, that pivot table can be interacted with on the web using the Excel Web App. Why is that better? Simple, its because I can give you this URL: http://sdrv.ms/Nb43St and you can go and interact with it yourself; you don’t even need to use Excel, all you need is a web browser.
If you visit that URL you’ll see four worksheets:
“Information” & “Table M02” are the worksheets contained in the original workbook that I downloaded from London Datastore. “Raw data” is the table that I created manually and “Pivot” is the pivot table with slicers. Here’s a screenshot of that pivot table on SkyDrive:
From there you can filter, slice, sort, drill-down and do most of the other stuff that you’re used to doing using pivot tables – if you want more functionality (such as drillthrough, drag-and-drop dimensions between axes) simply download your own copy of the workbook and play around at will in Excel. Notice also that the sub-total for Females in Tyne and Wear (Met County) is, of course, correctly given as 2000, not 2100.
Pretty cool, no? Finally one can interact with Excel pivot tables on the web and, best of all, those workbooks can be shared with the entire world – I have been waiting years for this. Sure, web based spreadsheets have existed for a long time but this is the first time (that I know of) that Excel pivot tables work seamlessly in a web browser and given the amount of Excel workbooks out there, that is a big deal. Click through, play around with the pivot table and slicers to your heart’s content and let me know your thoughts in the comments below.
@Jamiet