Contact
12 programs
Explore the site

Excel #REF!: Separate Deleted References from Out-of-Range Requests

#REF! indicates an invalid reference. The repair depends on whether a referenced structure disappeared, a function requested a position outside its range, or a supported external-link workflow is unavailable. Microsoft’s REF reference.

Preserve the formula before guessing

Copy the formula text and record the expected source. If it already contains #REF!, changing that token to an arbitrary nearby address can produce a plausible but wrong number. Determine the lost column’s meaning from a trusted copy, source specification or the workbook owner.

If a deletion just happened, consider Undo before making additional edits, checking that it reverses the intended action. A restored error-free formula is not enough: confirm that the returned data still represents the correct field.

Check the size of a lookup range

In a disposable sheet put North in A2, 15 in B2, South in A3 and 22 in B3. Enter this outside A2:B3:

=VLOOKUP("South",A2:B3,3,FALSE)

The range has only two columns, so requesting column 3 is invalid and should return #REF!. If the intended field is the amount in column B, repair the request:

=VLOOKUP("South",A2:B3,2,FALSE)

Expected result: 22. Column numbers are relative to the selected lookup range. Do not enlarge the range simply to accommodate an incorrect number; that may select an unrelated field.

The same dimensional question matters for INDEX: compare its requested row and column with the dimensions of its supplied range. This is a different repair from recovering a deleted worksheet column.

Range repair is not data recovery

Microsoft describes using a continuous SUM range to avoid certain deleted-column reference errors. That does not preserve the deleted values. If a report needed three periods of data, a two-period sum that no longer errors can still be incomplete. Reconcile the expected periods and source totals after any structural repair.

For external-workbook cases, Microsoft’s desktop guidance notes that INDIRECT cannot obtain its reference from a closed source workbook. Do not turn off security controls or convert formulas to values simply to hide a link error. Confirm the original source and supported workflow first.

Checked October 21, 2026. This reference follows Microsoft’s listed desktop editions, including Microsoft 365, 2024 and 2021; its cited REF page does not establish free-web parity for external-workbook behavior. The small local lookup is a documentation-derived example, independently checked as a two-column table, not native-tested. No paid application or screenshot was used. Formula examples use English names and comma separators; adapt your locale’s separators.

See VLOOKUP, INDEX, and relative and absolute references.