excel formula debugging tools

Does anyone know of a method to compare formulas between two spreadsheets? I have an old and a new version of a spreadsheet where numerous formulas have changed across multiple pages. I need to highlight or list the formula changes between the two spreadsheets. The actual data is also changing, but I am concerned about the formulas in this case.

One way I might do this is to do a find and replace on “=” and change them all to a character I know isn’t in use in the books, usually “#”. This changes all your formulas to text.

Then add formulas like “=A1=[Other Workbook]Sheet1!A1”. If the text in A1 matches A1 in the other book, it’ll result as TRUE.

When done, delete the new formulas and do a find and replace with “#” changing back to “=”.

No idea if this is the best or fastest way, but it’s fairly simple and should do the job if I understand you correctly. Obviously, you want to back up your work before starting.

good idea. I do similar things in other contexts, the idea might work here as well.
I am really hoping for an excel command, or an add-on, or something external to work the problem, but your idea could well work.

I looked at the top search result for “excel compare workbook formulas” and add ons do exist that compare workbooks, but I couldn’t tell if they actually compare formulas. I think you might have to still do the find and replace trick. Easy to record a macro for that. How many workbooks are we talking about?

Just 1 workbook, about 200 pages.
One little detail which I didn’t mention is that the spreadsheet moves around among PCs, Macs, and Linux (Open Office) machines. I live in a mixed environment! :slight_smile:

Fortunately I only need the formula checking on one platform-but it would be easiest to do the checking on a Mac since that is what I have at home.

From everything I have been able to find, the trick upthread is the best way to accomplish the task. That is strange to me. It seems like such a useful, indeed critical, need.