Why Am I Getting a #REF! Error in Excel?

You are getting a #REF! error because an Excel formula contains a reference that is no longer valid, or because the formula asks for a cell outside the range it can use. The usual cause is deleting a row, column, or worksheet that a formula refers to; checking the formula and restoring or replacing the missing reference is the way to fix it.

What #REF! means in Excel

#REF! is Excel’s reference error. A formula uses cell or range addresses—such as A2 or B2:B10—to identify its inputs. If Excel can no longer resolve an address, it displays the error instead of calculating a result. The error may appear in the formula itself or be passed along from another cell.

For example, a total might depend on a price in one column and a quantity in another. If a referenced column is removed, the total can lose the address it needs. Other formulas that use that total may then show #REF! too, so one broken reference can create a chain of visible errors.

This is different from errors such as #VALUE!, which commonly indicates an unsuitable value or argument, and #NAME?, which can indicate text Excel does not recognize in a formula. The exact error is useful evidence: #REF! points you toward an invalid reference, not simply an incorrect answer.

Common causes: deleted cells, rows, columns, or sheets

Deleting something a formula points to is the most common reason for the error. Excel normally adjusts references when you insert or move cells, but it cannot keep a reference to an item that has been deleted. A formula can also become invalid if you remove a worksheet used by a formula on another sheet.

Suppose A2 contains a price and B2 contains a quantity. With those values in place, a formula in C2 can multiply them:

Cell layout: A2 contains the price, B2 contains the quantity, and C2 contains the formula. =A2*B2

If you delete column B, Excel may change the formula to =A2*#REF!, because the quantity reference no longer points to a cell. The formula can still be visible and editable, but its missing input prevents calculation. If you delete a worksheet used in a formula such as a cross-sheet total, the affected formula may similarly contain #REF!.

Be careful to distinguish deleting cells from clearing their contents. Pressing Delete or choosing Clear Contents leaves the cells in place, so formulas that refer to those cells generally remain valid and may calculate using blanks or zero, depending on the formula. Choosing Delete for a row, column, cell, or sheet changes the workbook structure and can break references.

How to find the broken reference

Start with the cell displaying #REF!. Select it and inspect the formula bar. Look for the literal text #REF! inside the formula, or for a function whose arguments contain a range that may be too small. If the formula bar shows a normal-looking formula, check whether one of the cells it depends on contains the error.

For a large workbook, use Home > Find & Select > Find, search for #REF!, and use the search options to look in formulas if needed. Finding the error text can locate formulas that contain a broken reference, but it may not find every downstream cell that only displays an error passed from elsewhere. Search can also find matching text in ordinary cell values, so inspect each result rather than assuming it is a formula problem.

Excel’s auditing tools can help reveal the dependency path. Select the error cell and choose Formulas > Formula Auditing > Trace Precedents to show cells feeding the formula. Trace Dependents shows formulas that use the selected cell. These arrows are useful for following a chain of calculations; remove them with Formulas > Formula Auditing > Remove Arrows when finished.

Then check the workbook’s recent changes. Ask whether a row, column, or sheet was deleted, renamed, or replaced; whether a formula was pasted over another formula; and whether the error began after a file was imported or edited. If the error appeared immediately after an accidental deletion, undoing it may restore the reference. Use Ctrl+Z on Windows or Command+Z on Mac, provided you have not made later changes you need to keep.

Repair a formula after a deletion

If Undo is no longer available, the repair is to identify what the formula was meant to refer to and enter a valid reference. Select the affected cell, click in the formula bar, replace the #REF! portion with the correct cell or range, and press Enter. If you know which value belongs in the calculation, you can also rebuild the formula from scratch. Do not guess at a replacement address: a formula can become error-free while still calculating the wrong data.

For example, suppose a formula should add the values in B2 through B6, but a deleted cell has left a broken reference. Confirm the intended data range first, then rebuild the total using the correct range:

Cell layout: B2:B6 contains the five values to add, and B7 is the total cell. =SUM(B2:B6)

If the deleted cell was the only copy of an input, repairing the formula alone will not restore the missing data. Check a backup, an earlier workbook version, or another source for the original value before using the formula again. In Microsoft 365, a workbook stored in OneDrive or SharePoint may have version history available through File > Info; availability depends on where and how the file is stored. Save a separate copy before making extensive repairs.

Function errors that can produce #REF!

A formula does not have to contain an obviously deleted cell address to return #REF!. Some functions produce it when a requested position falls outside the supplied range. For instance, INDEX returns a value from a specified position in a range. If the requested row number is greater than the number of rows in that range, there is no valid cell to return.

Cell layout: A2:A5 contains four values, and C2 contains the formula. =INDEX(A2:A5,6)

This returns #REF! because the range has four rows, but the formula requests row six. Check the range and the row or column number being calculated. If those numbers come from another cell or formula, inspect that input as well; a calculation that unexpectedly returns 6 can make an otherwise reasonable INDEX formula fail.

INDIRECT can also return a reference error because it interprets a text string as a cell address. If the text names a sheet that has been deleted or renamed, the string no longer identifies a valid location. Unlike an ordinary direct reference, the text inside INDIRECT is not automatically updated when a referenced sheet is renamed.

Cell layout: a worksheet named Sales has a value in A1, and B1 contains the formula. =INDIRECT("Sales!A1")

The formula returns the value in Sales!A1 while that sheet and address exist. If the sheet is renamed or deleted, the text still says Sales!A1 and the formula can return #REF!. Update the text to the correct sheet and cell, or use a direct reference when the address is known and does not need to be assembled from text. INDIRECT also cannot retrieve a reference from a closed external workbook, which is a separate limitation to consider when the reference text points outside the current file.

References that look broken but need a different fix

Some formula problems resemble a reference error but are not the same issue. A formula such as =A2*B2 may return zero or an unexpected value if one of those cells is blank; that does not by itself mean the address is invalid. Similarly, a cell displaying #SPILL! has a dynamic-array output area blocked, while #REF! means a reference is invalid. Fix the specific error Excel displays rather than changing unrelated cells.

External workbook links can be another source of confusion. A normal link to a cell in another workbook may display a prompt or use a previously saved value when the source file is unavailable; that is not automatically a #REF! error. If a formula explicitly contains #REF!, inspect whether a source sheet, row, column, or address was removed. If it uses INDIRECT with an external workbook, remember that the source workbook must be open for that function to resolve the reference.

Structured references in Excel tables can also change as table columns are edited. If a table column used by a formula is removed, inspect the formula and the table’s current column names rather than assuming the original column still exists. When a workbook has many linked formulas, correct the earliest broken cell first; downstream errors may disappear automatically once the source formula is repaired.

Preventing future #REF! errors

Excel cannot protect every formula from a deliberate deletion, but a few habits reduce the chance of breaking a workbook or make recovery easier:

Before deleting a row, column, or worksheet, check whether formulas elsewhere refer to it. Formula Auditing arrows can help with important cells.
Keep a backup or use a versioned storage location before making structural changes to a complex workbook.
Use clear sheet names and avoid renaming sheets without checking formulas that build addresses as text, especially INDIRECT formulas.
After inserting or removing data, check key totals and look for errors with Find. Do not treat a clean-looking result as proof that the intended inputs are still included.
When copying formulas, check relative and absolute references. A reference such as A1 moves when copied to another location, while $A$1 stays fixed; an unintended shift can cause a different problem even if it does not display #REF!.

For a formula intended to cover a changing list, a structured table reference can be easier to maintain than a fixed range, provided the formula is written against the correct table and column. This is not a guarantee against errors: deleting a required table column can still break a formula. The useful principle is to make the intended inputs clear and verify formulas after changing workbook structure.

Frequently Asked Questions

Can I fix #REF! without knowing what was deleted?
Not reliably. The error tells you that a reference is invalid, but not what the formula was intended to calculate. Check the formula, workbook history, nearby labels, and related formulas, or restore an earlier version. Guessing a replacement may hide the error while producing an incorrect result.

Will changing #REF! to zero fix the calculation?
It may remove the displayed error if zero is genuinely the correct value, but it does not restore the missing reference. Replacing a missing price, quantity, or range with zero can make totals misleading. Identify the missing input and use zero only when it represents the intended data.

Why does #REF! appear in several cells at once?
Those cells may depend directly or indirectly on one broken formula or deleted range. Find the earliest formula with the invalid reference and repair it first. Dependent formulas may recalculate correctly afterward; if they do not, inspect their own formulas separately.

Does renaming a worksheet cause #REF!?
Excel normally updates ordinary direct references when a worksheet is renamed. A formula that stores the sheet address as text, such as an INDIRECT formula, may not update, because Excel treats that address as text rather than a direct reference. Check which kind of reference the formula uses.

Why does INDEX return #REF! when the range looks correct?
The row or column position requested by INDEX may be outside the range, even if the range itself is valid. Count the rows or columns available and compare that count with the position supplied to the function. Also check whether the position is calculated from another cell and is unexpectedly too large.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Errors & fixes

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.