#NULL!, #REF!, #DIV/0!, and ##### Errors in Excel

Common Error Values in Excel Formulas and How to Fix Them

Excel common error values
#NULL!, #REF!, #DIV/0!, and ##### Errors in Excel - Common Error Values in Excel Formulas and How to Fix Them. © Ted French

Common Error Values in Excel Formulas

If Excel cannot properly evaluate a worksheet formula or function; it will display an error value - such as #REF!, #NULL!, #DIV/0! -  in the cell where the formula is located.

The error value itself plus the error options button, which is displayed in cells with error formulas, gives some help in identifying the problem about the problem.

Green Triangles and Yellow Diamonds

Excel will display a small green triangle in the upper left corner of cells containing error values - cells D2 to D9 in the image above.

The green triangle indicates that the cell's contents violate one of Excel's error checking rules.

Clicking on a cell containing a green triangle will cause a yellow diamond-shaped button to appear next to the triangle. The yellow diamond is Excel's error options button  and it contains options for correcting the perceived error.

Hovering the mouse pointer over the error options button will display a text message - known as hover text - that explains the reason for the error value.

Below are listed common error values displayed by Excel, along with some common causes and solutions to help correct the problem.

#NULL! Errors - Incorrectly Separated Cell References

#NULL! error values occur when the two or more cell references are separated incorrectly or unintentionally by a space in a formula - rows 2 to 5 in the image above.

In Excel formulas, the space character is used as the intersect operator, which means it is used when listing two or more intersecting or overlapping ranges of data - such as: A1:A5 A3:C3 (the cell reference A3 is part of both ranges, so the ranges do intersect).

#NULL! errors occur if:

  • multiple cell references in a formula are separated by a space instead of a mathematical operator such as a plus sign ( + ) - example: =A1 A3+A5;
  • the start and end points of cell ranges are separated by a space instead of by the range operator - the colon ( : ) - example: =SUM( A1 A5);
  • individual cell references in a formula are separated by a space instead the union operator - the comma ( , ) - example: =SUM( A1 A3,A5);
  • the intersect operator - the space character - is used intentionally, but the specified ranges do not intersect - example: =SUM(A1:A5 B1:B5)

Solutions: Separate cell references correctly.

  • separate cell references in a formula with a mathematical operator - example: =A1+A3+A5;
  • separate the start and end points of a range with a colon ( : ) - example: =SUM(A1:A5);
  • separate individual cell references in a formula with a comma - example: =SUM(A1,A3,A5);
  • ensure that ranges separated by a space actually intersect - example: =SUM(A1:A5 A3:C3).

#REF! Errors - Invalid Cell References

An invalid cell reference error occurs when a formula contains incorrect cell references - rows 6 and 7 in the example above. This happens most often when:

  • individual cells or entire columns or rows containing data referenced in a formula are accidentally deleted;
  • data from one cell is moved (using cut and paste or drag and drop) into a cell that is referenced by a formula;
  • a formula contains a link - using OLE (Object Linking and Embedding) - to a program that is not currently running.

    Solutions:

    • use Excel's undo feature to recover data lost in deleted cells, columns or rows;
    • if the data cannot be recovered, re-enter the data and adjust the cell references if needed;
    • open programs containing OLE links and update the worksheet containing the #REF! error.

    #DIV/O! - Divide by Zero Error

    Divide by 0 errors occur when a formula attempts to divide by zero - rows 8 and 9 in the image above. This can be caused when:

    • the the divisor or denominator in a division operation is equal to zero - either explicitly - such as =A5 / 0 - or as the result of a second calculation that has zero for a result;
    • a formula referencing a cell that is blank.

    Solutions:

    • Check that you have the correct data in the cells referenced in the formula.
    • Check that your data is in the correct cells.
    • Check that the correct cell references were used in the formula.

      ##### Error - Cell Formatting

      A cell filled with a row of hash tags, number signs, or pound symbols as they are also called, is not referred to as an error value by Microsoft, but is said to be caused by the length of data entered into a formatted cell.

      Therefore, the row of ##### occurs in a variety of instances, such as when:

      • an entered value is wider than the current cell width for a cell formatted for dates or times - row 10 in the image above;
      • a formula entered into the cell formatted for numbers produces a result that is wider than the cell;
      • a number or text data, in excess of 253 characters,  is entered into a cell formatted for numbers dates, times, or accounting;
      • a negative number resides in a cell that has been formatted for dates or times. Dates and times in Excel must be positive values - row 11 in the image above.

      Solutions:

      • Widen the affected cell by widening the column (individual cells cannot be widened without widening the entire column);
      • Shorten the length of the data in the cell if possible or choose a different format for the cell such as General;
      • Correct the date or time value in the affected cell so that the result is not negative;
      • Correct the formula that results in a negative time or date value to be displayed in the affected cell;