How to find #REF errors in Excel on every sheet
Two Find passes that catch what the other misses, the four places Find never searches, and how to fix each kind of broken reference.
- Auditing
- Errors
On this page
Here is how to find #REF errors in Excel on every sheet, and how to fix each kind. A #REF! error tells you a formula has lost the cell it pointed at. One is easy to see. The problem is scale. One deleted column can break dozens of formulas on sheets you are not looking at. An IFERROR can also hide the result completely. The steps below also cover the places that Excel’s own search never reads.
The steps suit anyone who reviews a model before it goes out: an analyst, a controller, an auditor. For the other error codes, see the guide to finding errors in Excel.
What a #REF! error means
Microsoft’s page on how to correct a #REF! error gives the definition: the formula refers to a cell that is not valid. The causes fall into two groups, and the difference decides how you search.
The reference is gone from the formula. Someone deleted a row, a column or a sheet that the formula used. Or someone pasted other cells over it. Excel rewrites the formula and puts #REF! where the reference was. Microsoft’s example is =SUM(B2,C2,D2), which becomes =SUM(B2,#REF!,C2) after column C is deleted.
The formula is intact but points outside its range. Here the text of the formula is unchanged. Only the result shows #REF!. Microsoft lists these cases:
=VLOOKUP(A8,A2:D5,5,FALSE)asks for column 5 of a range that has 4 columns.=INDEX(B2:E5,5,5)asks for a row and a column outside a 4 by 4 range.INDIRECTpoints at a workbook that is closed.- A structured reference to a table in a linked workbook. Microsoft notes that these are not supported.
How to find #REF errors in Excel on every sheet
The fastest tool is Find. Microsoft’s page on find and replace confirms the two settings you need. Within chooses Sheet or Workbook. Look in chooses Formulas, Values, Notes or Comments. You need two passes, because each one misses what the other finds.
Pass 1: search the formula text
- Press
Ctrl+Fand click Options. - Set Within to Workbook and Look in to Formulas.
- Type
#REF!and click Find All.
The list shows each match with its sheet, cell and formula. Click a row to go to the cell. This pass finds every formula that lost a reference, including the ones that do not show an error. A formula such as =IFERROR(SUM(B2,#REF!,C2),0) displays 0, so it looks fine on screen. The broken reference is still in its text, and this pass finds it.
Pass 2: search the displayed values
Repeat the search with Look in set to Values. This pass finds the cells that show #REF! but have no #REF! in their formula. These are the lookup and INDEX cases above, and INDIRECT to a closed file. It also finds the cells further down the chain, because an error flows into every formula that uses it. Fix the source cells first and many of these disappear.
On one sheet: Go To Special
When you work on a single sheet, Go To Special selects every formula that returns an error.
- Press
Ctrl+Gand click Special. On the ribbon, use Home → Find & Select → Go To, then Special. - Choose Formulas, then keep only Errors ticked.
- Click OK and give the selection a fill colour.
Microsoft’s page on Go To Special covers the dialog. Ben Ducker’s ICAEW article on Go To Special explains the tick boxes under Formulas. The selection includes every error type, not only #REF!.
Keep them visible: a highlight rule
ERROR.TYPE returns 4 for a #REF! error and #N/A for a cell without an error. Its function reference documents both values. That makes a rule that colours only #REF! cells:
- Select the block to check, with
A1as the active cell. - Go to Home → Conditional Formatting → New Rule.
- Choose Use a formula to determine which cells to format.
- Enter
=IFERROR(ERROR.TYPE(A1)=4,FALSE)and pick a fill colour.
The rule stays in the file, so a new break shows up the moment it happens.
Four places that Find never searches
The Look in box reads cells, notes and comments. A broken reference can also sit in four places outside the grid. A clean Find result says nothing about them.
Defined names. A name whose range was deleted refers to #REF!. Any formula that uses the name then fails. Open Formulas → Name Manager, click Filter and choose Names With Errors. Microsoft’s page on the Name Manager describes this filter: it shows names with values such as #REF, #VALUE or #NAME. Read the Refers To column for each one.
Conditional formatting rules. A rule can refer to a deleted range and stop working without a message. Open Home → Conditional Formatting → Manage Rules, show the rules for the whole sheet, and read each formula.
Data validation lists. A drop-down whose source was deleted stops offering the right values. Press Ctrl + G, click Special, choose Data validation and All. Then open Data → Data Validation on the selected cells to read the source.
Chart series. Click a series in each chart. The formula bar shows its SERIES formula. A #REF! there means the chart lost part of its data.
Trace a #REF! back to its source
On a long chain, the cell that shows the error is rarely the cell that caused it. Select the cell and use Formulas → Trace Precedents. Microsoft’s page on tracing formulas notes that red arrows show cells that cause errors. Blue arrows show cells with no errors. A black arrow to a worksheet icon means the source is on another sheet. Double-click it to open the Go To dialog. The trace precedents guide covers the arrows in more detail.
How to fix each kind
When a delete or a paste removed the reference, rebuild it.
- Just deleted: press
Ctrl+Zat once. Excel restores the cells and the references. - Lost reference: select the cell, select
#REF!in the formula bar, delete it, and enter the correct range. Microsoft’s page on avoiding broken formulas gives these steps. - Deleted sheet: the same page says a deleted worksheet cannot be recovered. If you have not saved yet, close the file without saving and reopen it. Otherwise rebuild the references from an older version.
When the formula is intact but points outside its range, fix the formula itself.
- Lookup or INDEX out of range: widen the range or lower the column or row number, for example
=VLOOKUP(A8,A2:E5,5,FALSE). - INDIRECT to a closed file: open the source workbook, or replace
INDIRECTwith a direct link. The guide to breaking external links covers the cleanup. - Broken name: in the Name Manager, edit Refers To to the correct range. Delete the name if nothing needs it.
Do not wrap the formula in IFERROR to make the error go away. That hides the break and passes a wrong number into the totals.
Stop new #REF! errors
Most breaks come from a delete. Three habits prevent most of them.
- Use ranges, not lists of cells. This is Microsoft’s own advice.
=SUM(B2:D2)adjusts when a column inside it is deleted.=SUM(B2,C2,D2)breaks. - Check dependents before you delete. Select the row, column or sheet and look for formulas that use it. Replace them with their results first if the data must go.
- Watch typed column numbers. A
VLOOKUPwith a typed column number breaks when someone deletes a column inside its range. The range gets narrower, but the number stays the same.
Add one line to your review routine too: both Find passes return zero results before the model goes out. Our Excel model review checklist puts that item in Stage 5, the detailed review.
Find them across the whole workbook at once
The manual method above takes two searches, four extra checks and a trace for each source. On a 30-sheet model that adds up.
In Formula Audit XL, the Error Checker (Ctrl + Shift + E) lists every error value across every sheet, #REF! included. The list has a filter box and click-to-navigate. It is one of the five audit checks. For defined names, the Named Ranges auditor (Ctrl + Shift + J) flags every name whose reference resolves to #REF!.
To run the Error Checker on your own models, download Formula Audit XL and start the 7-day free trial.
Frequently asked questions
What causes a #REF! error in Excel?
A #REF! error means a formula refers to a cell that is not valid. The usual cause is a deleted row, column or sheet that the formula pointed at. Cells that were pasted over cause it too. Some intact formulas also return #REF!. Examples are a VLOOKUP column number wider than its range, an INDEX row or column outside its range, and an INDIRECT to a closed workbook.
How do I find all #REF errors in a workbook?
Press Ctrl+F, click Options, and set Within to Workbook. Search for #REF! twice: once with Look in set to Formulas, and once with Look in set to Values. The first pass finds broken references in the formula text, even when IFERROR hides the result. The second finds cells that show #REF! for other reasons, such as an out-of-range lookup. Then check defined names, conditional formatting, data validation and charts, which Find does not search.
Can I undo a #REF! error?
Yes, if you notice it at once. Press Ctrl+Z right after the deletion and Excel restores the cells and the references. Later, you have to rebuild each formula. Select #REF! in the formula bar, delete it, and enter the correct reference. A deleted worksheet cannot be recovered, so rebuild those references from an older version of the file.
Should I wrap formulas in IFERROR to hide #REF! errors?
No. IFERROR hides the error value, not the broken reference. The broken reference stays in the formula text, the cell returns your fallback value, and that value flows into totals. Fix the reference instead. If you inherit a model full of IFERROR, search for #REF! with Look in set to Formulas to find the hidden breaks.