Someone changed my Excel™ model: how to find what changed since the last version
A colleague sends back the workbook. The totals look about right, but you need to know what changed. Excel™ does not keep a list of edits to an ordinary saved file, so you have to compare two versions yourself.
What to compare
A change can be a value (an input moved), a formula (logic changed) or a formula replaced by a value (a calculation typed over). The last is the dangerous one: the number may look identical and never update again. Compare formulas, not only results.
By hand
These steps apply to Excel™ in Microsoft 365. Keep both files untouched and work in a third.
- Copy the old sheet into the new workbook as a new sheet (right-click the tab, Move or Copy, tick "Create a copy").
- On a blank sheet, compare values:
=IF(Old!A1=New!A1,"",Old!A1&" -> "&New!A1)and fill across the used range. - Compare formulas:
=IF(FORMULATEXT(Old!A1)=FORMULATEXT(New!A1),"","changed"). FORMULATEXT returns an error where a cell has no formula, so wrap it inIFERROR. - Filter for non-blanks.
- Look separately at Formulas → Name Manager for names that now point somewhere else.
Why inserted rows ruin this
Insert one row near the top and every cell below is compared with its neighbour, not its partner. The comparison reports thousands of differences that are really one insertion. Align the two sheets first, or compare only the part of the sheet above the insertion.
Compare by formula without the false alarms
Change Diff compares the open workbook with an earlier file you choose, aligns the sheets before comparing, and compares formulas in R1C1 form. It reports four classes: a formula replaced by a value (C-01), a formula changed (C-02), a value changed (C-03) and formatting only (C-04, off by default). Cells that merely recalculated are counted, not listed. Neither file is written to. The free tier compares the active sheet, up to 1,000 rows, for formula changes.
Microsoft, Excel and Microsoft 365 are trademarks of the Microsoft group of companies. Change Diff is not affiliated with or endorsed by Microsoft.