How to Fix #REF! Error in Excel — 5 Causes & Solutions

The #REF! error means Excel can't find a cell or range that your formula references. It's usually caused by deleting something a formula depends on. Here's how to fix every version of it.

Cause 1: Deleted Row or Column

The problem: You had =A2+B2+C2, then deleted column B. The formula becomes =A2+#REF!+C2.

The fix: Press Ctrl+Z immediately to undo. If it's too late, manually edit the formula and replace #REF! with the correct cell reference.

Prevention: Before deleting rows/columns, press Ctrl+` (backtick) to show all formulas. Check if anything references the row/column you're about to delete.

Cause 2: VLOOKUP Column Number Too Large

The problem: Your VLOOKUP asks for column 5, but your table range only has 4 columns.

=VLOOKUP(A2, B2:E10, 5, FALSE)  → #REF!
                         ↑ range has 4 cols, but you asked for col 5

The fix: Either expand the table range or reduce the column number:

=VLOOKUP(A2, B2:F10, 5, FALSE)  — expanded range to include col 5
=VLOOKUP(A2, B2:E10, 4, FALSE)  — or use correct column number

Pro tip: Use =COLUMNS(B2:E10) to verify how many columns your range has.

Cause 3: Pasting Over Referenced Cells

The problem: You copied data and pasted it over cells that other formulas reference. Cut+Paste (Ctrl+X) can also cause this if it shifts references.

The fix: Ctrl+Z to undo. Then use Copy + Paste Values (Ctrl+Alt+V → V) instead, which doesn't affect cell references.

Cause 4: INDIRECT Pointing to Invalid Reference

The problem: INDIRECT builds a reference from text. If the text doesn't form a valid cell address, you get #REF!

=INDIRECT("Sheet2!A1")   → #REF! if "Sheet2" doesn't exist
=INDIRECT(A1)            → #REF! if A1 contains "XYZ" (not a valid cell)

The fix: Check the text value being passed to INDIRECT. Make sure sheet names are spelled correctly and cell addresses are valid.

=IFERROR(INDIRECT("'" & A1 & "'!B2"), "Sheet not found")

Cause 5: Circular Reference

The problem: A formula references its own cell, directly or through a chain. Excel can't calculate it and shows #REF! or a circular reference warning.

Cell A1: =A1+1       — directly references itself
Cell A1: =B1+1
Cell B1: =A1+1       — A1→B1→A1 circular chain

The fix:

• Go to Formulas → Error Checking → Circular References — Excel shows you exactly which cell has the loop
• Break the chain by changing one formula to reference a different cell
• If you NEED circular logic, enable iterative calculation: File → Options → Formulas → Enable Iterative Calculation

How to Find All #REF! Errors in Your Workbook

1. Press Ctrl+H (Find & Replace)
2. In "Find what" type: #REF!
3. Click "Find All" — Excel lists every cell with this error
4. Fix them one by one, or use Ctrl+Z repeatedly to undo back to when it worked

Prevention Rules

• Never delete rows/columns without checking formulas first — use Ctrl+` to reveal formulas
• Use named ranges — =SUM(SalesData) is safer than =SUM(B2:B100) because named ranges auto-adjust
• Use Tables (Ctrl+T) — structured references like =SUM(Table1[Sales]) survive row/column insertions and deletions
• Lock your VLOOKUP ranges with $ — $B$2:$E$100 prevents range from shifting