AppGarden Change Diff logo AppGarden

Change Diff

A formula replaced by its own result: the Excel™ change that looks like no change

By Paul Kemp · Published

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.

  1. 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.
  2. Filter for TRUE.
  3. 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.
  4. 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.

See Change Diff for Excel™

Microsoft, Excel and Microsoft 365 are trademarks of the Microsoft group of companies. Change Diff is not affiliated with or endorsed by Microsoft.