How to Stop a PivotTables From Overwriting Rows

Written by chris farrugia
  • Share
  • Tweet
  • Share
  • Email

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


  1. 1

    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.

  2. 2

    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.

  3. 3

    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.

  4. 4

    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.

Don't Miss

  • All types
  • Articles
  • Slideshows
  • Videos
  • Most relevant
  • Most popular
  • Most recent

No articles available

No slideshows available

No videos available

By using the site, you consent to the use of cookies. For more information, please see our Cookie policy.