Excel #REF! Error: How to Find and Repair the Formula

To find a #REF! error in Excel, press Ctrl+F, search for #REF!, and choose Find All; then inspect each result and repair the invalid cell, sheet, or range reference. If search misses an error, use Home > Find & Select > Go To Special > Formulas > Errors to select formula cells that return errors.

A #REF! error means a formula contains a reference Excel can no longer resolve. It often appears after someone deletes a referenced row, column, cell, or worksheet. Finding every instance is useful before sharing a workbook or changing its structure, because one broken reference can affect totals and other dependent calculations.

What You’ll Need

The workbook open in Excel for Microsoft 365 on Windows, ideally with editing enabled.
A saved backup or a copy of the workbook before you make repairs, especially if it contains important calculations.
Some knowledge of what each affected formula is meant to calculate. If you do not know, check a nearby formula, an earlier workbook version, or the person who created the sheet.

Step-by-Step

Save a copy before investigating. Use File > Save As to create a separate copy, or make sure the current version is saved before editing. This gives you a way to compare the original and repaired workbook. Avoid immediately replacing every occurrence of the error text: different formulas may need different repairs, and a blanket replacement can damage valid calculations.
Search the whole workbook for the error. Press Ctrl+F to open Find, type #REF!, and open Options if the search settings are not visible. Set Within to Workbook rather than Sheet, then choose Look in: Values and select Find All. The results list identifies cells whose calculated value is #REF!, including cells where the formula refers indirectly to another cell that is already broken. Select a result to jump to it. On a Mac, use Command+F; the Find options are similar, though their layout can vary slightly by Excel version.
Search formula text as well as displayed values. In the Find dialog, change Look in to Formulas and search for #REF! again. This can find formulas that contain an explicit broken-reference token. The two searches serve different purposes: a formula such as =A1 can display #REF! because A1 is broken, even though its own formula text does not contain that text. Conversely, a formula may include a broken reference token whose displayed result is another error. Review both result lists, and do not assume the same cells will appear in each.
Use Go To Special to select formula errors on each sheet. On the ribbon, choose Home > Find & Select > Go To Special, select Formulas, select only Errors under the formula options, and click OK. Excel selects cells on the active worksheet whose formulas return errors. Look for #REF! among the selected cells; this selection can also include #VALUE!, #N/A, and other error types, so check each cell rather than treating every selected cell as a broken reference. Repeat on each worksheet: Go To Special works on the active sheet, not the entire workbook. You can also press Ctrl+G and choose Special. On Mac, use the equivalent Go To Special command if available in your Excel version.
Inspect the formula and its surroundings. Select a result and read the formula bar. Look for #REF! inside the formula, a reference to a missing sheet, or a link that no longer points to the intended range. Compare the formula with similar rows or columns that still calculate correctly. Check headings and labels to understand which data the formula should use. A visible error may also be passed along from an earlier cell, so follow the references rather than editing only the last cell where the error appears.
Trace the source of the broken reference. With the affected cell selected, use Formulas > Trace Precedents to show cells that feed into it, and Formulas > Trace Dependents to see which cells rely on it. Arrows help reveal whether the error begins in this formula or in an upstream cell. To step through the calculation, choose Formulas > Evaluate Formula. These tools are especially helpful in long calculations, but they may not fully explain a missing external workbook or a reference that was deleted.
Repair the reference using the intended data. If the correct source is clear, edit the formula bar or press F2, select the invalid part, and enter the correct cell, range, or sheet reference. For example, assume C2 should total the amounts in B2:B10; if deleting those source cells damaged the formula, a broken formula might appear as =SUM(#REF!), and the intended formula would be =SUM(B2:B10). Check that the example range actually contains the intended values in your workbook before using it. If the missing cells or sheet cannot be restored confidently, use an earlier saved copy or ask the workbook owner instead of guessing.
Check the repair and scan again. Press Enter after editing, confirm the cell displays the expected result, and compare it with a manual check or a similar calculation. Repeat the workbook searches for #REF! using both Values and Formulas, and revisit Go To Special on each sheet. Finally, inspect important totals and formulas that depend on the repaired cells. Save the workbook under an appropriate name once you are satisfied with the results.

Common Mistakes

Searching only the active sheet: A search set to Sheet can miss errors elsewhere. Set Within to Workbook, and still check each sheet with Go To Special.
Using only one search mode: Look in: Values finds displayed results, while Look in: Formulas searches formula text. A broken error passed from another cell may not appear in the formula-text search.
Replacing every #REF! at once: The correct replacement can differ from cell to cell. Find All is for locating and reviewing results; it does not establish what each formula should reference.
Assuming every selected error is #REF!: Go To Special can select several error types. Read the cell value and formula before changing anything.
Fixing the visible symptom instead of the source: If a total depends on another cell with #REF!, repairing the total alone may leave the underlying problem untouched. Trace precedents and work back to the first broken reference.
Guessing a range from a neighboring formula: Similar-looking formulas can have different row boundaries or special exceptions. Compare labels, intended coverage, and the workbook’s earlier versions before copying a reference.

Tips

Use Find All rather than repeatedly pressing Find Next when you need a list of locations. The results make it easier to revisit cells and compare patterns.
When formulas follow a consistent pattern down a column, inspect several neighboring rows before repairing one. This can show whether the reference should move with the row or stay fixed.
If the error appeared immediately after deleting or moving content, try Ctrl+Z before making further edits. Undo may restore the original reference if you have not performed other actions since the deletion. On Mac, use Command+Z.
Check whether a missing worksheet was deleted or whether the workbook was copied without all of its source sheets. A formula may need a restored sheet or an updated reference, not a newly invented range.
After resolving the error, consider documenting important source ranges or keeping a clean template. Clear labels and a saved reference copy make future repairs less dependent on guesswork.
If a workbook uses external links, ask whether the linked file was moved, renamed, or unavailable. Do not replace a link with an internal range unless that is what the calculation is supposed to use.

Troubleshooting

Find returns no results. Check that you searched the whole workbook, not just the active sheet. Try both Look in: Values and Look in: Formulas, and confirm the search text is exactly #REF!. If the error appears in a chart, name, or other workbook feature rather than a cell, a cell search may not locate it; inspect the relevant feature separately.

Go To Special selects nothing. It checks the active worksheet, so first activate a sheet that contains formulas and repeat the command. Make sure Formulas and Errors are selected in the Go To Special dialog. If the error is stored as text rather than returned by a formula, it will not be selected by the Formulas option; check the cell contents and search for the text instead.

The formula has no visible #REF! token, but the cell still shows the error. Follow the formula’s input cells with Trace Precedents. The source may be another formula that returns #REF!, or a reference may be invalid in a nested calculation. Repair the earliest broken input you can identify, then check whether downstream cells recalculate.

You cannot tell what the correct reference should be. Do not substitute the nearest-looking row or sheet. Check a prior version, the workbook’s documented logic, or a matching formula in a comparable section. If the deleted data is needed, restore it from a backup or version history before editing dependent formulas.

The error returns after you fix it. Another formula may still point to the damaged cell, or the workbook may be recalculating from a broken external link. Search again, inspect dependents, and confirm that the restored source is present. If Excel is in manual calculation mode, check Formulas > Calculation Options and choose Automatic if that is appropriate for the workbook.

Frequently Asked Questions

What does #REF! mean in Excel?

It means a formula contains a reference Excel cannot resolve, commonly because referenced cells, rows, columns, or a worksheet were deleted. It can also occur when a reference becomes invalid through an edit or when a linked source is unavailable.

Can I find every #REF! error in a workbook at once?

Use Ctrl+F, set Within to Workbook, and choose Find All. Search once with Look in: Values and again with Look in: Formulas. Go To Special can select formula errors too, but you need to repeat it on each worksheet and review the different error types.

Why does Find miss an error that I can see?

The search may be limited to the active sheet, or Look in may be set to the wrong option. Try both Values and Formulas and verify the Within setting. Also check whether the error is displayed in a chart or another feature rather than in a worksheet cell.

Can Excel repair a #REF! error automatically?

Excel can sometimes update references when cells are moved or sheets are renamed, but it cannot reliably know the intended source after a referenced range or sheet has been deleted. Restore the source or enter the correct reference after checking the workbook’s logic.

Should I delete a cell that contains #REF!?

Usually not. Deleting the cell can break formulas that depend on it and spread the error. First find out what the formula is meant to calculate, repair its reference, and then check its dependent cells.

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.