TRACKING FORMULA ERRORS in SPREADSHEETS
Guest Post: J. Helstrom
Spreadsheet formula errors may cause other calculations to also yield an error message.
This is aggravating, especially if you’re working with a large worksheet.
Formula errors can be fixed - the hard part is finding all of them.
Luckily, Excel provides a handy tool for identifying formula errors. It’s embedded within the Find & Select icon on the Home ribbon.
Press Find & Select, then click on Go To Special…
or press the F5 key and then click Special....
The following dialog box appears:
Click the Formulas button and then deselect everything except the Errors check box as shown below:
Click on OK and all errors in the worksheet are now highlighted.
As an example, assume that a worksheet contains the following errors:
When Find & Select…Go To Special…Formulas…Errors is selected as shown above, the result is:
Excel has highlighted all the error messages.
If you haven't used Go To Special before - take a look at it. You can do a lot of cool things with it such as selecting visible cells only and it also allows you to find cells containing Conditional Formatting and Data Validations among other things.