"OVERWRITE EXISTING CELLS WITH NEW DATA..." GET DESELECTED

I went on ChatGPT and it gave me a fix that works:

🛠 What You Can Do

✅ Switch to Legacy Wizard (Classic Behavior)

You can re-enable the old import wizard that does remember settings like it used to:

1. Go to:
   File → Options → Data
2. Under Show legacy data import wizards, check:
   ✅ From Access
3. Now go to Data → Get Data → Legacy Wizards → From Microsoft Access Database
4. The old interface should remember the overwrite setting just like it did in 2002–2013.

🧩 TL;DR

• You're not imagining it — this did work consistently for 20 years.
• Microsoft changed the underlying import engine.
• The new system is more cautious and doesn’t persist all settings.
• You can bring back the old behavior by enabling legacy wizards in Excel settings.

Hi, i use workaround with VBA:

Sub SetExternalDataProperties()
' Change TableName to yours table name
Range("TableName[[#All]]").Select
        With Selection.ListObject.QueryTable
            'set true or false
			.RowNumbers = False
            .AdjustColumnWidth = False
            .PreserveColumnInfo = True
			.PreserveFormatting = True
'set one of three XlCellInsertionMode 			
			.RefreshStyle = xlOverwriteCells ' Possible:
'xlInsertDeleteCells	1	Partial rows are inserted or deleted to match the exact number of rows required for the new recordset.
'xlInsertEntireRows	2	Entire rows are inserted, if necessary, to accommodate any overflow. No cells or rows are deleted from the worksheet.
'xlOverwriteCells	0	No new cells or rows are added to the worksheet. Data in surrounding cells is overwritten to accommodate any overflow.

            
        End With
End Sub

After sitting all parameters just run macro and its sets them without crashing.

Hi gopir, there is no updates. I do not have a way to contact Microsoft. The work around I am using is to delete the query and using get data from access database to put it back in each week to update with the new data.

Do we have any updates on this issue? From Microsoft or any work around?

Thankyou so much for posting this!  I'm having the exact same problem.  "Overwrite existing cells" was checked in several power queries in different workbooks.  One of the workbook was having a problem and now thanks to you I see that excel automatically unchecks it when I close the External Data Properties configuration popup. The other workbooks are fine. 

I'll throw in that the option was removed from the Query->Properties menu, had to go to Data->Queries & Connections->Properties to find it again.

May consider below before contacting Microsoft support:

• Update Office 365: Ensure your Office 365 application is up to date. Sometimes bugs are fixed in newer updates.
• Check for Add-ins: Disable any add-ins that might be interfering with Excel's functionality. Go to File > Options > Add-ins and disable them temporarily to see if the issue persists.
• Repair Office Installation: You can try repairing your Office installation. Go to Control Panel > Programs > Programs and Features, select Office 365, and choose "Change" then "Repair."
• Recreate the Data Connection: Sometimes recreating the data connection can help. Delete the existing connection and set it up again from scratch.