THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Jamie Thomson

This is the blog of Jamie Thomson, a freelance data mangler in London

Using Pivot tables in the Office Excel Web App

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:

image

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:

image

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:

image

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:

image

“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:

image

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

Published Monday, August 13, 2012 3:13 PM by jamiet

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Florian Eiden said:

Hey Jamie,

To enable this, all you did was put the Excel file in your Skydrive? In some kind of public folder maybe?

Anyway that's really cool. Thanks for the info :)

August 13, 2012 9:44 AM
 

jamiet said:

Hi Florian,

Yes, all  had to do was store it on SkyDrive (which in Excel 2013 is the default save location) and make the containing folder publicly accessible.

Regards

Jamie

August 13, 2012 9:47 AM
 

Ronald said:

Looks cool. Does this also work with Powerpivot sheets?

too bad skydrive is blocked on most company sites :/

August 23, 2012 9:58 AM
 

jamiet said:

Hi Ronald,

No, its regular pivot tables only, not PowerPivot.

August 23, 2012 10:14 AM
 

SSIS Junkie said:

I have burgeoning interest in the world of Open Data which wikipedia describes as: Open data is the idea

February 11, 2013 8:34 AM
 

Olesia said:

hello! what other pivot components for online work you can recommend? I'm trying to find but its not easy.

February 28, 2014 1:05 PM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement