Pivot Table Compatibility Issues

Like a lot of users, I’ve done a lot with pivot tables over the years. A typical application to produce reports would be something like what I did today:

  1. SAS query against a database, summarised and output to csv
  2. open the csv file in Excel and create a pivot table off the summarised data
  3. format the pivot table, selecting the fields to aggregate by, and generate a pivot chart for a nice visual

This week I joined the pilot for our rollout of Office 2007, so I did all the above in Excel 2007. Having followed the improvements to pivot table functionality in this version on the Excel team blog (some posts archived here), and having tried out some fairly trivial pivot tables in the trial version I downloaded at home, I was keen to take a look at what I could do with them in this case. I wasn’t disappointed, there are a lot of really nice enhancements to both form and function, which produced a good-looking report.

The wrinkle occurred when I sent the document to a co-worker – when saving to 97/2003 format I was advised that some functionality would not be available. Turns out it’s more a case of all functionality is not available – the pivot table cannot be refreshed, filters cannot be changed…basically it turns into a static report, which of course is not at all what I wanted. I’d been bitten by the fact that pivot tables are versioned, just like Excel. The pivot table I’d created was version 12; the highest version my co-worker can use in Excel 2003 is 10.

So ok, I should have expected that in a new version of Excel, correct? After all, if there’s new functionality available in the new version, it seems pretty reasonable to expect that functionality not to be available in an old version, no? Well, yes…but I had naively expected that saving the file in 97/2003 format would have ‘downgraded’ the pivot table version, possibly disabling functionality that isn’t supported in 2003. Turns out that cannot be done after the pivot table has been created – the Excel team blog posted an explanation of why this is the case a couple of years ago, which obviously I had either missed or forgotten.

In any case, the way around the issue appears to be to ensure the file is saved in 97/2003 format before creating the pivot table. This is a fairly straightforward fix, but unfortunately it’s going to create some issues for us. We use a lot of code for our regular reporting (served to Excel 97/2003 users) that creates pivot tables, and I’m pretty sure most of it creates the pivot table before saving the file. Revising all of that is going to be a whole lot of nastiness…

Related post from a while back on funnybroad’s Dear Microsoft Office 2007 team