Excel IF, IFS, AND/OR and error handling
For office, finance, operations and service staff who build flags, statuses and bands in Excel: 'Late', 'Needs approval', commission rates, priority codes. Prerequisite: 'Spreadsheet fundamentals for office work' (cell references and simple formulas). You'll write IF formulas with the right comparison operator, combine conditions with AND, OR and NOT, choose between nested IF, IFS, SWITCH and a lookup table for banded results, handle errors with IFERROR, IFNA and the IS functions without hiding real faults, and test a formula with boundary values before sharing it. IFS and SWITCH need Excel 2019 or later, or Microsoft 365. For diagnosing error values in someone else's workbook, continue with 'Spreadsheet review: finding formula errors in someone else's workbook'; for #N/A from lookups, see 'Excel lookups: XLOOKUP, INDEX/MATCH and handling missing matches'. Based on Microsoft Support's function documentation. Your organisation's thresholds and spreadsheet standards apply on top.
- Level
- Beginner
- Length
- About 95 minutes
- Contents
- 5 lessons · final exam
- Status
- Published · updated 10 Oct 2026
Skills you'll practise
- Write IF formulas that compare values with the correct operator and return a stated result for both outcomes
- Combine IF with AND, OR and NOT to test several conditions at once
- Choose between nested IF, IFS, SWITCH and a lookup table for banded results, with conditions in the right order
- Handle errors with IFERROR, IFNA and the IS functions without hiding problems that need fixing
- Test a logical formula with boundary values, blanks and text before sharing the workbook
Course outline
- 1.Writing IF formulas that compare values and return both resultsLesson · 15 min
- 2.Combining IF with AND, OR and NOTLesson · 15 min
- 3.Choosing between nested IF, IFS, SWITCH and a lookup tableLesson · 18 min
- 4.Handling errors with IFERROR, IFNA and the IS functionsLesson · 15 min
- 5.Testing a logical formula before sharingLesson · 12 min
- 6.Excel IF, IFS, AND/OR and error handling: knowledge checkKnowledge check · 14 questions
- 7.Excel IF, IFS, AND/OR and error handling: practical exerciseKnowledge check · 1 question
- 8.Final exam9 questions · passing it completes the course, so people who already know the material can test out
Sources it draws on
The lessons and questions are written from these references, so learners can go back to the original.
- Microsoft Support: IF function
- Microsoft Support: Calculation operators and precedence in Excel (comparison operators)
- Microsoft Support: AND function
- Microsoft Support: OR function
- Microsoft Support: NOT function
- Microsoft Support: Using IF with AND, OR, and NOT functions in Excel
- Microsoft Support: IF function – nested formulas and avoiding pitfalls
- Microsoft Support: IFS function
- Microsoft Support: SWITCH function
- Microsoft Support: IFERROR function
- Microsoft Support: IFNA function
- Microsoft Support: IS functions (ISBLANK, ISERR, ISERROR, ISNA, ISNUMBER, ISTEXT and others)
See it with your own jobs and topics
Tell us about your team and we'll walk you through setup, from choosing jobs to your first skills check.