Excel > Excel 2007 Advanced > Auditing Worksheets

Working with Formula Errors

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 Formulas tab on the Ribbon
  2. Click the Error Checking button in the Formula Auditing group
  3. 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 cells

    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
  • Evaluate a formula error
  • 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.

Excel errors




The numeric value is too wide to display in the cell. Resize the column width to fix. This error also appears for negative dates


The formula references a text entry instead of a numerical entry


The formula is trying to divide by 0 or a blank cell


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


The formula references a value that is not available to it yet. Commonly seen in Lookup and reference functions such as Vlookup


A cell reference is not valid. A cell range referenced within the formula has probably been deleted


The function contains an invalid argument


An intersection of two cell ranges has been specified that do not intersect

Follow us on

Facebook  Twitter  You Tube