Microsoft Excel offer analysts the flexibility to create dynamic reports that can be easily updated when data is modified. Unlike a conventional Excel report, a PivotTable will resize to accommodate modified data when refreshed, which can overwrite any cells outside of the PivotTable. Unfortunately, this problem has existed since the advent of PivotTables and there is not a setting in Microsoft Excel to prevent overwriting data as of Microsoft Excel 2011. However, there are methods to avoid losing data when refreshing a PivotTable.
- Skill level:
- Moderately Easy
Other People Are Reading
Select any cell within the existing PivotTable. Refresh the PivotTable data by clicking on the "Refresh Data" button on the PivotTable toolbar. Alternately, right-click inside of the PivotTable and select "Refresh" from the menu. If the PivotTable refreshes without an error message, data outside of the PivotTable will not be overwritten. Choose "No" if a warning results that asks if you want to replace the contents of the destination cells.
Click on the first row of data outside of the PivotTable that will be overwritten when refreshing. Select numerous rows by holding the "Shift" key on the keyboard and pressing "Page Down." Insert new rows by clicking on the "Insert New Sheet Rows" button from the toolbar, located within the Home menu.
Refresh the PivotTable again. If you inserted enough rows to allow the PivotTable to expand, you will not receive a warning. If the warning reappears, insert more rows to act as padding.
Select the blank rows below the refreshed PivotTable that were added as padding. Delete them by either right-clicking and selecting the "Delete" option or by clicking on the "Delete" button in the Home toolbar and selecting "Delete Sheet Rows."
Tips and warnings
- The same process can be used if the PivotTable grows horizontally when refreshed. Follow the same steps, but insert columns to the right of the PivotTable in lieu of rows. If the PivotTable will be frequently refreshed, consider putting it on its own worksheet to prevent users from encountering this issue. You can move a PivotTable by clicking inside of the PivotTable to select it and then clicking the "Move PivotTable" button from the toolbar.
- If you accidentally agree to overwrite data when refreshing a PivotTable, use the "Undo" button in the Home section of the toolbar to bring the data back. Keep in mind that, after pressing "Undo," the PivotTable will no longer show the refreshed data.
- 20 of the funniest online reviews ever
- 14 Biggest lies people tell in online dating sites
- Hilarious things Google thinks you're trying to search for