When a defined name is repointed and no formula text changes in Excel™
A formula reads =B4*Uplift. It reads the same this week and last week. But last week Uplift pointed at one cell, and this week it points at another. Nothing in any formula was edited, and every result that uses the name has moved. (The name and cells here are illustrative.)
Why it hides
A comparison of formula text sees identical text. The change lives in the name's definition, which is held outside every cell. The same is true of a change in a lookup range that was set by a name.
Check by hand
These steps apply to Excel™ in Microsoft 365.
- Open the current workbook and choose Formulas → Name Manager.
- Read down the Refers To column. Note each name and its target.
- Open the earlier version and do the same.
- Compare the two lists. Names that exist in one and not the other, or that point to a different range, are the changes.
- For any name that moved, find the formulas that use it: press Ctrl+F, set Look in to Formulas, and search for the name.
- Decide whether the new target is intended.
For a long list, choose Formulas → Use in Formula → Paste Names, then Paste List in the dialog, to put every name and its target on a sheet. Do it in both files and compare the two lists.
Let the comparison include names
Change Diff's structure detection (a paid feature) reports inserted and deleted rows and columns, sheets added, removed, renamed or reordered, and defined names repointed. The free tier compares the active sheet for formula changes only. Neither workbook is modified, and the report is written as four new sheets: Summary, Changes, Structure and Audit.
Microsoft, Excel and Microsoft 365 are trademarks of the Microsoft group of companies. Change Diff is not affiliated with or endorsed by Microsoft.