How to Stop Excel Refresh from Overwriting Adjacent Cells
Question details
Users need to enforce the external data property to overwrite existing cells, as the built-in setting fails to save and causes data refreshes to overwrite nearby cells.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Refreshing external data connections or queries where new data expands and replaces surrounding workbook data.
- Observed behavior
- The external data property to 'overwrite existing cells' does not remain selected after reopening connection settings, causing refreshes to inadvertently overwrite nearby data.
Verify the exact name of your data table from the Table Design tab, and ensure the Developer tab is enabled in Excel's ribbon to run the necessary VBA script.
Use a VBA Macro to Enforce Cell Overwrite Settings
Apply a short VBA script to manually set the QueryTable RefreshStyle property to overwrite cells, bypassing the unreliable UI setting.
By utilizing a VBA macro, you can explicitly force Excel's QueryTable object to respect the xlOverwriteCells parameter, preventing it from inserting new rows or deleting adjacent data.
Press ALT + F11 on your keyboard to open the Visual Basic for Applications (VBA) editor.
In the top menu, click Insert and select Module to create a blank script window.
Copy and paste the following code into the module window: Sub SetExternalDataProperties() With Range("TableName[[#All]]").ListObject.QueryTable .RowNumbers = False .AdjustColumnWidth = False .PreserveColumnInfo = True .PreserveFormatting = True .RefreshStyle = xlOverwriteCells End With End Sub
Replace the word "TableName" in the script with the actual name of your data table. Click the Run button (green triangle) or press F5 to execute the script.

Use the Legacy Data Import Wizard
Revert to Excel's legacy data interface, which often retains external data settings more reliably than the newer Get & Transform interface.
Delete and Recreate the Query Connection
If settings continue to fail and VBA is not an option, deleting and recreating the connection can reset the table's properties to a working state.
Try WPS Office for Reliable Data Management
If Microsoft Excel's query settings and external data connections are causing workflow disruptions, consider switching to WPS Office. It delivers robust spreadsheet management and format compatibility in a streamlined package.

Frequently Asked Questions
Why does Excel forget my overwrite cells setting?
This is a known behavior in certain versions of Excel where the connection properties dialog may fail to save the 'overwrite existing cells' option, reverting the QueryTable to its default refresh style of inserting cells.
Can I use different RefreshStyle properties in the VBA script?
Yes, besides xlOverwriteCells, you can also set the RefreshStyle to xlInsertDeleteCells to insert or delete cells matching the data size, or xlInsertEntireRows to push down entire rows of the spreadsheet when new data arrives.
How do I find my specific table name for the VBA macro?
Click any cell inside your data table, navigate to the Table Design tab on the ribbon, and look at the Table Name field on the far left side. Copy that exact name into the VBA script.




