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

Common error values in Excel formulas and how to fix them

In This Article

Jump to a Section

If Excel cannot properly evaluate a worksheet formula or function, it displays an error value (such as #REF!, #NULL!, or #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, help identify the problem.

Note: The information in this article applies to Excel versions 2019, 2016, 2013, 2010, 2007, Excel for Mac, and Excel Online.

Green Triangles and Yellow Diamonds

Excel displays a small green triangle in the upper left corner of cells containing error values. The green triangle indicates that the cell contents violate one of Excel's error checking rules.

Error drop-down dialog

When you select a cell containing a green triangle, a yellow diamond-shaped button appears 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 displays a text message, known as hover text, that explains the reason for the error value.

Listed below are 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. 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.

Screenshot of Excel showing a NULL error

#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.
=A1 A3+A5
  • The start and end points of cell ranges are separated by a space instead of by the range operator (the colon).
=SUM(A1 A5)
  • Individual cell references in a formula are separated by a space instead of the union operator (the comma).
=SUM(A1 A3,A5)
  • The intersect operator (the space character) is used intentionally, but the specified ranges do not intersect.
=SUM(A1:A5 B1:B5)

The solutions to these problems is to separate cell references correctly. Here are a few tips:

  • Separate cell references in a formula with a mathematical operator.
  • Separate the start and end points of a range with a colon.
  • Separate individual cell references in a formula with a comma.
  • Ensure that ranges separated by a space actually intersect.

#REF! Errors – Invalid Cell References

An invalid cell reference error occurs when a formula contains incorrect cell references.

Screenshot of Excel with REF error

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.

When you encounter a #REF! error, try these 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! Errors — Divide by Zero

Divide by 0 errors occurs when a formula attempts to divide by zero.

Screenshot of Excel showing DIV error

This can be caused when:

  • 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 references a cell that is blank.

When you encounter a #DIV/O! error, check for the following:

  • The correct data is in the cells referenced in the formula.
  • The data is in the correct cells.
  • The correct cell references are used in the formula.

##### Error – Cell Formatting

A cell filled with a row of hashtags (also called number signs or pound symbols) is not referred to as an error value by Microsoft. It is caused by the length of data entered into a formatted cell.

Screenshot of Excel showing hashtags in a cell

The row of ##### occurs in a variety of instances. For example:

  • An entered value is wider than the current cell width for a cell formatted for dates or times.
  • 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.

Here's how to fix a ##### error:

  • 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 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.
Was this page helpful?