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

4 thoughts on “Pivot Table Compatibility Issues

  1. jonpeltier says:

    I worried about this issue, because I use PTs for a lot of intermediate data crunching. However, what I like to do to make PT generation easier is to create templates with dummy data and PTs with the layouts I want. The code overwrites the dummy data with active data and refreshes the PTs. Simpler and more reliable than rebuilding the pivot tables in code.

    It turns out that templates built for Excel 2003 work equally well in 2007. The PTs are version 10, which doesn’t matter because they are mostly used behind the scenes, and the users rarely interact directly with them.

    • geoffness says:

      That’s a great idea, thanks Jon – I’d imagine that would be slightly more efficient as well as more reliable.

      Also, that’s a good point about how users interact with the tables – I know how our team uses them but I have a feeling that the vast majority of our users don’t even realise that they can change pivot tables/charts. We (business information unit) would like users to interact more with them really – it would likely save a lot of customer contact if they did…

  2. Charles Urban says:

    I have had the experience that even when I saved the file in 97/2003, I lost some functionality, even the pivot table.. Excel tries to warn me when I save the file that I will lose some functionality, but I don’t understand the warning.

    • geoffness says:

      I think the subtlety there is that the file needs to be closed after saving in 97/2003, and then re-opened before the pivot table is created. Since opening the file in that format puts the application into Compatibility mode, any pivot table created will be version 10…at least that’s how I understand it. Kind of annoying, but as Jon points out if you have a 97/2003 template they’ll work in 2007 also.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s