Pivot Tables/Excel 2007/.xls file format - Save before you start
Posted
Mon, May 25 2009 12:15
by
Nate Oliver
Happy Memorial Day!
Right - Excel
I'm a fly by the seat of my pants kind of guy, crunch data now, ask questions later - I put the rapid in RAD. It's quite common for me to open a brand new, unsaved Workbook, and simply start to perform whatever task at hand.
It turns out this is not a very good idea in Excel 2007 if you're creating a Pivot Table and your intent is to save the file in an older file format (pre 2007), that being .xls, which Excel refers to as compatibility mode. There's a lack of compatibility.
The first time I did this (and last time), Excel prompted me, at save-time, with the Compatibility Checker, that my Pivot Table won't work. I thought it was joking, turns out it wasn't. You're kind of in no-man's land at this point, you've invested time in creating your Pivot Table, you need compatibility with prior versions, and there's no way to get there.
This is explained, here:
http://blogs.msdn.com/excel/archive/2006/08/30/730796.aspx
"Strategies for sharing PivotTables with other users
As noted above, version 12 PivotTables are not downgraded to version 10
PivotTables and will not be refreshable in previous versions of Excel. If
you wish to share PivotTables with people using a previous version of Excel
AND they have a need to refresh the PivotTables, you will need to ensure
that these PivotTables were created as version 10 PivotTables."
Basically, the inability to save a file with a refreshable Pivot Table that was created in an unsaved Excel Workbook, in Excel 2007, as an .xls file, is understood and by design. Whew - that was a mouthful.
The safe strategy, when it comes to Pivot Table creation, is to always save your Workbook as an .xls file before you even think about creating a Pivot Table. This rule really applies to saving in an .xls file format with Pivot Tables - but personally? I think I'm going to make it a general one, save before you start.