Find and fix #REF!, #VALUE!, and #N/A errors
#REF! means a reference was lost. #VALUE! means the wrong kind of argument reached a function. #N/A usually means a lookup missed. The cells that already store those errors are listed by the formula-error finder, from the values saved in the file. Fix one pattern, recalculate, save, and look again. Wrapping every cell in IFERROR first hides the pattern.
Start from the stored value
Excel writes the error into the cell when it calculates, with the cell marked as an error type. The finder reads that stored value and the address. It does not calculate a fresh result, so it matches the file you saved, not the file you might get after a data refresh you have not run. If Excel is showing an error and this page is not, you are either on an unsaved calculation or the error is only a format or a chart. If this page shows an error and the sheet looks blank, you may be looking at the wrong sheet, or the text is white, or the sheet is hidden.
#REF! is the one to clear first
#REF! does not clear when new rows arrive. Something it pointed at is gone: a deleted row or column, a renamed or deleted sheet, a name that is itself #REF!, or an external workbook that no longer resolves. Pick one sample address. Look at the formula. If you see #REF! inside it, the reference was deleted in place. Restore the missing sheet or column from a copy, or rewrite the formula at the real range. If the formula looks intact and still returns #REF!, check defined names and external links. An ordinary-looking formula can call a broken name. Fix the name and the cells follow.
#VALUE! and type mismatches
#VALUE! appears when text meets arithmetic, when a range is used where a single value is required, or when a date is not a date. The sample cell’s inputs are the whole investigation. A classic case is a number stored as text in the lookup column, or a space in a cell that looks empty. Clean the input or convert it, and do not wrap the formula until the input is actually clean. A #VALUE! that appears only on some rows is a data problem on those rows. A #VALUE! on every row is the formula itself.
#N/A, blanks, and IFERROR
Lookups return #N/A when the key is absent. Sometimes that is the right signal: the key is not in the table, and someone should see that. Sometimes the table is wrong, the key has an extra space, or the match range is shorter than the table. Decide which case you have before you hide it. IFERROR or IFNA is appropriate for a missing key that should display as a blank. It is a bad wrap for #REF!, because it turns a broken workbook into a quiet one. The error finder will then show fewer errors and you will have lost the trail.
When you want the numbers from your own workbook, find the cached errors privately.
Other codes, briefly
#DIV/0! is division by zero or by a blank. Guard the divisor if blank is normal, or fix the source if zero is a hole in the data. #NAME? is a misspelled function or a name that does not exist. #NULL! is usually a space where a comma or a colon belonged. #NUM! is an impossible numeric argument. #SPILL! means a dynamic array could not write because cells below or beside it are occupied. Move the blockage. Do not convert the array to values unless you mean to freeze it.
One pattern at a time
Sort the report by count. The largest type on the busiest sheet is the pattern. Fix that pattern on a copy, calculate, and save. Run the finder again. The count should drop by about the number of cells you repaired. If it drops and then returns on calculate, a name or a link is recreating the error. If #REF! remains only in a very hidden sheet, unhide it before you decide the file is clean. The visible tabs are not the whole workbook.
Tie the repair to a total
Before you edit, write down the count of #REF!, #VALUE!, and #N/A from the finder, and write down one business total the workbook is supposed to show, such as a revenue figure or a row count. Repair the largest error pattern. Calculate. The error count should fall. The business total should move only if the errors were inside that total. If the total moves and you did not expect it, stop and understand the cell you changed. IFERROR will also make the error count fall, and it will move the total to whatever fallback you typed, which is why it is the last tool rather than the first. Hidden sheets are in the finder too. A clean visible sheet and a stubborn count means the remaining errors are on a sheet the tabs do not show. Check visibility before you declare the workbook clean.
Leave a trail
On the copy you keep, a short note of “#REF! in Commission was a deleted Bonus column; formulas now point at Amount” saves the next reviewer from rediscovering the same hole. The finder does not store that story. You do. The next file from the same process will break in the same place if the export still drops the column. Fixing the cell and fixing the export are both part of the repair when the workbook is generated every week. If the same #REF! returns next Monday, the cell edit was temporary and the export is still dropping the column the formula names. Keep the finder count in the same note as the business total. A future drop in the total with a flat error count is a data change. A future drop in the error count with a moving total is a formula change. Those two stories should not be confused when someone asks why the report moved. If the same pattern returns on the next export, change the export, then rerun the finder so the note and the count still agree.
Related reading
The closest repair notes are A list of volatile functions and When Break Links does nothing .
Walk the same checks in order with the Workbook audit checklist.
The terms behind this guide are Cached cell error and Defined name .
The paired check for that note is the Volatile formula checker for Excel.
Questions people ask before trusting the result
Why did repair in Excel remove errors and also remove a chart?
Excel’s repair is for a damaged package. Formula errors are valid content. Do not use repair to clear #REF!. Edit the formula.
The error is in a pivot. Will this list it?
Only if a worksheet cell stores an error. A pivot that displays an error in its cache is a pivot refresh problem, not a cell of type e, unless the source cell itself is an error.
Can I replace all errors with zero?
You can, and you will hide broken references. Replace with zero only where the business meaning of a failure is zero. #REF! is almost never that case.
How do I find the next cell after the sample list?
The sample list is capped. In Excel, use Go To Special and choose Errors on that sheet, or search for the error text in formulas. The count on this page tells you whether any remain.
Do shared formulas hide errors?
The error value is stored on the cell even when the formula is shared. The finder uses the cell type, so shared formulas do not hide it.