Re-create Pivot Table Source Data Table

If you accidentally delete the Excel 2003 worksheet that has the source data for your pivot table, you may be able to use the pivot table’s Drill to Details feature to re-create it.

Extract the Pivot Table Data

To re-create the source data in Excel 2003, follow these steps to use the Drill to Details feature:

1. Make sure that none of the items in the pivot table fields are hidden. For page fields, (All) should be selected. For row and column fields, (Show All) should be checked. Note: You don’t need to include all the fields in the pivot table before using the Drill to Details feature.
2. Show the grand totals for rows and columns. If they aren’t visible, right-click a cell in the pivot table, and click Table Options. Check the options Grand totals for rows and Grand totals for columns, then click OK.
3. Double-click the grand total cell at the bottom right of the pivot table. This should create a new sheet with the related records from the original source data.

Fix the Extracted Data

If the original source data contained formulas, you will have to re-create them, because the Drill to Details feature exports the data only.

The columns in the extracted data will be in the same order as they were in the original source data.

The extracted data will be formatted with Table AutoFormat List 3. You can apply a different AutoFormat, or apply your own formatting.

Note: If you had made changes to the source data and not updated the pivot table, those changes won’t be in the extracted data.

Connect to the Extracted Data

If you rename the sheet that was created during the Drill to Details process, and use the same name as the worksheet that originally held the source data, the pivot table might automatically connect to the new source data table.

If not, you can connect to the re-created source data:

1. Right-click a cell in the pivot table, and choose PivotTable Wizard.
2. Click the Back button, and select the new source data table range.
3. Click Finish.