How to Fix a #REF! Error in Excel Without Losing Data

To remove a #REF! error in Excel, first make a copy of the workbook, then find the formula showing the error and restore its intended cell, range, or worksheet reference. The error means a formula points to a reference Excel can no longer resolve; hiding the message does not repair the calculation. If you know what the formula should refer to, editing that reference is usually the fix. If a cell or sheet was deleted, undoing the deletion or restoring an earlier file version may be necessary.

What You’ll Need

The workbook containing the error, opened in Excel for Microsoft 365 on Windows.
A backup copy, or access to the workbook’s version history or another saved copy. This matters most if a deletion or replacement caused the error.
Enough context to identify the intended source: nearby formulas, worksheet names, previous versions, or the person who created the workbook.

You do not need an add-in or a special repair tool. The steps below use Excel’s built-in search and formula tools. On a Mac, use Command where Windows steps say Ctrl; for example, Command+F searches and Command+Z undoes. Some Go To Special and search options are arranged differently in Excel for the web, so open the file in the desktop app if you cannot find a described command.

Step-by-Step

Protect the workbook before editing. If the file is important, choose File > Save As and save a separate working copy. If the workbook is stored in OneDrive or SharePoint, check File > Info for version history, or use the file’s version history in its storage location. Do not save over the only copy while investigating. This is especially important if the broken reference followed a deletion, because fixing the formula manually may not restore the deleted data or the original formula structure.
Find every visible #REF! result. Press Ctrl+F, select Options in the Find dialog if the extra settings are hidden, set Within to Workbook, and set Look in to Formulas. Search for #REF!, then choose Find All to list the matching cells. In some Excel builds, the search options are presented in a slightly different layout. Check the formula bar for each result and record the cell address and worksheet name. If you only want to inspect errors rather than search formula text, use Home > Find & Select > Go To Special, choose Formulas, select Errors, and select OK; this selects formula cells returning errors, including errors other than #REF!.
Inspect the complete formula and identify what broke. Select a result and read the formula bar from beginning to end. A reference may be broken in the main calculation or nested inside another function. Look for the literal #REF! in place of a cell or range, or in place of a worksheet reference. Compare the formula with similar formulas above or below, and check the workbook’s other sheets for the data it should use. A neighboring formula can reveal the intended pattern, but do not copy it blindly: the row, column, and sheet may differ.
Undo immediately if the deletion just happened. If you have not made other changes since deleting a row, column, cell, or worksheet, press Ctrl+Z until the deletion is undone and check whether the formula returns to normal. On a Mac, press Command+Z. Undo is most useful before closing the workbook or making a series of unrelated edits. If undo would reverse important work, stop and use the saved copy or version history instead. Excel cannot recreate deleted source data simply by changing the formula that now displays the error.
Restore the correct reference when you know it. If the source data still exists, select the broken formula cell, click in the formula bar, and replace the missing reference with the correct cell, range, or sheet reference. Press Enter and check the result. For a reference to another sheet, confirm the sheet name exactly matches the tab, including spaces and punctuation. If the formula should include a range, verify both endpoints and make sure the range covers the intended data. If you are unsure of the intended source, do not guess: compare older versions, related formulas, or the workbook’s documentation first.
Check how the formula was copied or filled. A formula copied to another location can shift a relative reference, and filling a formula too far can produce an invalid reference. Compare the broken formula with one in a working row and check whether the reference should move with the formula or stay fixed. If the source should remain fixed when copied, edit the formula to use an absolute reference by adding dollar signs to the relevant column and row, or rebuild the formula from the correct source cell. Use the F4 key while editing a reference in Windows to cycle through reference styles; on a Mac, the shortcut can depend on keyboard settings, so editing the dollar signs directly is an alternative.
Recheck formulas that build references from text or arguments. If the formula uses INDIRECT, check that the text it is turning into a reference spells the worksheet and cell address correctly. If it refers to another workbook, confirm the source workbook is available; INDIRECT cannot resolve references to a closed external workbook. For functions such as INDEX, check that the requested row or column position is within the supplied range. When a formula uses a workbook link, inspect Data > Workbook Links if that command is available in your Excel version, and confirm the source file and sheet still exist. Correct the underlying name, range, or source rather than suppressing the error.
Confirm the repair and check dependent results. Press Enter after editing, verify that the repaired cell now returns the expected value, and review formulas that use its result. Repeat the search for #REF! across the workbook. Save the working copy only after checking key totals, charts, and dependent calculations. If you repaired a formula by comparing it with a neighboring one, validate it against the source data rather than assuming that a blank or zero result means the repair is correct.

Common Mistakes

Replacing every error with zero or blank: a workbook-wide replacement can conceal missing references and change totals, charts, or decisions. A #REF! may mean required input is gone, not that the correct result is zero. Repair each affected formula based on its intended source.

Deleting and retyping a whole formula without checking it: this can remove valid conditions, ranges, or references that were not broken. Edit only the missing portion when you understand the formula; otherwise compare it with a trusted earlier version.

Assuming the displayed error is the only problem: one deleted sheet or column can break many formulas, and some dependent formulas may show a different error or an incorrect value. Search the entire workbook, then inspect important outputs that depend on the repaired cells.

Using IFERROR to hide the message: IFERROR can be appropriate when a formula is expected to produce an error in a defined situation, but it does not restore a missing reference. Wrapping a broken calculation can make the workbook look clean while leaving its results incomplete.

Confusing #REF! with other Excel errors: #VALUE!, #NAME?, and #N/A have different causes. Confirm the exact text in the cell before changing a formula; a fix for a missing reference will not necessarily fix another error type.

Tips

Use Find All with Within set to Workbook to check every worksheet, not just the active sheet. Keep Look in set to Formulas so Excel searches the formula text, not only displayed results.
Before removing a row or column, search for formulas that refer to it and check whether other sheets, charts, or summaries rely on it. Deleting an unused-looking row can affect a calculation elsewhere.
When designing a workbook that will grow, use Excel Tables and structured references where appropriate. Table references can be easier to maintain as rows are added, though they still need checking if the source table or columns are removed.
Keep a short note beside complex formulas explaining their source data. This makes it easier to identify the right replacement reference if the layout changes later.
For repeated formula patterns, compare several working cells rather than only the nearest neighbor. The first or last row may intentionally use a different range.
If someone else owns the workbook’s data structure, ask before changing a formula that feeds reports or shared totals. A syntactically valid replacement can still point to the wrong data.

Troubleshooting

Find does not locate the error: confirm that the search is set to Formulas and Within: Workbook, and that Match entire cell contents is not enabled. You can also select Home > Find & Select > Go To Special > Formulas > Errors to select formula cells with errors. This selection includes all error types, so inspect each selected cell. If you are working in Excel for the web and do not see the same options, open the workbook in the desktop app.

The formula has #REF! but you cannot tell what it should reference: do not substitute the closest-looking cell. Check an earlier version of the workbook, a backup, another copy, or a corresponding formula in a similar section. If the workbook is shared, ask its owner or the person who maintains the source data. Without evidence of the intended reference, a guessed repair can produce a plausible but wrong answer.

The error returns after you fix it: check whether the formula is generated or overwritten by another formula, a refresh, a copied range, or a macro. Search the whole workbook again and inspect the source of the repeated change. If the formula uses text to build a reference, verify the text inputs as well as the formula itself; fixing only the displayed cell may not correct the underlying input.

The formula now gives a number, but the result seems wrong: verify the referenced sheet, range boundaries, and row or column alignment. Check whether the formula was copied from a row with different logic, and confirm the result against the original source data. A result without an error message is not proof that the reference is correct.

You cannot restore a deleted sheet or data: stop editing the original file and look for a version created before the deletion. For a cloud-stored workbook, review its available version history; for a local workbook, check backups or recovery copies. If no earlier copy exists, you may need to rebuild the missing data and then update each affected formula. Excel cannot infer deleted content from #REF! alone.

Frequently Asked Questions

What does #REF! mean in Excel?
It means a formula contains a reference Excel cannot resolve. This commonly follows a deleted cell, range, row, column, or worksheet, but an invalid generated reference or out-of-range function argument can also cause it.

Can I remove #REF! without changing the formula?
Not reliably if the reference is genuinely broken. You can hide an error with error-handling logic, but that does not restore the missing source or make the calculation correct. Fix the reference unless the error is an expected case that you have deliberately handled.

Can I fix all #REF! errors at once?
Only if every affected formula should use the same replacement reference, which is uncommon. Find All can identify the affected cells efficiently, but review each formula before editing. A bulk replacement can silently redirect formulas to the wrong data.

Will undo always bring back a deleted worksheet?
Undo can restore it if the deletion is still in the workbook’s undo history. It may no longer be available after closing the file or performing later actions, so check version history or a backup if Ctrl+Z or Command+Z cannot restore it.

Why does #REF! appear after I copy a formula?
A relative reference may shift as the formula moves, or the formula may have been filled beyond a valid range. Compare it with a working formula and decide whether the reference should move or remain fixed before editing.

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.