Best Microsoft Excel Bloggers

Wednesday, February 5, 2014


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.



No comments:

Post a Comment


Ms. Excel- Resident Excel Geek