What does the #REF! error mean and how do I fix it?
Short answer
The #REF! error means a formula refers to a cell, range or sheet that no longer exists or is not valid. It usually appears after you delete rows, columns or sheets that a formula used, or paste over referenced cells. Undo the change, or edit the formula and replace the #REF! token with a correct reference.
#REF! stands for "reference". The spreadsheet could not follow a reference in the formula. It is different from #NAME? (unknown function or name) and #VALUE! (wrong type of data).
What causes a #REF! error in a formula?
- A referenced cell, row or column was deleted.
- A sheet that a formula pointed to was deleted.
- Cells were cut and pasted over a referenced cell.
VLOOKUPwas given a column index larger than the number of columns in its table.INDEXorOFFSETpoints outside the sheet or outside its range.- A formula was copied so that a relative reference moved above row 1 or left of column A.
- A link to another workbook points to a file or sheet that is missing.
What does a #REF! error look like inside a formula?
Suppose C2 holds =A2+B2. If you delete column B, the formula becomes:
=A2+#REF!
If you delete a sheet that a formula used, a reference like =Sheet2!A1 becomes =#REF!A1. The error is written into the formula itself, so reinserting the column does not repair it.
How do I fix a #REF! error once it appears?
- Undo right away. Press Ctrl+Z immediately after the deletion. This is the only way to restore the original references automatically.
- Otherwise click the cell and read the formula in the formula bar. Find
#REF!, select it and type the correct reference or click the cell you want. - To find every error in a sheet in Excel, choose Home > Find & Select > Go To Special, tick Formulas, clear everything except Errors, and click OK. Or press Ctrl+F, set Look in to Formulas, and search for
#REF!. - Fill the corrected formula down or across.
In Google Sheets, hover over the red corner of the cell. The tooltip states the cause, for example "Reference does not exist". Sheets also uses #REF! for a circular dependency, and for an array formula that would overwrite data (the message says the result was not expanded). With IMPORTRANGE it appears until you click Allow access to connect the sheets.
LibreOffice Calc also shows #REF! for an invalid reference (internal error code 524). A circular reference is a different error, Err:522.
In Numbers, a formula whose source cell was deleted is flagged with a warning icon, and its formula editor marks the missing reference. Open the formula and re-select the cells.
What happens to a SUM formula when rows 2 to 6 are deleted?
A sheet has prices in B2:B6 and a total =SUM(B2:B6). Deleting row 6 shrinks the range to =SUM(B2:B5), which is fine. Deleting rows 2 to 6 removes the whole range, so the formula becomes =SUM(#REF!) and shows the error. To avoid it, clear the contents of rows you want to empty, or delete rows only after checking what depends on them (Formulas > Trace Dependents in Excel).
How can I prevent #REF! errors from appearing?
- Clear contents instead of deleting rows and columns that other formulas use.
- Use named ranges or Excel Tables, which adjust more safely than scattered single-cell references.
- Replace
VLOOKUPcolumn numbers withXLOOKUPorINDEX/MATCH, which do not break when columns move. See VLOOKUP or XLOOKUP. - Do not wrap formulas in
IFERRORto hide#REF!. The formula is still broken, and the wrong answer will look right. - Keep a copy before restructuring a workbook. Version history in Google Sheets and Excel can restore an earlier state.
Good structure helps too: separate inputs, calculations and outputs, as described in spreadsheet modeling, so you can see what a deletion will touch.