How to Get Rid of a #REF! Error in Excel

To get rid of a #REF! error in Excel, edit the affected formula and replace its invalid cell or range reference with the correct one. Click the error cell, inspect the formula bar for #REF!, and restore the intended reference; if you just deleted the referenced cells, press Ctrl+Z immediately if possible. Avoid simply hiding the error until you know what the formula was meant to calculate: suppressing it can conceal a wrong result.

What You’ll Need

The workbook containing the error, preferably with a recent saved copy or backup.
Some idea of what the formula should calculate, such as which cells or worksheet it should use. If you do not know, check nearby formulas, labels, earlier versions, or ask the workbook’s owner.
Microsoft Excel for Microsoft 365 on Windows for the menu paths below. Mac users can use the equivalent ribbon commands; keyboard shortcuts are noted where they differ.

Step-by-Step

Save a copy before editing. Choose File > Save As and create a separate copy, especially if the workbook is shared or contains important calculations. This gives you a way to compare formulas or recover the original if a repair changes results. If the error appeared immediately after an accidental deletion and you have not made other changes, use Ctrl+Z on Windows (Command+Z on Mac) first. Undo may restore the deleted cells and their references. If it works, check the affected totals and save the repaired workbook.
Find the cells showing the error. For a small sheet, scan for cells displaying #REF!. To search more broadly, press Ctrl+F (Command+F on Mac), open Options, set Look in to Formulas, and search for #REF!. Set Within to Workbook if you want to search all sheets, then choose Find All. The search can find broken-reference text inside formulas even when the formula is not easy to spot on the sheet. Close the Find window when you have a list of affected cells.
Inspect the formula, not just the displayed result. Select an error cell and read the formula bar. A formula that contains #REF! has lost a cell, range, or sheet reference. For instance, a total may once have included a range on another sheet that was deleted. Click into the formula bar or press F2 to examine the formula; do not press Enter until you are ready to commit an edit. Note the nearby headings, the formula’s purpose, and whether similar rows or columns have working formulas.
Determine what the missing reference should be. Compare the formula with working formulas above or below it, and check labels, named ranges, tables, and other sheets. A formula in a monthly report might refer to the corresponding month on a source sheet; a copied formula might follow a consistent row pattern. If a worksheet tab was renamed, look for the current name. If a referenced column or row was deleted, identify the replacement data before changing the formula. Do not guess when the source is unclear: confirm the intended range with the workbook’s author.
Replace the broken reference with the correct one. Select the formula cell, click the formula bar, and replace only the damaged part if you are confident about the intended reference. You can also rebuild the formula from its beginning, using the correct sheet and cell addresses. With values in B2:B6, for example, a total of those five cells can use =SUM(B2:B6); if the cells contain 12, 8, 5, 10, and 15, the result is 50. Press Enter to apply the edit, then verify that Excel returns a plausible result rather than a different error.
Check formulas copied across or down. When a formula is copied, relative references shift to match the new location. If a formula was dragged too far or pasted into a different layout, its reference may no longer point to the intended cells. Compare the affected formula with a neighboring working one and confirm the row and column addresses. References with dollar signs, such as $B$2, are fixed when copied; B2 can move. Correct the formula in the affected cell, then copy it only across the intended range if the pattern is consistent.
Check for deleted worksheet or table items. If a sheet tab was deleted, restore that sheet from Undo, a backup, or version history when possible. Recreating a sheet with the same name does not necessarily restore its original cells or formulas, so confirm the content and addresses. If a table column was removed, inspect formulas that used that column and update them to a valid column. If a workbook link or named range is involved, check Formulas > Name Manager and the source workbook before replacing a reference; the correct repair depends on how the file is set up.
Verify the repair throughout the workbook. Repeat the search for #REF! with Look in set to Formulas and Within set to Workbook. Also check totals, charts, and dependent calculations that use the repaired cells. A cell can stop showing #REF! while still returning a mathematically wrong result, so compare it with a known total or calculate a small sample manually. Save the copy only after checking all affected formulas and confirming that other sheets still behave as expected.

Common Mistakes

Replacing every error with zero. A zero may look tidy but can change totals, averages, or decisions. First repair the source reference. Use a fallback value only when zero is genuinely the correct meaning of missing or unavailable data.
Rebuilding a formula from a guess. Two adjacent formulas can have different ranges by design, such as totals for separate departments. Use labels and the workbook’s logic to confirm the intended cells rather than copying a neighbor blindly.
Renaming a worksheet without checking formulas. Renaming a sheet usually updates direct references to it, but references already broken by deleting a sheet will not automatically recover just because you create a new sheet with the old name.
Using Find and Replace across the whole workbook without reviewing matches. Replacing text that looks like an address can alter valid formulas or labels. Search first, inspect each relevant result, and repair only the formulas that should change.
Assuming IFERROR fixes the cause. IFERROR can hide an error result but cannot restore the missing cells or make a broken reference valid. It may also mask errors that should be investigated.

Tips

Use Undo promptly after an accidental deletion. Ctrl+Z on Windows or Command+Z on Mac is often the quickest recovery, but it reverses the most recent action. If you have already made other changes, review what Undo would remove before using it.
Use version history or backups for deleted content. For files saved to OneDrive or SharePoint, check File > Info for version history options. Availability and wording can vary by account and organization. You can also compare with a dated backup rather than trying to reconstruct an entire sheet from memory.
Make formulas easier to audit. Use clear sheet names, consistent layouts, and named ranges where they genuinely help. Avoid deleting rows, columns, or table fields that other formulas depend on until you have checked those dependencies.
Consider IFERROR only for intentional error handling. With a numerator in A2 and a denominator in B2, =IFERROR(A2/B2,"") displays a blank if the division returns an error; if A2 is 20 and B2 is 4, it returns 5. This is useful only if a blank is an acceptable result. It does not repair a missing reference inside a formula, and it can hide problems, so apply it deliberately.
Keep the formula’s purpose visible. Add a clear label beside important calculations or document unusual references. That context makes it easier to distinguish a genuine broken reference from a formula that intentionally uses a different range.

Troubleshooting

The Find search returns no results, but I can see #REF!. In the Find window, choose Options and confirm Look in is set to Formulas. If it is set to Values, Excel may search displayed results rather than the formula text. Set Within to Workbook if the error might be on another sheet, and check spelling and punctuation in the search text. You can also select a visible error cell and inspect its formula bar directly.

I cannot tell which cells the formula should use. Look at the heading for the affected column, the labels beside the formula, and formulas for comparable periods or categories. Use Formulas > Trace Precedents or Formulas > Trace Dependents to inspect relationships where available, but remember that a broken reference cannot point to a live cell. If the intended source is still uncertain, consult the workbook’s owner or an earlier version rather than substituting a plausible-looking range.

The error returns after I repair one cell. There may be several broken formulas, or another formula may feed into the one you repaired. Search the entire workbook again, check formulas in dependent cells, and review any copied series for inconsistent references. A repair to one formula does not automatically correct other formulas that independently reference deleted cells.

The original sheet or data is gone. Check Undo first if the deletion was recent. Otherwise, look for a workbook backup or version history and restore the missing sheet or source data from a copy. If you cannot recover it, create a valid replacement only after confirming its structure and data with the workbook’s owner. Changing a formula to a different range without that confirmation could produce a convincing but incorrect answer.

The cell no longer shows #REF!, but the result seems wrong. Check that the repaired range includes all and only the intended cells, and compare the result against a small manual calculation. Also look for other errors in source cells and confirm that the formula was copied with the correct relative or absolute references. If the result feeds a report or chart, check those outputs too.

Frequently Asked Questions

What does #REF! mean in Excel?
It means a formula contains an invalid cell reference. This commonly happens after referenced cells, a row or column, or a worksheet are deleted, or when a formula is copied into an unsuitable layout.

Can I fix #REF! without knowing the original formula?
Sometimes. Nearby formulas, headings, named ranges, and earlier workbook versions can reveal the intended reference. If there is no reliable clue, ask the workbook’s owner or recover a backup rather than guessing.

Will renaming a worksheet fix a #REF! error?
Usually not if the sheet was deleted and the formula’s reference is already broken. Renaming an existing sheet generally updates valid references to it, but it does not recreate deleted cells or restore a lost reference.

Can I hide #REF! instead of repairing it?
You can use error-handling functions such as IFERROR, but that hides the displayed error rather than restoring the intended calculation. Do this only when the fallback result, such as a blank, is appropriate and the underlying issue has been considered.

How do I find every #REF! error in a workbook?
Press Ctrl+F, choose Options, set Look in to Formulas and Within to Workbook, and search for #REF!. Review the results individually, since a workbook can contain several unrelated broken references that need different repairs.

Does this work in Excel for Mac or the web?
The formula-repair approach is the same. On Mac, use Command+F to find and Command+Z to undo; ribbon labels can differ slightly by release. Excel for the web has similar formula editing, but some workbook recovery and search options may be easier to access in the desktop app.

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.