Susan has a workbook that was originally created in Excel 2003 containing several PivotTables. She loaded the workbook in Excel 2007 and added additional PivotTables, then saved it in compatibility mode so it could still be used in Excel 2003. However, if she doesn't open the workbook in Excel 2007 the PivotTables will not update either manually or automatically. It works fine if she opens it in Excel 2007, but not in Excel 2003. Susan wonders if this is normal behavior.
Apparently it is. From the information we can gather, it appears that if a PivotTable is created in Excel 2007, even if the workbook is saved in compatibility mode it will not work properly in Excel 2003. (You won't be able to reliably edit or update the PivotTable in Excel 2003.) The only solution is to recreate the PivotTables in Excel 2003.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (6950) applies to Microsoft Excel 2003.
Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!
Create a complex PivotTable and you may find that your system slows to a crawl. The reason for this may be due to the way in ...
Discover MorePivotTables are used to boil down huge data sets into something you can more easily understand. They are very good simple ...
Discover MorePivotTables are used to analyze huge amounts of data. The number of rows used in a PivotTable depends on the type of analysis ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2014-12-11 20:24:42
Dinesh
Hello,
Is there a way to convert pivot created in excel-2003 to excel-2013 without recreating them from scratch. If so would you please share the steps with me.
2012-04-26 15:40:12
Mark Woollen
What if you don't have Excel 2003, and need to creat the pivot tables?
The trick is: if you open a spreadsheet that is already in compatibility mode (either originally 2003 version or saved as 2003 .xls version) then Excel 2007 and 2010 will continue to treat the spreadsheet in compatibility mode... i.e. creating pivot tables the old school way and limiting data rows to 65K.
The problem arises when you create a NEW spreadsheet using 2007 or 2010, create your fancy new pivot tables, use new conditional formatting, and create MEGA large datasets over 65K. Even if you now save it in compatibility mode, the pivot tables cannot be converted, and they will be unmodifiable in early versions of Excel.
BTW - 2007/2010 conditional formatting can be saved in compatibility mode and works just fine.
Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2017 Sharon Parq Associates, Inc.
Comments