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.