Why do I get #REF! errors in my spreadsheet program?

Answer: If you see the text "#REF!" in Microsoft Excel or another spreadsheet program, it means the program has encountered a reference error.

Cells that contain #REF! have an invalid reference to another cell. This may be caused by mistyping cell labels or by syntax errors in formulas. References errors may also occur if formulas are copied and pasted between cells in different spreadsheets and the spreadsheets don't contain the same cells.

To fix a #REF! error, view the cell contents in the formula field, which is typically located near the top of the screen. If you can determine which cell the formula should be referencing, you can replace "#REF!" with the correct cell, such as A3, B5, or $C7. After you have manually entered the correct cell, the reference error should disappear and the cell should display the result of the formula.

Important: Other spreadsheet errors also start with a number sign (#). Some examples include #NULL! for null data, #DIV/0 for divide by zero errors, #NAME? for unrecognizable text, and ##### for invalid data. Each of these errors can be fixed by editing the cell formula and entering valid information.

Published: January 18, 2010 — by Per Christensson