Find formula in Excel

Find formula in Excel

If you are wanting to find formula in Excel, in order perhaps to better understand a spreadsheet, to protect the formula via a password or perform a spreadsheet review, there are a couple of ways of achieving this.

CTRL & ~ (Show Formulas)

If you are just interested in seeing the formula visually, then the Show Formulas button will do this for you. You can find it in the Formula Auditing Tool bar under the Formulas ribbon

Excel-$-sign-tips-and-tricks

This will allow you to toggle between a view of the results of the formula and a view of the formula themselves as shown below

Excel-$-sign-tips-and-tricks

GOTO Special Tool

The GOTO Special tool is a very useful tool that very few people understand. On the Home ribbon, on the far right is a Find and Select button. In this dropdown there is a GOTO Special button and a bit lower down the Formulas button.

The Formulas button is a shortcut to what you can do with the GOTO Special button. You can use the shortcut but have a look at what the GOTO Special can do. You will be amazed, especially when you realise it has always been in Excel, just not so prominently shown.

Spreadsheet Professional Tool

If you are doing a model review or audit and you need to identify formula to review them then we suggest you use a professional spreadsheet testing and auditing tool like Spreadsheet Professional.

It allows you to map a spreadsheet so that you can see where all the formula are as well as the numbers and text. This allows you to find inconsistencies in formula and focuses your attention on the important cells.

So can you find the error in this spreadsheet?

Maps-raw-spreadsheet

A map of the Unique formula and copied formula generated by Spreadsheet Professional would show the following. Notice how easy it is to see where it looks like a formula was rewritten instead of being copied across. You can also easily identify which formula to investigate and then rely on the copies made being correct.

Maps-of-spreadsheet-to-find-formula