Power Query/Overwrite cell question

Hi Missippy To keep the "Overwrite cells" option selected, go to Data > Queries & Connections, right-click the query, choose Properties, and enable "Overwrite cells." Ensure you save the workbook after making this change to try persisting the setting. If the option resets, use a VBA macro to automate enabling "Overwrite cells" for all queries in your workbook.

Hi Missippy , pls Try Using the VBA to Force Overwrite
Since Excel sometimes resets the Overwrite Cells setting, you can automate this using a VBA macro.

Open VBA Editor

 - Press ALT + F11 to open the Visual Basic for Applications editor.
Insert a New Module

- Click Insert > Module.

Add the Follewing VBA

Sub RefreshQueriesWithOverwrite()
    Dim ws As Worksheet
    Dim qt As QueryTable
    Dim lo As ListObject
    
    ' Loop through all worksheets
    For Each ws In ThisWorkbook.Worksheets
        ' Refresh all QueryTables
        For Each qt In ws.QueryTables
            qt.RefreshStyle = xlOverwriteCells ' Ensures overwrite
            qt.Refresh
        Next qt
        
        ' Refresh all Table Queries (ListObjects)
        For Each lo In ws.ListObjects
            If Not lo.QueryTable Is Nothing Then
                lo.QueryTable.RefreshStyle = xlOverwriteCells ' Ensures overwrite
                lo.QueryTable.Refresh
            End If
        Next lo
    Next ws
End Sub

 Run the Macro

• Close the editor and run RefreshQueriesWithOverwrite() from the Macros window (ALT + F8).

_____
Try Refresh Queries via Power Automate (for Office 365) - as Akash_Varuna suggested (windows only)

If your Excel workbook is connected to Power Automate, you can create a flow that triggers the refresh with the overwrite setting enabled.

Solution 3: Convert Queries to Tables with Automatic Overwrite

• Instead of loading data directly into cells, load the query into a Table (Load to → Table).
• Excel automatically overwrites table rows instead of resetting the Overwrite Cells setting.

Hi Missippy , Thank you for reaching out to the Microsoft Community Forum.

Since the "Overwrite Cells" setting does not persist automatically in your Excel version, the best solution is to use VBA to enforce it every time you refresh queries. You can use the macro provided by SacheeTh , which ensures all queries overwrite cells when refreshed. If you prefer a manual method, consider converting your queries to tables, as Excel automatically overwrites table rows.

RefreshStyle Property [Excel 2003 VBA Language Reference]

Thank you SacheeTh , Akash_Varuna for providing valuable answers.

If this helped solve the issue, please consider marking it 'Accept as Solution' so others with similar queries may find it more easily. If not, please share the details, always happy to help.
Thank you.

Hi Missippy , Thank you for reaching out to the Microsoft Community Forum.

Please let us know if your issue is solved. If it is, consider marking the answer that helped 'Accept as Solution', so others with similar queries can find it easily. If not, please share the details.
Thank you.

Hi Missippy , Please let us know if your issue is solved. If it is, consider marking the answer that helped 'Accept as Solution', so others with similar queries can find it easily. If not, please share the details.
Thank you.

Hi Missippy ,

I hope you do Unblock before opening the file from the properties right?

Hi @Missippy , Sorry to disturb you. hope you are recovered and your issue is solved. If it is, consider marking the answer that helped 'Accept as Solution', so others with similar queries can find it easily. If not, please share the details.
Thank you.

Hi Missippy , Just wanted to know if your issue is solved. If it is please mark the the answer the that helped "Accept as Solution", so others with similar issues may find it easily. Thank you.

If you mean that sometimes formulas in added columns to your table will sometimes not copy all the way down after a refresh, I've found that this can be fixed by loading into your sheets what the following article refers to as 'Reverse Linked' tables: https://www.sqlbi.com/articles/linkback-tables-in-powerpivot-for-excel-2013/

Basically:

1. Load your query to the data model
2. Insert a table from the data model using existing connections
3. Right-click on the table, select Table > Edit DAX...
4. In pop-up window, change Command Type to DAX
5. Still in pop-up, in expression field, use a DAX expression that evaluates to a table. Simplest is:
   EVALUATE
   'table name'

Hi Missippy , Just checking in—were you able to resolve the issue?
If one of the replies helped, please consider marking it as "Accept as Solution" and giving a 'Kudos'. Doing so can assist other community members in finding answers more quickly.
Thank you!