Two of the most useful functions in Excel are also two of the simplest. IF makes a decision: if this is true, show that; otherwise show something else. IFERROR catches errors and replaces them with something readable. Together, they turn fragile formulas into spreadsheets other people can actually use.
The IF function
=IF(logical_test, value_if_true, value_if_false)
- logical_test — a comparison, for example
B2>=60. - value_if_true — what to show when the test is true.
- value_if_false — what to show when it’s false.
Example: a test score in B2. =IF(B2>=60,"Pass","Fail") shows “Pass” for 60 and above, “Fail” below. The text needs quotes; numbers don’t.
Step-by-step: a grading sheet
- Put scores in column B.
- In C2, type
=IF(B2>=90,"A",IF(B2>=80,"B",IF(B2>=70,"C",IF(B2>=60,"D","F")))). - Press Enter and drag down. Each score becomes a letter grade.
- Count the quotes and brackets carefully — nested IFs are easy to mistype. Excel’s formula bar colour-codes matching brackets; use it.
If you have Microsoft 365 or Excel 2019+, the IFS function is cleaner for this job: =IFS(B2>=90,"A",B2>=80,"B",B2>=70,"C",B2>=60,"D",TRUE,"F"). Same result, much easier to read. And for a lookup-style grade table, VLOOKUP with TRUE or XLOOKUP with match mode -1 does the same thing with a table instead of nested logic.
The IFERROR function
=IFERROR(value, value_if_error)
IFERROR runs the first formula. If it works, you see its result. If it produces any error — #N/A, #DIV/0!, #VALUE! — you see the second argument instead.
The classic use is division. =A2/B2 shows #DIV/0! when B2 is empty or zero. Wrap it: =IFERROR(A2/B2,"—") or =IFERROR(A2/B2,0), and the sheet stays clean.
The other classic use is lookups. =IFERROR(VLOOKUP(A2,$A$10:$B$14,2,FALSE),"Not found") shows “Not found” instead of #N/A. (If you use XLOOKUP, its built-in fourth argument does this without IFERROR.)
Worked example
| Name | Score | Grade formula | Result |
|---|---|---|---|
| Mina | 92 | =IF(B2>=60,”Pass”,”Fail”) | Pass |
| Jae | 58 | =IF(B3>=60,”Pass”,”Fail”) | Fail |
| Sora | — | =IFERROR(B4/C4,”No data”) | No data |
IF makes the decision; IFERROR makes the failure graceful. Neither changes the underlying numbers — they only change what’s displayed.
Common errors
#VALUE! inside an IF test. Comparing text with a number, for example B2>=60 when B2 contains the text “sixty”, errors out. Make sure the tested cell holds the type you expect.
Missing quotes. =IF(B2>=60,Pass,Fail) without quotes makes Excel look for named ranges called Pass and Fail. Text results always need double quotes.
IFERROR hiding real mistakes. IFERROR catches every error, including typos in cell references. If a formula returns the wrong answer silently, check it without the IFERROR wrapper first. Use IFERROR narrowly — around one operation, not around a whole chain of calculations.
Too many nested IFs. Beyond three or four levels, nested IFs become unreadable and error-prone. Switch to IFS, a lookup table with VLOOKUP/XLOOKUP, or a helper column.
TRUE/FALSE as text. =IF(B2>0,"TRUE","FALSE") returns the text “TRUE”, which won’t work in later logical tests. Return the values TRUE and FALSE without quotes if other formulas need to test them.
Practice file
Download the IF & IFERROR practice file (.xlsx) — a grading sheet with nested IF and IFS side by side, division-by-zero examples, and a VLOOKUP wrapped in IFERROR.
Google Sheets
IF, IFS and IFERROR all work identically in Google Sheets. The same file opens in Sheets and the formulas keep working.
All amounts in this post are in nominal dollars — they are not adjusted for inflation. Example figures are illustrative, not market data.