Excel #REF! error: fix and prevent it

The #REF! error is one of the most frustrating error messages in Excel, because it often only appears after you have deleted or moved something. Unlike a typo, it indicates a reference that literally no longer exists. Below you will read where it comes from, how to fix it and how to prevent it from happening again.

The #REF! error means a formula points to a cell that no longer exists

The #REF! error (short for reference) appears whenever a formula refers to a cell, row, column or sheet that is no longer valid. Excel cannot replace the reference with another cell and therefore places the text #REF! where a cell address used to be.

You can see this in the formula bar. Where it previously said, for example, =B2*C2, after deleting column C you suddenly see =B2*#REF!. The formula still exists, but it lacks a valid address to calculate with.

The error propagates. If a second formula refers to the cell containing #REF!, it inherits the error. In a large model, a single deleted column can turn dozens of cells red. That is why it pays to trace the source of the error rather than repairing each cell individually.

Don't confuse #REF! with other messages. #NAME? means Excel does not recognise a function name or a named range, #VALUE! indicates an incorrect data type, and #N/A signals that a lookup function found nothing. #REF! specifically stands for a reference that has lost its target. This distinction helps you choose the right solution: with #REF! you restore the reference, not the function name or the data type.

Four situations cause almost every #REF!

The error always arises because the target of a reference disappears or falls out of scope. This table lists the four most common causes.

Cutting (Ctrl+X) and then pasting is especially treacherous. If you cut a cell and paste it over a cell that a formula referenced, the original target disappears and a #REF! remains. Copying (Ctrl+C) does not have this effect, because the source remains.

#REF! appears fastest in lookup and reference functions

Certain functions are extra susceptible to this error because they work with fixed positions.

• VLOOKUP. The error occurs when the column index number is larger than the number of columns in the table array. If you delete a column within that array, the count shifts and the index falls outside the table. If you prefer a method that references the column itself, read the difference in VLOOKUP vs XLOOKUP.
• INDEX. If you ask INDEX for row 12 in a range of 10 rows, the function returns #REF! because that row does not exist.
• INDIRECT. This function builds a reference from text. If the text is incorrect, for example due to a deleted sheet, you immediately get a #REF!.
• 3D references. If a formula refers to a series of sheets (=SUM(Jan:Dec!B2)) and you delete one of those sheets, the sequence can break.

The pattern is always the same: the function calculates with a position that has been removed by a change.

Immediately after a deletion, fix the error with Ctrl+Z

If the #REF! appears because you have just deleted something, the quickest solution is to undo.

1. Press Ctrl+Z immediately, or click Undo on the Quick Access Toolbar. The deleted row or column returns and the reference restores itself.
2. If undo is no longer possible, click the error cell and examine the formula in the formula bar.
3. Select the #REF! part in the formula and type the correct cell address or range over it.
4. Press Enter and check that the result is correct.

If you are repairing a lookup function, adjust the column index number to the new position of the desired column, or replace the fixed index with one that looks up the column itself. If the error is in an INDEX formula, check whether the requested row or column number is still within the bounds of the range.

In a large workbook, track down every #REF! with Find and Replace

In an extensive file, the errors are sometimes scattered across multiple sheets. Find them all at once.

1. Press Ctrl+H to open Find and Replace.
2. In the Find what box, type #REF!.
3. Click Options and set Within to Workbook so that all sheets are searched.
4. Choose Find All. Excel displays a list of every cell containing the error; click a line to jump directly to it.

If you want to understand how a formula leads to the error, use Formulas > Evaluate Formula. Excel walks through the calculation step by step, so you can see exactly when the reference drops out. A second handy tool is Formulas > Trace Precedents, which uses arrows to show which cells feed a formula.

Repairing a broken VLOOKUP shows the approach

A common case: you had =VLOOKUP(A2, Price!A:E, 5, FALSE) to get the price from the fifth column. Someone deletes column C in the Price sheet, so the table now only has four columns. The formula still counts to 5, that column no longer exists, and the result becomes #REF!.

1. Click the error cell and check in the formula bar whether the index number (5) still matches the new table width.
2. Count the columns in the Price sheet again. The price is now in the fourth column.
3. Adjust the index number to 4: =VLOOKUP(A2, Price!A:D, 4, FALSE).
4. Copy the corrected formula down to the remaining rows.

If you want to avoid such corrections in the future, replace the fixed index with XLOOKUP or INDEX with MATCH, which refer to the column itself. The full difference is explained in VLOOKUP vs XLOOKUP.

Deleted names and validation lists also cause a #REF!

It's not just cell references that break. If you use a named range in a formula and you delete that name via Formulas > Name Manager, every formula that used the name turns into an error. Excel then usually shows #NAME?, but with a range reference within that name it can also become #REF!.

Data validation is susceptible too. If a drop-down list (Data > Data Validation) points to a range you later remove, the list stops working. For such errors, check whether the source still exists via Formulas > Name Manager, and restore the name or assign a new range. That way you keep not only your formulas intact, but also your input lists and pivot tables.

Tables and names prevent references from breaking

You can prevent the #REF! error by using references that move along when the structure changes.

• Work with Excel tables. Turn your range into a table with Ctrl+T. Structured references like Sales[Revenue] adjust automatically when you add or delete rows or columns.
• Use names for ranges. Give a range a name via Formulas > Define Name. When you refer by that name, the formula remains readable and shifts with it.
• Choose INDEX and MATCH or XLOOKUP instead of a fixed column index number. Such lookup methods refer to the column itself and do not break if you insert one.
• Delete columns consciously. If you want to remove a column, first use Trace Precedents to check whether formulas depend on it.
• Hide instead of delete. If a helper column is only visually in the way, hide it (right-click, Hide) instead of deleting it; hidden columns do not break references.

Pivot tables are equally sensitive to deleted source columns; how to build and refresh them is described in how to create a pivot table in Excel.

Neatly hide a remaining error with IFERROR

Sometimes a reference is temporarily empty or the error simply does not belong in your report. Catch it with IFERROR, so that an empty value or custom text appears instead of #REF!:

=IFERROR(B2*C2, "")

Use IFERROR deliberately. It hides not only #REF!, but also other errors such as #DIV/0! and #N/A. So resolve the underlying reference first and only wrap IFERROR around it when you are sure the error is harmless. Otherwise you mask a real problem in your calculation and only realise too late that figures are missing.

If you specifically want to catch only certain errors, use IFNA for solely #N/A, or check beforehand with ISERROR. That way you keep sight of the errors you do want to see. A good approach is to leave errors visible while building, and to add IFERROR only when the model is finished and ready to share. That prevents masking problems you still need to solve. More about common messages and how to avoid them can be found in Microsoft's explanation of the #REF! error.