Error ExplainerExcel #REF! error
A plain-words explanation of this Excel message: what it means, the usual causes, and the steps that fix it.
The message#REF!
Make the error yourself
Typed into Microsoft Excel 16.0 build 20326. This is the smallest example we could find that shows it.
| Cell | What was typed |
|---|---|
| A1 | 5 |
| B1 | =A1*2 |
Then column A was deleted.
The cell looked at: A1.
Excel shows: #REF!
Why it happens
The formula points at a cell that no longer exists. Most often the cell, row, column or sheet that the formula used was deleted: in the example column A was deleted and the formula that used A1 now holds #REF!. It also appears when you copy a formula with a relative reference to a place where it would point outside the sheet (for example above row 1), when a VLOOKUP asks for a column number larger than the range, and when a link to another workbook cannot be reached.
How to fix it
- Press Ctrl+Z immediately after a deletion. It restores the references.
- Otherwise click the cell and read the formula: the broken part reads
#REF!instead of an address. Type the correct address back. - For
VLOOKUP, check that the column number is not larger than the number of columns in the range. - Avoid deleting cells that others use. Clear their contents instead, or hide the column.
- For lists that grow and shrink, use a Table (Ctrl+T) or
INDEXso formulas follow the data.
The corrected version
| Cell | What was typed |
|---|---|
| A1 | 5 |
| B1 | =A1*2 |
The cell looked at: B1.
Excel shows: 10
How to avoid it
Before you delete a row or column, use Formulas > Trace Dependents to see what uses it.
Questions
Can Excel repair #REF! automatically?
Not after you save and close. Only Undo restores the original reference.
This is an independent guide. It is not connected with Python, Microsoft, Google, GitHub or the Git project. Product names belong to their owners. Examples were run on 13 November 2026 in a practice folder or a blank workbook; your version may word a message slightly differently.