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.
Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!
If you ever try to edit a PivotTable and get an error that tells you that the "underlying data was not included," it can be ...
Discover MoreIf you are using a data set that includes a number of zero values, you may not want those values to appear in a PivotTable ...
Discover MoreWhen you create a PivotTable based on data that contains lots of text, you may be surprised to find that your text is ...
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