Excel overwriting my formulas

Question

Even after turning off "Extend data range formats and formulas," Excel still overwrites my formulas with other formulas added to other cells in the same column. Am I missing another setting, or something?

Your help is very appreciated.

Answer 1

Hi benhaminHN,

Thank you for querying in this forum. We understand the inconvenience caused and apologize for it. And we will try our best to help you.

According to your description, it seems that your Excel will overwrite your formulas with the other formulas added to the other cells in the same column. And you have tried turning off "Extend data range formats and formulas".

If my understanding is right, we have tested it on our side, we created a simple Excel file and we typed different formulas in different cells under same column, and we cannot reproduce the same situation as yours. As shown below:

Given this situation, we’d give you some suggestions below and hope that will help you:

1. We assume that you may use Autofill in Table formulas, to prevent this situation, we’d suggest you go to Excel > File > Options > Advanced > unselect Enable fill handle and cell drag-and-drop. Then you can also go to Excel > File > Options > Proofing > AutoCorrect Options > AutoFormat As You Type > unselect Fill formulas in tables to create calculated columns.
2. If the suggestion above doesn’t work, you can open your Excel in safe mode to check if the same problem will occur. It will help in identifying an add-in or extension that may be causing a problem on startup. As steps shown below: Right-click the Start button (lower-left corner) in Windows, and click Run.> In the Run box, type one of the command: excel /safe
3. We’d also suggest you go to Control Panel > Programs > Programs and Features > select Office> right-click and select Change > Online repair.

If all suggestions above don’t work, to better understand your situation, could you provide the information below?

1. Could you please share a screenshot of your Product Information and version of your Office? You can go to Excel > File > Account and provide the full screenshot of Account page. As shown below:
2. May I know if all Excel files have the same problem? You can create a new Excel file to check if the same problem will occur.

Best Regards,

Sukie

Answer 2

Adding formulas via Flow, makes all formulas the same as the most recent formula.

Adding formulas by hand, only effect that cell.

I feel like we are getting closer...

I really appreciate your help, Sukie. What are my next steps?

Answer 3

Hi BenjaminHN,

We appreciate that you posted back and shared the detailed information with us. We also appreciate that you spent your precious time doing those tests.

We have read your description carefully, it seems that if you use Microsoft Flow to add formulas in Excel file, it will make all formulas to show the same formula as the most recent formula, however, if you add formulas manually, this behavior will not happen.

If my understanding is right, this issue may be related to Microsoft Flow. Since our category focus on Office 365 products built-in features, to make sure you get professional help, we’d suggest you go to Power Automate Community to post a new thread, that’s a specific channel to handle related questions. And the engineers will also focus on your specific scenario above and provide specific suggestions.

If the scenario above is not consistent with yours, you can also post back and provide more details about the situation you encountered and we will also try our best to help you.

Your understanding and patience will be highly appreciated.

Best Regards,

Sukie

Answer 4

Sukie,

What you describe is correct.

I have spoken with a Flow support agent, and he said that, since the formula is being added to the Excel sheet correctly and then changed by Excel, that this is something that Excel is doing, and I should ask an Excel support person. 

I may need two support people to talk to each other, one from Excel and one from Flow.

In any case, we can all see that this is something that Excel is doing. I think you will agree that I can't just go back and forth between your two departments and not ever get any help.

I look forward to hearing back from you with some help.

Answer 5

Hi BenjaminHN,

Thank you for posting back and sharing those information with us. I do understand the inconvenience it has made and apologize for it. 

It seems that you have contacted with Power Automate support, and the supporter mentioned that this issue may be related to Excel client. Given this situation, we understand the inconvenience caused by running back and forth between the two forums. And given this situation, we will also continue to try our best to help you.

To better understand your situation and test it on our side to check if we can reproduce the same situation as yours, could you provide the information below?

1. Could you provide the detailed steps about how you used Power Automate Flow to add formulas?
2. Could you please share a screenshot of your Product Information and version of your Office? You can go to Excel > File > Account and provide the full screenshot of Account page. As shown below:
3. May I know if all Excel files have the same problem? You can create a new Excel file to check if the same problem will occur.

Thank you for your patience so far.

Best Regards,

Sukie