Excel Formulas Change to Values When Anything is Pasted Anywhere in the File

[Arleigh_Hays]

I have an Excel spreadsheet with two tabs: Summary and Data. The Summary worksheet has several tables that use COUNTIFS formulas. I started noticing that my formulas were randomly changing to values. I'm on a deadline so I put it down to user error/moving too fast but it began happening over and over.

In the process of troubleshooting (recreating the problem in new files with new data etc.), I narrowed the problem down to the paste function. Executing the paste function in any capacity appears to change formulas to values.

I can be on the Summary tab, checking totals and everything works smoothly. Then I switch over to the Data tab to enter information. I often copy and paste cells across the various columns as updated information comes in. After working a while on the Data tab, I come back to the Summary tab to find the formulas gone with only values remaining. I can fix it, create a new worksheet or a new file, but it will happen again. I can even copy a simple value or word and paste into an unrelated cell and the formulas will switch to values. 

I've used Check for Issues function in Excel on the files and run office repair multiple times. I even reinstalled Office. Windows is up to date, windows integrity checks find nothing, and other common troubleshooting dont reveal a problem.

I did see one thing that may or may not be related and was able to grab a screenshot:

But I couldn't see the whole message. This appeared after the formulas changed to values.

We were previously on O365 which has now changed to M365 and there was an Office update this week. I believe my problem happened after but it could be coincidental. I have M365 Enterprise Apps version 2303, Build 16227.20318.

Any help would be appreciated.

[#2]

  @Arleigh_Hays 

Same problem here.   

Brand new spreadsheet. Autosum a list of numbers. Formula works fine.  Copy the autosummed cell and you get the original sum value instead of a (relative) formula.

It's a sad day when Excel cannot copy and paste a formula. Cannot be classed as a spreadsheet until this is fixed. Meanwhile your subscription fee continues to burn down for a product that doesn't do the basic function.
Booked a restaurant the other night and was asked to prepay. When I got there I found out they didn't serve food... Microsoft Cafe  ;) 

[#3]

Hi There,
I have the same issue and it's really inconvenient. Also not too promising that no-one found a solution for it in half a year (and not much people complaining either).

I tore up my excel settings and I couldn't find an option remotely close to this issue.
:(

[#4]

@KuEva All this time later and still no real fix! 

Its not a solution by any means but, save and close the doc, then open it again and try the paste. 

This worked for me, it must be a bug that finds its was into your current file but goes after a save and close. 

Hopefully this works for you.