Your sales report has a margin column that divides by units sold, and rows with no sales show #DIV/0!. That error contaminates the total and the chart you present to the director.
How to use IFERROR Formula step by step
1. Choose what to protect
A division, a VLOOKUP search, an INDEX and MATCH search, only the #N/A error with IFNA or a formula of your own with English names.
2. Fill in the details
Enter the cells of the operation and the text you want to see on error. If you leave it empty, the cell stays blank.
3. Copy the formula
Paste it into your sheet and drag it down. The errors stop showing, but check now and then why they were appearing.
The Step Count: Traditional Friction vs. KorexTools
Fixing errors one by one in a column (4 steps)
- Find the cells with an error.
- Understand why each one fails.
- Write an IF condition to avoid it.
- Repeat the condition down the whole column.
With the KorexTools “IFERROR Formula” builder (3 steps)
- You choose what to protect.
- You type the notice you want to see.
- You paste the result and extend it down the column.
Hiding an error is easy; what matters is hiding only the one you expect
IFERROR evaluates a formula and, if it returns any error, shows in its place whatever you indicate: a text, a zero or an empty cell. It is the usual move with divisions that may have a zero divisor and with lookups whose value may not exist, because it stops one #N/A from breaking the sums and charts that depend on that column.
The risk is that it hides every error, including those that point to a real failure, such as a broken reference. IFNA is more precise: it catches only #N/A, the typical error of a lookup with no result, and leaves the others visible. If you are looking something up, use IFNA; if you are calculating, IFERROR.
Which one to use
- •A lookup that may not find: IFNA, which covers only #N/A.
- •A division with a possibly zero divisor: IFERROR.
- •A long formula you do not control: IFERROR, with a clear notice.
With 10 in A2 and 0 in B2
| Case | Formula | Result |
|---|---|---|
| Protected division | =IFERROR(A2/B2,0) | 0 |
| Lookup with a notice | =IFERROR(VLOOKUP(E2,A:C,3,FALSE),"Not found") | Not found if E2 is missing |
| Only #N/A | =IFNA(VLOOKUP(E2,A:C,3,FALSE),"Not found") | Notice only if it does not exist |
Common Mistakes and How to Avoid Them
- ⚠️Using zero as the notice and having it added into the total by accident.
- ⚠️Wrapping a formula that still has a logic error.
Frequently Asked Questions
What is the difference between IFERROR and IFNA?
IFERROR catches any error; IFNA catches only #N/A. With a lookup, IFNA is better so that other errors, like a broken reference, stay in view.
Can I leave the cell blank on error?
Yes: leave the what-to-show field empty and the formula returns an empty string. Keep in mind that cell is no longer a number and may break later calculations.
Does IFERROR work with any formula?
Yes, it wraps the whole formula. Choose the own-formula option, type it with English names and the tool translates it into your language.
What I would do if I were in your shoes
«A visible error is fixed in minutes; a hidden one can live for years in a report.» — DataKorex Engineering Team.