Excel #REF! Error: How to Fix and Prevent Reference Errors

To correct a #REF! error in Excel, inspect the formula that shows the error, identify the missing cell, range, or sheet reference, and replace it with the correct reference. If the error appeared after a recent deletion, try Undo first. Before making broader changes, save a copy of the workbook so you can compare or recover the original.

A #REF! error means Excel cannot resolve a reference used by a formula. It commonly appears after a referenced cell, row, column, or worksheet is deleted, but it can also result from an invalid address in a function or a broken link. The right fix is to restore the intended reference—not simply to hide the error.

What You’ll Need

The workbook containing the error, opened in Excel for Microsoft 365 on Windows.
A general idea of what the formula should calculate and which cells or sheet it should use. If you do not know, check nearby formulas, labels, or an earlier copy of the workbook.
A backup or saved copy before repairing many formulas. If the workbook is stored on OneDrive or SharePoint, version history may also be available.

Step-by-Step

Save a copy and locate the error. Use File > Save As to make a working copy, or duplicate the file in its folder. Look for cells displaying #REF!, and note the worksheet name and nearby headings. If you are dealing with a large workbook, press Ctrl+f, search for #REF!, and inspect the search results. On Mac, use Command+f. Search can find the displayed error in a cell, but you should still inspect the formula itself before changing anything.
Check whether Undo can reverse the change. If the error appeared immediately after deleting a row, column, cell, or sheet, press Ctrl+z on Windows (Command+z on Mac) once and check the formula again. If the deletion was the cause, Undo may restore both the deleted content and its references. Do not keep pressing Undo if you have made other edits you need to preserve; use the copy or version history instead.
Read the broken formula. Select the error cell and inspect the formula bar above the worksheet. A reference that Excel has lost is usually shown as #REF! within the formula. Check the whole expression: the missing reference might be one argument in a longer formula, rather than the entire calculation. Note the function, sheet name, and other cell addresses so you can determine what the missing part was meant to point to.
Use formula-auditing tools to follow the dependency. Select the error cell, then choose Formulas > Formula Auditing > Trace Precedents to see cells feeding into it, where Excel can identify them. Choose Formulas > Formula Auditing > Error Checking to review the error and possible explanations. These tools can narrow down the source, but they cannot always reconstruct a reference that has been deleted. In Excel for Mac, look on the Formulas tab for the Formula Auditing commands. Excel for the web has a formula bar and supports editing formulas, but some auditing options may be limited; open the workbook in desktop Excel if you need them.
Work out the intended cell, range, or sheet. Use nearby labels, column headings, similar formulas, and the workbook’s layout as clues. Compare the formula with the same calculation in adjacent rows or columns. Check whether a worksheet was renamed or deleted, and whether the intended source is in another workbook. If you cannot establish the intended reference confidently, look for an earlier saved version rather than guessing.
Restore the missing source when possible. If the source sheet or data was deleted, recover it from Undo, a backup, or version history before editing dependent formulas. For a file saved to OneDrive or SharePoint, check File > Info > Version History, if available, and open an earlier version to confirm the original layout. Restoring a deleted column by inserting a new one in the same position does not necessarily repair formulas already changed to #REF!; verify the formula text after any restoration.
Replace the invalid reference in the formula. Select the affected cell, click in the formula bar, and replace the #REF! portion with the correct address. You can type the reference or, while editing the formula, click the intended cell or drag across the intended range. Include the worksheet name when the source is on another sheet, and preserve the formula’s other operators and arguments. Press Enter, then check that Excel returns a sensible value. If the formula uses a function with row or column number arguments, check that those numbers still point inside the supplied range.
Check the repaired formula and related cells. Compare the result with a manual check or a neighboring calculation. If similar formulas elsewhere also show #REF!, repair them based on their own intended references rather than assuming every formula should use the same address. Recalculate with F9 on Windows (Fn+F9 may be needed on some Mac keyboards), and save the working copy once the result is correct.
Find additional broken formulas carefully. Press Ctrl+f and search for #REF!; on Mac, press Command+f. Check whether the search is looking in formulas or values if the options are available, and review each result individually. A bulk Replace All can put the wrong reference into formulas that were intended to point to different cells. Use it only when you have confirmed that every match needs the exact same replacement.

For example, assume B2 contains 10, B3 contains 20, and B4 contains 30, and the total is meant to add those three cells. The repaired formula in the total cell is =SUM(B2:B4). It returns 60 because 10 + 20 + 30 = 60. If your data is in a different location, use the actual range rather than copying this example unchanged.

Common Mistakes

Replacing #REF! with zero or blank text. This can make the visible error disappear while leaving the calculation incomplete or misleading. Fix the reference if you need a valid result; use a deliberate error-handling approach only when suppressing an error is appropriate for the report.
Inserting a row or column and assuming Excel will restore the old address. Excel may update references when you insert cells, but inserting something after a reference has already become #REF! does not reliably reconstruct the formula. Inspect the formula bar and repair it directly.
Copying a formula from another row without checking relative references. When copied, ordinary references can shift to match the new location. Confirm that each formula points to the intended row, column, or fixed range.
Using Replace All before reviewing matches. The same error text can occur in formulas with different intended destinations. A single replacement can corrupt correct calculations, so inspect the matches and keep a backup.
Assuming every #REF! has the same cause. One error may come from a deleted sheet, while another may come from an invalid function argument. Diagnose each affected formula instead of applying one broad fix.

Tips

Compare parallel formulas. If a formula is part of a repeated table, inspect the cells directly above and below it. Their structure can reveal which reference was lost, but check that the table follows a consistent pattern first.
Look at the workbook’s history before reconstructing complex formulas. An earlier version can show the original sheet name, range, or formula. Make a separate copy before restoring an older file so you do not overwrite newer work.
Keep source data in place when practical. If other formulas depend on a sheet or range, avoid deleting it without checking dependencies. Renaming or reorganizing data can also affect references, especially in formulas that build addresses as text.
Use Find to audit the workbook after a repair. Search for #REF! again after editing. Then check important totals and outputs, since a formula can be free of #REF! and still point to the wrong cells.
Preserve evidence while investigating. Take a note of the original formula text or keep the untouched copy open. This makes it easier to reverse a mistaken edit and compare the intended calculation.

Troubleshooting

The formula bar shows #REF!, but I do not know what the original address was. Compare similar formulas, inspect labels near the missing data, and check an earlier workbook version. If these clues do not establish the intended range, ask the workbook’s creator or data owner rather than substituting a guess.

Undo is unavailable or does not fix it. Undo history can be cleared when the workbook is closed or when later actions replace the relevant history. Check File > Info > Version History for a cloud-stored file, or look for a backup or AutoRecover copy. Restore into a separate file if you need to retain current edits.

The error returns after I edit the formula. Check for another #REF! elsewhere in the expression, verify every function argument, and confirm that the referenced sheet still exists. If the formula uses INDIRECT to create a reference from text, check that the text forms a valid address. Also check whether a copied formula shifted a relative reference unexpectedly.

The formula is fixed, but the displayed result seems wrong. Confirm the repaired range includes the intended data and excludes headings or unrelated values. Check calculation settings under Formulas > Calculation Options; if calculation is set to Manual, choose Automatic or press F9 to recalculate. Recheck key totals against the source data.

Excel for the web does not show the auditing command I need. Use the formula bar to inspect and edit the formula, or open the file in the desktop version of Excel for Formula Auditing tools. Keep edits saved and verify the cell result after switching applications.

Frequently Asked Questions

What does #REF! mean in Excel?
It means a formula contains a reference Excel cannot resolve, often because the referenced cell, range, or worksheet was deleted. It can also result from an invalid address produced by a formula or function.

Can I fix #REF! without changing the formula?
Sometimes. If a recent deletion caused the error, Undo may restore the original reference. Otherwise, recover the missing source from a backup or version history, or edit the formula to point to the correct cells.

Why does #REF! appear after I delete a column?
A formula that used cells in that column may lose its reference when the column is removed. Undo the deletion if appropriate, restore the original data, or update each affected formula to use the correct remaining cells.

Can Find and Replace repair every #REF! error?
It can help only when all affected formulas need the same replacement. Because different formulas may have lost different references, review each match and avoid Replace All unless the replacements are genuinely identical.

Will IFERROR fix a #REF! error?
IFERROR can display a chosen alternative when a formula returns an error, but it does not restore the missing reference or make the calculation correct. Repair the underlying reference when you need an accurate result.

Does #REF! mean the workbook is damaged?
Usually not. It generally indicates a broken formula reference, not a damaged file. Save a copy, inspect the affected formulas, and check backups or version history if the intended reference is unclear.

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.