XL2000: Cannot Undo PivotTable Refresh for Non-OLAP Data Sources

After refreshing the PivotTable in your workbook, some or all of the PivotTable Refresh actions are not listed in the Undo list.

This behavior occurs when your workbook contains PivotTables from both OLAP and relational databases. When you refresh a PivotTable created from a non-OLAP data source, Excel clears all Undo actions. If you have several of these types of PivotTables in the same workbook, the number of PivotTable refresh actions that you can undo depends on the order in which you refresh the PivotTables.

For example, this behavior will occur if your workbook contains the following PivotTables: If you refresh the PivotTables in the order PT1, PT2, and PT3, or click Refresh All on the External Data toolbar when the PivotTables have been created in the order PT1, PT2, and PT3, the action that you can undo is the refresh of PT3. This behavior occurs because the refresh of PT2, which was created from a non-OLAP data source, clears the undo item containing the refresh of PT1.

If you manually refresh each PivotTable in the order PT1, PT3, and PT2, no refresh actions are available because the refresh of PT2 would clear all prior undo actions.