Saturday, October 6, 2012

Working with Formula Errors in Microsoft Excel 2003

The Error Checking feature of Excel helps us to deal with formula errors. A formula error appears whenever a formula cannot function and complete its task.

Error Checking

Error checking can be performed on an individual cell or on the entire worksheet.
To error check an individual cell:
  1. Select the cell containing the formula error
  2. Point to the smart tag icon that appears in the corner of the cell
  3. A tip appears explaining why you are getting this type of error. Click on the smart tag to display a list of error checking options.
    Error Checking options
To error check the worksheet:
  1. Click the Error Checking button on the Formula Auditing toolbar
  2. Error Checking buttons on toolbar
    Excel selects the first cell on the worksheet containing an error and displays the Error Checking dialogue box. The formula is shown along with an explanation of why you are getting this type of error.
    Error checking
    The dialogue box contains various error checking buttons.
  • Help on this error: Displays a help topic explaining the type of error you are seeing
  • Show Calculation Steps: Displays the Evaluate Formula dialogue box breaking down the formula arguments to isolate the error. Click Step In to examine the source of the particular argument
  • To evaluate a formula, you can also click the Evaluate Formula button on the Formula Auditing toolbar.
  • Ignore Error: Skip this error and moves onto the next one in the worksheet
  • Edit in Formula Bar: Places the cursor in the Formula Bar where you can directly edit the formula
  1. Click the button you want to use to fix the formula error
Click the Options button in the Error Checking dialogue box to change the error checking rules.
Error checking options

Excel errors

Error Reason
##### The numeric value is too wide to display in the cell. Resize the column width to fix. This error also appears for negative dates
#VALUE! The formula references a text entry instead of a numerical entry
#DIV/0! The formula is trying to divide by 0 or a blank cell
#NAME? The formula contains text that it does not recognise. Commonly displayed because a function name or range name has been misspelt. Also if a text entry is not enclosed in double quotation marks
#N/A The formula references a value that is not available to it yet. Commonly seen in Lookup and reference functions such as Vlookup
#REF! A cell reference is not valid. A cell range referenced within the formula has probably been deleted
#NUM! The function contains an invalid argument
#NULL! An intersection of two cell ranges has been specified that do not intersect

No comments:

Post a Comment