Error values
An error value is a result that starts with # and names what went wrong, such as #N/A or #DIV/0!. It spreads through formulas, and hiding it can hide a gap in your audit evidence.
Last updated: 14 October 2026
Explains: error value
When a formula cannot produce a result, Excel does not leave the cell empty or guess: it returns an error value, a special result that starts with # and names the problem. Each type has its own cause, so the name is the first clue.
As an auditor you meet error values in every client workbook:
- in lookups that did not match
- in ratios with an empty denominator
- in totals that stopped working
What you do with them matters as much as what they say: an error that is fixed, counted or reported is information, an error that is silenced can be a missing population.
What an error value is
An error value is a result, not text and not a message in a dialog. The cell holds a real value of the error type, so other formulas can test it (ISERROR, ISNA) and so it behaves in a way a text never would: it passes through calculations.
The nine below are the ones you meet most often; current Excel has a few more, such as #FIELD! and #GETTING_DATA. They all start with #, and most end with ! or ?.
The names are translated. English Excel shows #N/A and #DIV/0!, Dutch Excel shows #N/B and #DEEL/0! for the same errors. In a formula you do not type the error name: you test for it with a function such as ISNA.
Two things look like errors and are not: a cell full of ##### usually means the column is too narrow for the number (or, for a date or time, that the value is negative), and a green triangle in the corner is a hint, not an error value.
The nine most common errors, their causes and fixes
The error type tells you where to look. Work from the name: #N/A points at a lookup, #DIV/0! at a divisor, #REF! at something that was deleted. Fix the cause in the cell that first shows the error, because the cells that depend on it show the same error without being wrong themselves.
| Error | Means | Typical cause | Fix |
|---|---|---|---|
| #N/A | A value is not available. | A lookup did not find its value: a typo, a trailing space, a number stored as text, or a lookup range that is too short. NA() returns it on purpose. | Check the lookup value against the list, character by character. Use IFNA only when not found is an expected answer. |
| #VALUE! | A value is of the wrong type. | Arithmetic on text, such as 100 + "n/a", or a cell that looks empty but holds a space. Dates and amounts imported as text. | Find the text cell with ISNUMBER, clean it with TRIM, and convert it with NUMBERVALUE. |
| #REF! | A reference is no longer valid. | A row, column or sheet that the formula pointed to was deleted, or a copied formula points outside the sheet. INDEX with a row number beyond the range gives it too. | Undo the deletion at once with Ctrl+Z. Afterwards the original reference is gone and you have to rebuild it. |
| #DIV/0! | A division by zero. | The divisor is 0 or an empty cell, for example a margin on a project without revenue. | Test the divisor first: IF(B2=0, ..., A2/B2). Ask why it is zero before you decide what to show. |
| #NAME? | Excel does not recognize a name. | A function name with a typo, a function your version does not have (XLOOKUP in Excel 2016), text without quotation marks, or a named range that does not exist. | Look at the part Excel underlines when you edit the cell, and check the spelling and the version. |
| #NUM! | A number is not valid here. | An impossible argument, such as the square root of a negative number, a result too large for Excel, or a rate function that finds no solution. | Check the arguments against what the function allows. For rate functions, give a starting estimate. |
| #NULL! | Two ranges do not intersect. | A space was typed where a separator belongs: =SUM(A1:A5 C1:C5). A space between two ranges asks for their intersection, and there is none. | Replace the space with a separator (a comma in English Excel, a semicolon in Dutch Excel) or a colon. |
| #SPILL! | The spill range is blocked. | A formula that returns several values finds a cell with content, a merged cell or a table in the way. | Clear or move whatever is in the way. Excel outlines the blocked range when you select the cell. |
| #CALC! | A dynamic array formula cannot finish its calculation. | Most often a FILTER that finds no rows and has no text for that case, so the result would be an empty array. | Give FILTER its third argument (if_empty), a text for the empty result, and decide whether no rows is a problem. |
How errors spread
An error value is contagious. A formula that uses a cell with an error as an input returns the same error, and so does every formula that uses that formula.
One #N/A in a column of 500 amounts makes SUM of the column return #N/A, and every subtotal, ratio and check built on that total follows. That is useful on purpose: a total never looks fine while a part is unknown.
A few functions stop the spread:
- COUNT skips error cells in a range (COUNTA counts them as filled)
- IF only evaluates the branch it picks, so an error in the other branch stays out
- IFERROR, IFNA and AGGREGATE exist to deal with errors
The first error you see is usually not the first error that happened: trace it back through the cells it depends on.
=SUM(B2:B100)
=SUM(B2:B100)
If one cell in B2:B100 holds #N/A, the total is #N/A as well, not the sum of the other cells.
=COUNT(B2:B100)
=COUNT(B2:B100)
Counts only the numbers and skips error cells, so this count is lower than the number of rows when errors are present.
Catching errors
Watch out: IFERROR(formula, 0) turns unknown into zero or an empty text: the column looks clean, the total adds up, and nobody sees that rows were never matched. For an audit that is a completeness problem, because the amounts you tested are not the amounts in the ledger.
Which function you choose decides which errors you accept. IFERROR replaces every error type with your fallback, including #REF! from a deleted column and #NAME? from a typo, which are mistakes in the workbook and not answers. IFNA replaces only #N/A, the one error that a lookup gives when the value is not there.
That is why IFNA is the safer choice around a lookup: a missing value gets your message, a broken formula stays visible.
To test instead of replace, use the IS functions in the table, which return TRUE or FALSE. To add up a range that contains errors, AGGREGATE with function number 9 (SUM) and option 6 (ignore error values) skips them. AGGREGATE reads the range as it is, so it also skips cells you may not want to lose: see the examples and the pitfalls.
| Function | Reacts to | Use it for |
|---|---|---|
IFERROR | Every error type | A fallback for a formula whose errors you understand, such as a division you have already tested. |
IFNA | #N/A only | A lookup where not found is an expected answer. |
ISERROR | Every error type, gives TRUE or FALSE | Flagging or counting every error in a column. |
ISNA | #N/A only, gives TRUE or FALSE | Flagging or counting the values a lookup did not find. |
ISERR | Every error except #N/A | Finding the errors that are something other than not found. |
AGGREGATE | With option 6, error values in the range | A sum, average, maximum or similar over a range that contains errors. |
=IFNA(XLOOKUP(E2,A2:A100, B2:B100), "Not found")
=IFNA(XLOOKUP(E2;A2:A100; B2:B100); "Not found")
Replaces only #N/A. A #REF! or #VALUE! from the lookup stays visible.
=IF(ISNA(MATCH(E2,A2:A100, 0)), "Missing", "Present")
=IF(ISNA(MATCH(E2;A2:A100; 0)); "Missing"; "Present")
Tests for #N/A without fetching a value. Useful for a completeness check of one list against another.
=AGGREGATE(9,6, B2:B100)
=AGGREGATE(9;6; B2:B100)
Adds up B2:B100 and skips the error cells. The result is a sum of fewer rows than the range has.
Finding errors in a workbook
In a large sheet you do not scroll for errors. For a quick look:
- press
F5and choose Special - tick Formulas and then only Errors: Excel selects every formula cell that shows an error
- repeat the step under Constants: a pasted value that is an error is a constant, not a formula
To count, use COUNTIF with the error as the criterion, or SUMPRODUCT with ISERROR when you want every type at once.
The criterion in COUNTIF is read like a typed entry, so use the error name your Excel shows and test the formula on a cell you know holds that error. Conditional formatting with the formula =ISERROR(A2) turns every error cell red, so the errors stay visible while you work.
=COUNTIF(B2:B100,"#N/A")
=COUNTIF(B2:B100;"#N/A")
Counts the cells in B2:B100 that hold #N/A. The criterion takes the error name of your Excel language.
=SUMPRODUCT(--ISERROR(B2:B100))
=SUMPRODUCT(--ISERROR(B2:B100))
Counts every error in B2:B100, whatever its type. ISERROR gives TRUE or FALSE per cell and -- turns that into 1 or 0.
=SUMPRODUCT(--ISERR(B2:B100))
=SUMPRODUCT(--ISERR(B2:B100))
Counts every error except #N/A. Compare it with the count above to see how many are something other than not found.
Why hiding errors is risky for an auditor
What this means in practice:
- in a workbook you receive from the client, treat IFERROR around everything as a reason to look closer: remove the IFERROR and count what comes out
- in your own workbooks, use IFNA with a visible message such as
Not found, so the rows can be filtered and counted, and keep a control cell that counts the errors with SUMPRODUCT - write down how many errors there were and what you did with them, and reconcile the total back to the ledger
Worked examples
Example1 of 4Beginner
Count the invoices the creditor lookup did not match
Veldhuis Installatie BV linked each purchase invoice to a creditor number with a lookup and sent you the result as values. Some invoices show an error instead of a creditor number. Before you test the purchase ledger you want to know how many invoices were not matched, and how many errors are of another kind.
| Row | A | B | C | D | E |
|---|---|---|---|---|---|
| 1 | Invoice no. | Creditor no. | Not matched | ||
| 2 | 25-1101 | C2001 | 33=COUNTIF(B2:B9, "#N/A")=COUNTIF(B2:B9; "#N/A") | ||
| 3 | 25-1102 | #N/A | |||
| 4 | 25-1103 | C2003 | |||
| 5 | 25-1104 | #N/A | |||
| 6 | 25-1105 | C2002 | |||
| 7 | 25-1106 | #VALUE! | |||
| 8 | 25-1107 | #N/A | |||
| 9 | 25-1108 | C2004 | |||
| 10 |
Swipe sideways to see all columns
Formula in D2
=COUNTIF(B2:B9,"#N/A")
=COUNTIF(B2:B9;"#N/A")
Result: 33
Step by step
- The criterion is the error value #N/A (in Dutch Excel #N/B). COUNTIF compares each cell of B2:B9 with it, and an error cell qualifies when it is that same error.
- B2 (C2001): a creditor number, no. B3 (#N/A): yes. B4 (C2003): no. B5 (#N/A): yes. B6 (C2002): no.
- B7 holds #VALUE!. That is an error, but a different one, so it does not qualify: no. B8 (#N/A): yes. B9 (C2004): no.
- Three cells qualify (B3, B5 and B8), so the result is 3: three invoices have no creditor number because the lookup did not find them.
- The #VALUE! in B7 is not in the count. It is a different problem, and the first variation below counts every error: 4. The difference of 1 is the cell to investigate separately.
Watch out: A count of 3 says how many invoices were not matched, not whether they matter. Get the amounts of those three invoices before you decide how much work they are.
More on this example: why it works, 3 variations
Why it works
Each error type is a different value, so a criterion can single out one type. COUNTIF counts the cells equal to the criterion, and the error counts as equal to itself. Counting by type gives you the size of each problem, which a single count of all errors cannot.
Variations
=SUMPRODUCT(--ISERROR(B2:B9))
=SUMPRODUCT(--ISERROR(B2:B9))
Counts every error, whatever its type: 4. It does not depend on the language of your Excel.
=SUMPRODUCT(--ISNA(B2:B9))
=SUMPRODUCT(--ISNA(B2:B9))
Counts only #N/A, like the main formula: 3. Prefer this version when the file is opened in different Excel languages.
=SUMPRODUCT(--ISERR(B2:B9))
=SUMPRODUCT(--ISERR(B2:B9))
Counts every error except #N/A: 1. These are the errors that are something other than not found.
Example2 of 4Intermediate
Show the margin per project without hiding a division error
Veldhuis Installatie BV recognizes revenue per installation project when the project is delivered. You recalculate the gross margin per project from the project list. Project P-2501 has costs but no revenue yet, so the margin cannot be calculated, and the sheet has to say so in words instead of showing #DIV/0! or a misleading 0%.
| Row | A | B | C | D | E |
|---|---|---|---|---|---|
| 1 | Project | Revenue | Cost | ||
| 2 | P-2501 | €0.00€ 0,00 | €1,840.00€ 1.840,00 | No revenue=IF(B2=0, "No revenue", (B2-C2)/B2)=IF(B2=0; "No revenue"; (B2-C2)/B2) | |
| 3 | P-2502 | €40,000.00€ 40.000,00 | €34,000.00€ 34.000,00 | ||
| 4 | P-2503 | €62,500.00€ 62.500,00 | €55,000.00€ 55.000,00 | ||
| 5 | P-2504 | €28,000.00€ 28.000,00 | €29,400.00€ 29.400,00 | ||
| 6 |
Swipe sideways to see all columns
Formula in D2
=IF(B2=0,"No revenue", (B2-C2)/B2)
=IF(B2=0;"No revenue"; (B2-C2)/B2)
Result: No revenue
Step by step
- B2 is 0, so the test B2=0 is TRUE. IF picks the text No revenue and does not evaluate the other branch, so the division by zero never happens.
- Without the test, (B2-C2)/B2 is (0 - 1840) / 0, a division by zero, and the cell would show #DIV/0!.
- Copied to D3, B3 is 40000, so the test is FALSE: (40000 - 34000) / 40000 = 6000 / 40000 = 0.15, a margin of 15%.
- In D4: (62500 - 55000) / 62500 = 7500 / 62500 = 0.12, so 12%. In D5: (28000 - 29400) / 28000 = -1400 / 28000 = -0.05, a loss of 5%.
- If a revenue cell holds text such as n/a, B2=0 is FALSE, the calculation runs and returns #VALUE!. That error stays visible, which is correct: the data is wrong, not merely empty.
Watch out: The result No revenue is text. A column that mixes percentages and this text cannot be summed meaningfully, and AVERAGE skips the text. Do not show 0% instead: that reads as a project that breaks even.
More on this example: why it works, 2 variations
Why it works
The test names the one cause you expect, a revenue of zero, and answers it with a message. Every other problem still produces its own error. An IFERROR around the same division would treat the expected case and all the unexpected ones in the same way.
Variations
=IFERROR((B2-C2)/B2,0)
=IFERROR((B2-C2)/B2;0)
Not recommended here: the result is 0, which reads as a 0% margin. A project without revenue and a project that breaks even look the same.
=IF(B2=0,NA(), (B2-C2)/B2)
=IF(B2=0;NA(); (B2-C2)/B2)
Returns #N/A on purpose, so charts skip the point and SUM-type totals do not quietly include it. Use it when the column feeds further calculations.
Example3 of 4Advanced
Map an account and keep a broken mapping visible
To map the accounts of Veldhuis Installatie BV to financial statement lines, you use the client's mapping table. An account that is not in the table must show Unknown account, so you can follow it up. But the table itself may contain a broken link, and that must not disappear behind the same message. In E1 you type the account to map.
| Row | A | B | C | D | E | F |
|---|---|---|---|---|---|---|
| 1 | Account | Statement line | Account to map | 49994999 | ||
| 2 | 11001100 | Cash and banks | Statement line | Unknown account=IFNA(XLOOKUP(E1, A2:A6, B2:B6), "Unknown account")=IFNA(XLOOKUP(E1; A2:A6; B2:B6); "Unknown account") | ||
| 3 | 13001300 | Receivables | ||||
| 4 | 40004000 | Personnel costs | ||||
| 5 | 45004500 | #REF! | ||||
| 6 | 80008000 | Revenue | ||||
| 7 |
Swipe sideways to see all columns
Formula in E2
=IFNA(XLOOKUP(E1,A2:A6, B2:B6), "Unknown account")
=IFNA(XLOOKUP(E1;A2:A6; B2:B6); "Unknown account")
Result: Unknown account
Step by step
- E1 holds 4999. XLOOKUP searches for 4999 in A2:A6: 1100, 1300, 4000, 4500, 8000. None is equal, so XLOOKUP returns #N/A.
- IFNA receives #N/A from its first argument and returns its second argument: Unknown account.
- Type 4000 in E1: XLOOKUP finds it as the third item and returns the third item of B2:B6, Personnel costs. There is no error, so IFNA passes the value through.
- Type 4500 in E1: XLOOKUP finds the fourth item and returns the fourth item of B2:B6, which is the cell with #REF!. That is not #N/A, so IFNA passes it through and E2 shows #REF!.
- With IFERROR in place of IFNA, 4500 would also show Unknown account. You would look for an account missing from the table, while the account is in the table and the table is damaged.
Watch out: The result Unknown account is text. If you add up a column of mapped amounts with SUMIF on the statement line, rows with that text drop out of every line, so check that the total of all lines still equals the ledger total.
More on this example: why it works, 2 variations
Why it works
The two situations produce two different errors: a missing account gives #N/A, a damaged mapping gives #REF!. IFNA answers the first and lets the second through, so each problem reaches the person who has to solve it.
Variations
=XLOOKUP(E1,A2:A6, B2:B6, "Unknown account")
=XLOOKUP(E1;A2:A6; B2:B6; "Unknown account")
The same behavior without IFNA: the fourth argument of XLOOKUP is only used when the value is not found. The result for 4999 is Unknown account.
=IFERROR(XLOOKUP(E1,A2:A6, B2:B6), "Unknown account")
=IFERROR(XLOOKUP(E1;A2:A6; B2:B6); "Unknown account")
Not recommended: for 4500 it also gives Unknown account and hides the #REF!.
Example4 of 4Advanced
Add up open balances past errors, and measure what is left out
The receivables list of Veldhuis Installatie BV has a balance column calculated with formulas. Two balances show an error, so the SUM of the column returns an error as well. You want a total for your working paper, and you want to know how many balances the total leaves out.
| Row | A | B | C | D | E | F |
|---|---|---|---|---|---|---|
| 1 | Customer no. | Open balance | Total without errors | 54346.554346,5=AGGREGATE(9, 6, B2:B9)=AGGREGATE(9; 6; B2:B9) | ||
| 2 | D1001 | €12,450.00€ 12.450,00 | Balances left out | |||
| 3 | D1002 | €8,300.50€ 8.300,50 | ||||
| 4 | D1003 | #N/A | ||||
| 5 | D1004 | €5,120.00€ 5.120,00 | ||||
| 6 | D1005 | #DIV/0! | ||||
| 7 | D1006 | €9,875.25€ 9.875,25 | ||||
| 8 | D1007 | €3,400.00€ 3.400,00 | ||||
| 9 | D1008 | €15,200.75€ 15.200,75 | ||||
| 10 |
Swipe sideways to see all columns
Formula in E1
=AGGREGATE(9,6, B2:B9)
=AGGREGATE(9;6; B2:B9)
Result: 54346.554346,5
Step by step
- =SUM(B2:B9) would return #N/A: B4 holds #N/A and the error spreads into the total.
- AGGREGATE has function number 9, which is SUM, and option 6, which means ignore error values. It skips B4 (#N/A) and B6 (#DIV/0!).
- The six remaining balances: 12,45012.450 + 8,300.508.300,50 = 20,750.5020.750,50; + 5,1205.120 = 25,870.5025.870,50; + 9,875.259.875,25 = 35,745.7535.745,75; + 3,4003.400 = 39,145.7539.145,75; + 15,200.7515.200,75 = 54,346.5054.346,50.
- The result is 54,346.5054.346,50. This is not the total of the receivables list. It is the total of six of the eight customers, and the sheet shows nothing that says so.
- So a second cell, E2 next to the label Balances left out, counts what was dropped: SUMPRODUCT(--ISERROR(B2:B9)) is 2 (B4 and B6). Put both next to each other: a total of 54,346.5054.346,50 with 2 balances not included.
Watch out: AGGREGATE with function numbers 1 to 13 needs a range. A calculated array such as B2:B9*1.21 gives #VALUE!. Use the numbers 14 to 19 (LARGE, SMALL and similar) if you need an array, or put the calculation in a helper column.
More on this example: why it works, 1 more pitfall, 2 variations
Why it works
Option 6 of AGGREGATE makes the function ignore every cell in the range that holds an error, whatever its type, so the calculation can finish. The cost is that the reader no longer sees that anything was skipped. A count of the skipped cells next to the total puts that information back.
More to watch out for
- A #N/A balance may be a customer missing from the rate table, and a #DIV/0! an invoice count of 0. Resolve the cause, then include them again; do not add them by hand.
Variations
=SUMPRODUCT(--ISERROR(B2:B9))
=SUMPRODUCT(--ISERROR(B2:B9))
Counts the balances the total leaves out: 2. Put it next to the AGGREGATE total.
=SUM(IFERROR(B2:B9,0))
=SUM(IFERROR(B2:B9;0))
The same total, 54346.5, in Excel 365. In Excel 2019 and earlier it must be entered with Ctrl+Shift+Enter; AGGREGATE needs no special entry.
Common mistakes
| Symptom | Cause | Fix |
|---|---|---|
| The total looks complete, but the lookups hide failed matches | A column of lookups ends in =SUM(...) that looks complete, because every lookup is wrapped in IFERROR(..., 0). | Unmatched rows silently add 0. Use IFNA with a text such as Not found and count the texts. |
| A test for #N/A never reacts, or returns an error itself | =IF(A2="#N/A", ...) never reacts, or returns an error itself, when A2 holds #N/A. | Comparing an error with anything returns that error, and the text "#N/A" is not the error value. Test with ISNA(A2), or ISERROR(A2) for every type. |
| ISERR counts 0 on a column that shows #N/A cells | =SUMPRODUCT(--ISERR(B2:B9)) returns 0 on a lookup column that clearly shows #N/A cells. | ISERR is false for #N/A by design. Use ISNA for the lookups that found nothing, ISERROR for every type. |
| SUM(IFERROR(...)) gives #VALUE! or a wrong total in older Excel | =SUM(IFERROR(B2:B9, 0)) gives #VALUE! or a wrong total in an older Excel. | Before dynamic arrays (Excel 2019 and earlier) the formula needs Ctrl+Shift+Enter. =AGGREGATE(9, 6, B2:B9) gives the same total in every version, and a count of the errors next to it shows what is missing. |
| A cell shows ##### and you look for a formula error | A cell shows ##### and you start looking for the formula error. | This is not an error value. The column is too narrow for the number or date, or a date or time is negative. Widen the column; if ##### stays, look for a negative date or time. |
| The average drops after you replace #DIV/0! with 0 | You fix the #DIV/0! in a ratio column with =IFERROR(A2/B2, 0) and the average of the column drops. | The zeros are counted as real ratios in the average. Test the divisor instead, =IF(B2=0, "", A2/B2), so those rows hold text, which AVERAGE skips. |