VBA & Macro Problems

How to Stop Excel Refresh from Overwriting Adjacent Cells

Olivia MillerOlivia Miller Nov 10, 2026 871 views

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.

How to Stop Excel Refresh from Overwriting Adjacent 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.
Before you start

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.

Solution 1Recommended

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.

1
Open the VBA Editor

Press ALT + F11 on your keyboard to open the Visual Basic for Applications (VBA) editor.

2
Insert a New Module

In the top menu, click Insert and select Module to create a blank script window.

3
Paste the VBA Code

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

4
Modify the Table Name and Run

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 a VBA Macro to Enforce Cell Overwrite Settings
Verification: Once the script is executed, try refreshing your external data connection to confirm that the adjacent cells are no longer being shifted or overwritten incorrectly.
Free Microsoft Office alternative

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.

Fully compatible with Microsoft Excel formats (.xlsx, .xls, .csv).Provides reliable data handling without complex configuration bugs.Familiar user interface ensures a zero-learning-curve transition.Lightweight architecture runs smoothly on both older and modern devices.
QA img-9

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.