# excel formula debugging tools **URL:** \ **Category:** Factual Questions\ **Created:** [October 20, 2011, 1:52am UTC](https://boards.straightdope.com/t/excel-formula-debugging-tools/600212 "2011-10-20T01:52:59Z")\ **Posts on this page:** 5\ **Page:** 1
**Author:** ![rbroome](https://avatars.discourse-cdn.com/v4/letter/r/838e76/32.png) [@rbroome](https://boards.straightdope.com/u/rbroome)\ **Post date:** [October 20, 2011, 1:52am UTC](https://boards.straightdope.com/t/excel-formula-debugging-tools/600212/1 "2011-10-20T01:52:59Z")
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. Thanks ---
**Author:** ![Dave\_Hartwick](https://avatars.discourse-cdn.com/v4/letter/d/7feea3/32.png) [@Dave\_Hartwick](https://boards.straightdope.com/u/Dave_Hartwick)\ **Post date:** [October 20, 2011, 2:28am UTC](https://boards.straightdope.com/t/excel-formula-debugging-tools/600212/2 "2011-10-20T02:28:19Z")
> [@rbroome](#): > > 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. > Thanks 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. ---
**Author:** ![rbroome](https://avatars.discourse-cdn.com/v4/letter/r/838e76/32.png) [@rbroome](https://boards.straightdope.com/u/rbroome)\ **Post date:** [October 20, 2011, 2:43am UTC](https://boards.straightdope.com/t/excel-formula-debugging-tools/600212/3 "2011-10-20T02:43:14Z")
> [@Dave\_Hartwick](#): > > 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. ---
**Author:** ![Dave\_Hartwick](https://avatars.discourse-cdn.com/v4/letter/d/7feea3/32.png) [@Dave\_Hartwick](https://boards.straightdope.com/u/Dave_Hartwick)\ **Post date:** [October 20, 2011, 11:55pm UTC](https://boards.straightdope.com/t/excel-formula-debugging-tools/600212/4 "2011-10-20T23:55:55Z")
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? ---
**Author:** ![rbroome](https://avatars.discourse-cdn.com/v4/letter/r/838e76/32.png) [@rbroome](https://boards.straightdope.com/u/rbroome)\ **Post date:** [October 21, 2011, 12:03pm UTC](https://boards.straightdope.com/t/excel-formula-debugging-tools/600212/5 "2011-10-21T12:03:35Z")
> [@Dave\_Hartwick](#): > > 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! 🙂 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.