Excel Formula Errors: How to Fix #VALUE!, #REF!, #NAME?

Excel formula errors are the codes that start with a pound sign, such as #VALUE!, #REF! and #NAME?, and each one points to a specific cause. That's good news: the code alone tells you where to look. What doesn't fix anything is wrapping the cell in IFERROR so the code disappears. The error is gone from the screen, but the total is still wrong, and that number goes straight into a month-end cost report or a customer quote.

Below: what each code means, how to fix each one, how to find every error in a large workbook at once, a worked cleanup of a shared cost file, and when IFERROR is the right tool.

What each Excel error code means

A small green triangle in a cell's corner is different. The formula calculated, but Excel wants you to look at something, such as "Number Stored as Text" or "Inconsistent Formula". Click the cell and the warning button next to it says what it suspects.

How to fix each error

#VALUE!: clean up text and spaces

Quantities typed as "5 ea", prices with a currency word, or a cell that looks empty but holds a space will stop arithmetic. Microsoft's #VALUE! troubleshooting article lists hidden spaces in "blank" cells among the most common causes.

Move units into the column header and keep only numbers in the cells. Remove spaces with TRIM and non-printing characters with CLEAN; for tables copied from a web page, =SUBSTITUTE(A2,CHAR(160),"") strips non-breaking spaces.

=SUM(A2:B2) skips text, so switching from =A2+B2 makes the error go away. But if the skipped value was a number that should have been counted, the total is now quietly too small. Converting the text is the real fix.

Numbers stored as text need the format changed to General and then re-entered with F2 and Enter. For a whole column, select it, open Data > Text to Columns, and click Finish without changing anything; every value is re-entered as a number.

#REF!: undo right away

Deleting a column or sheet that formulas referenced turns those references into #REF!. Right after the delete, Ctrl+Z is the cleanest fix. If the file has been saved and closed, look at where #REF! sits in the formula bar and work out from neighboring formulas what it pointed to. Converting ranges to tables with Ctrl+T and using structured references like Costs[Freight] makes this far less likely, because the reference follows the column.

#NAME?: typos and quotes

Check in this order: a misspelled function such as SUMM, text missing its quotes as in =IF(A2=Done,1,0), a reference to a deleted defined name, and a missing colon as in =SUM(A2A10). Picking functions from the autocomplete list with Tab removes most typos. Newer functions such as XLOOKUP and FILTER also show #NAME? when the file is opened in Excel 2016.

#DIV/0!: check the denominator

Blank cells count as zero. If a zero denominator is a real situation, like an item with no production this month, test it first: =IF(C2=0,"-",B2/C2). If the denominator should never be blank, filling in the missing value is the fix.

#N/A: the lookup found nothing

VLOOKUP, MATCH and XLOOKUP return #N/A when the value isn't there. If it truly isn't, the formula is working. If the value clearly exists, check for leading or trailing spaces, a code stored as text on one side and as a number on the other, and VLOOKUP's fourth argument set to FALSE.

#SPILL!, #NUM!, #NULL! and circular references

#SPILL! appears when a dynamic array formula (FILTER, UNIQUE, SORT and others in Microsoft 365) has something in the way. Click the formula and the intended range shows as a dashed border; clear it. Microsoft's #SPILL! article also notes that spilled arrays aren't supported inside Excel tables. #NUM! comes from impossible math such as =SQRT(-1) or an IRR that doesn't converge. #NULL! means a space was typed where a comma belonged, as in =SUM(A1:A5 C1:C5). Circular references are usually a total whose range includes its own cell; Formulas > Error Checking > Circular References names the cell.

Find every error cell at once

On a workbook with several sheets and hundreds of rows, don't hunt by eye. Excel can select every error for you:

1. Press Ctrl+G (or F5) and click Special to open Go To Special.
2. Choose Formulas, leave only Errors checked, and click OK.
3. Every formula cell with an error is selected. Give them a fill color and you can see where they cluster.

Then narrow down the cause with the Formula Auditing group on the Formulas tab. Error Checking steps through the sheet's errors one at a time. Evaluate Formula walks through a nested formula one step at a time, and the first step that produces an error is the cause. Trace Precedents draws arrows to the cells a formula reads, which shows whether an error started in that cell or flowed in from elsewhere. Microsoft's guide to detecting formula errors covers each tool.

Worked example: cleaning up a shared cost file

The cost accountant at Ridgeline Manufacturing reconciles a product cost workbook every month-end. Production, purchasing and logistics each maintain their own sheet in it, and this month it opens with more than 40 errors.

Step 1: Mark the errors on every sheet

Using Go To Special on each sheet, the accountant selects the error cells and fills them yellow: 18 #REF! on Purchasing, 9 #DIV/0! and 12 #VALUE! on Production, and 3 #NAME? on Summary.

Step 2: Break the chain first

Some Summary errors come from the Purchasing #REF! cells. Trace Precedents leads back to one place: someone deleted the freight column on Purchasing, which broke 18 formulas at once. Freight still exists on the Logistics sheet, so the accountant rewrites the first formula to point there and fills it down. That one fix also clears several Summary errors.

Step 3: Fix the data errors

The #VALUE! cells on Production are quantities typed as "5 ea". Find and Replace removes " ea" and the values become numbers. The #DIV/0! cells are items with no production this month, so they become =IF(Units=0,"-",Cost/Units). The three #NAME? errors are VLOOKUPP typos.

Step 4: Compare totals before and after

Writing the before and after totals per sheet side by side explains the change in the cost report: with freight back in, total product cost rose 2.1%. A final Go To Special pass confirms nothing is left.

👉 How to Copy Formulas in Excel Without Breaking References

IFERROR belongs on formulas you've already checked

=IFERROR(formula,value_if_error) replaces every error with the value you choose. IFERROR is convenient, but it hides #REF! and #NAME? just as readily as an expected #N/A, and those two mean the formula itself is broken.

Use it after you've confirmed the formula is right, for errors you expect. To handle only failed lookups, IFNA catches #N/A and lets everything else through. For division by zero, an IF on the denominator handles that one case without masking others.

Returning "" looks blank but isn't: COUNTA counts it and charts may plot it as zero. For a column that feeds a chart, return NA() instead so the line simply breaks at that point.

When formulas show as text

If a cell displays a formula like =B3+C3 instead of its result, one of four things is going on. The cell is formatted as Text: switch to General and re-enter with F2 and Enter. The whole sheet shows formulas: Show Formulas is on, so press Ctrl and the grave accent key (left of 1) to turn it off. Results don't update when inputs change: Formulas > Calculation Options is set to Manual, so set it to Automatic or press F9. There's a leading space or apostrophe: delete it in the formula bar and press Enter.

Errors that depend on your Excel version

Microsoft 365 and Excel 2021 added dynamic arrays, and with them new codes. FILTER returns #CALC! when no rows match, which means an empty result rather than a broken formula; =FILTER(A2:C500,B2:B500="Return","None") supplies text for that case. Open such a workbook in Excel 2016 or 2019 and those formulas gain an _xlfn. prefix and show #NAME?, because the function doesn't exist there; the only fix is a different function. Going the other way, older formulas opened in Microsoft 365 may show an @ sign, which tells Excel to return a single value as older versions did, and results don't change. If you swap files with suppliers or customers, knowing which Excel version they run saves a round of these errors.

FAQ

Q. Why does my formula keep returning 0?

Usually the referenced numbers are stored as text, and SUM skips them. If COUNT and COUNTA on the same range give different results, text numbers are mixed in. Also check whether calculation is set to Manual.

Q. What's the difference between #N/A and #VALUE!?

#N/A means the lookup found nothing; the formula itself is fine. #VALUE! means the data types didn't fit and the calculation couldn't start, so clean up text and spaces in the referenced cells.

Q. Why do I see #NAME? only on a coworker's computer?

Their version of Excel doesn't have that function. XLOOKUP, FILTER, UNIQUE and TEXTJOIN aren't in Excel 2016, so shared files should use functions both versions support, such as VLOOKUP or INDEX and MATCH.

Q. Can I total a column while skipping the errors?

=AGGREGATE(9,6,D2:D500) sums the range and ignores error cells. The total is smaller by whatever it skipped, so use it to see the situation while you fix the causes, not as the final number.

How inline AI fixes formula errors

With the cost workbook open, you ask inline AI to "group every error by cause across all sheets and fix the clear ones," and it sorts them into #REF!, #VALUE!, #DIV/0! and the rest, fixes them, and shows which cells changed and how each sheet's total moved. With ARP, product cost is calculated from production and purchasing records directly, so there's no shared cost workbook to break.