A formula replaced by its own result: the Excel™ change that looks like no change
Monday's file has a formula in D4. Friday's file shows the same number in D4, but it is now typed in. Compare the two by eye and they match. Change an input next week and that one cell stays where it was.
Why it slips through
A comparison of values sees the same value twice and reports nothing. Excel™'s own behaviour is no help either: the cell is a valid number, so nothing is flagged. Only a comparison of formulas between versions shows that D4 used to be a calculation and now is not.
Find it by hand
These steps apply to Excel™ in Microsoft 365. Copy the old sheet into the new workbook (right-click the tab, Move or Copy, "Create a copy") and call it Old.
- On a blank sheet, enter
=AND(ISFORMULA(Old!D4), NOT(ISFORMULA(New!D4)))and fill over the used range. TRUE means a formula became a value. - Filter for TRUE.
- For each hit, open the old formula (select the cell on Old and read the formula bar) and decide whether the new value is still right.
- Retype the formula in the new sheet if it was an accident.
This only finds one kind of change. A formula edited into a different formula, and a typed number changed to another typed number, need their own comparisons.
Report it at the top
Change Diff puts a formula replaced by a value (C-01) first in the list, with both forms quoted and a Show me button that selects the cell. It is the headline finding, not one line among thousands. Formula comparisons are free for the active sheet, up to 1,000 rows. The earlier version is read on your machine and uploaded nowhere.
Microsoft, Excel and Microsoft 365 are trademarks of the Microsoft group of companies. Change Diff is not affiliated with or endorsed by Microsoft.