Power Query/Overwrite cell question

[Missippy]

I have a large spreadsheet with 30 power queries on it. when i run some of them,not all, some of the calculations get changed and don't work. So I read that selecting the "Overwrite cells" in the external data properties would help. It does. The problem goes away. My question now is how to keep that selected? Right now, when I make the "Overwrite" selection, then run the query it goes back to being "unselected". I run these queries daily. Can it be set to always overwrite?

[Missippy · Accepted solution]

I didn't actualy get the problem solved. I did find an easy workaround. If some of you could actually see what its doing , I expect you could fix it. I am probably not doing a good enough job of describing it. But I am in a good spot with the form now. I appreciate the help. I am actually having a different issue that I may post when I have a chance.

[Missippy · Accepted solution]

I didn't actualy get the problem solved. I did find an easy workaround. If some of you could actually see what its doing , I expect you could fix it. I am probably not doing a good enough job of describing it. But I am in a good spot with the form now. I appreciate the help. I am actually having a different issue that I may post when I have a chance.

[v-hashadapu · Accepted solution]

Hi @Missippy , thank you for the update. We're glad to hear you found a workaround. Could you please share the details here and mark it as 'Accept as solution' to help others with similar issues? If you have any other questions, feel free to post them (as a new post) in the community. We're always happy to assist.

Thank you.

[v-hashadapu]

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!

[MarkLaf]

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'

[v-hashadapu]

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.

[Missippy]

It has not corrected my problem. Even charging it manually doesn't correct the problem. When I download the queries, some of them change the formula on some of the bottom few. I have found a work around that allows me to quickly correct the cells with the incorrect formulas. Thanks for all the suggestions and energy spent on my issue.

[v-hashadapu]

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

Since the Overwrite Cells option and VBA didn’t fully solve it, a more reliable fix is to keep your formulas separate from the query output. The best way to do this is to either load each query to its own sheet and reference it from your main sheet with formulas like =Query1_Data!A2 or place your queries in dedicated columns and keep your formulas in different, non-overlapping columns. This keeps Excel from touching your formulas when the data refreshes.

If you want to quickly fix overwritten formulas each day, you can also automate your workaround with a small macro. Just press ALT + F11 to open the VBA editor, insert a module and paste the code. Example:

Sub RestoreFormulas()

    Range("C2:C100").Formula = "=A2*B2" 'Update with your actual formula and range

End Sub

Run it with ALT + F8 after your queries refresh. Also, it’s worth checking your Excel version, older versions like Excel 2016 don’t handle these features as well, so upgrading to Microsoft 365 might help in the long run.

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.

[v-hashadapu]

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.

[SacheeTh]

Hi @Missippy ,

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

[Missippy]

yes, I do. Thanks

[v-hashadapu]

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.

[v-hashadapu]

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.

[Missippy]

I am sorry I am just now getting back to you. I have been down with the flu and I havent gotten much done. I will try the suggestions as soon as possible and respond back. I am very appreciative of all the help.

[v-hashadapu]

Hi @Missippy , No worries, happy to help. Hope you recover soon. 

[v-hashadapu]

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.

[SacheeTh]

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.

[Missippy]

I just tried this. It didnt refresh the queries. It went to the Debug screen. I am sure its me. This VBA is way over my head. No doubt I need to take a VBA class or 2. 

[Akash_Varuna]

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.

[Missippy]

Thak you for taking the time to respond. That option is not available on my excel. Maybe I have an older version? I can write the macro though. I hadnt thought of that. Thanks again