Excel Formula Errors and Error Handling Practice Questions with Solutions

Introduction

Excel formulas can sometimes return errors when data is missing, incorrect, or a formula uses an invalid reference or calculation. Understanding common Excel errors and learning how to handle them is an important part of working with formulas. In this chapter, you will practice common errors such as #DIV/0!, #N/A, #VALUE!, #REF!, #NAME?, and #NUM!, along with the IFERROR function through practical examples. Excel Formula Errors and Error Handling practice questions with solutions to help you understand the concepts.

Q1. Handle a #DIV/0! Error Using IFERROR

Problem Statement

Calculate the average marks by dividing total marks by the number of students. If the number of students is zero, display "No students" instead of an error.

Sample Data

Excel Formula

For the first row:

=IFERROR(A2/B2,"No students")

Copy the formula down.

Output

Explanation

Normally:

=A2/B2

when B2 is zero, Excel returns:

#DIV/0!

IFERROR replaces that error with the text you specify.

=IFERROR(A2/B2,"No students")

Concept Covered

• IFERROR
• Division by zero
• Error handling

Q2. Handle #N/A When a Product Is Not Found

Problem Statement

Use VLOOKUP to find a product price. If the product does not exist in the table, display "Product Not Found" instead of #N/A.

Product Data

Suppose cell E2 contains:

Laptop

Excel Formula

=IFERROR(VLOOKUP(E2,A2:B5,2,FALSE),"Product Not Found")

Output

If E2 contains Laptop:

55000

If E2 contains Webcam:

Product Not Found

Explanation

When VLOOKUP cannot find the requested product, it returns:

#N/A

IFERROR catches that error and displays a meaningful message.

Concept Covered

• IFERROR
• VLOOKUP
• #N/A
• Missing data

Q3. Fix a #VALUE! Error Caused by Text

Problem Statement

You want to add two numbers, but one cell contains text instead of a number. Handle the error using IFERROR.

Sample Data

Excel Formula

=IFERROR(A2*B2,"Invalid data")

Copy the formula down.

Output

Explanation

Excel cannot multiply the text "Five" as a number.

Without error handling, the formula can return:

#VALUE!

Using:

=IFERROR(A5*B5,"Invalid data")

displays:

Invalid data

Concept Covered

• #VALUE!
• IFERROR
• Numbers vs text
• Data validation

Q4. Handle a Missing Cell Reference

Problem Statement

Suppose a formula refers to a cell that has been deleted. Understand the resulting #REF! error and learn how IFERROR can handle formula errors.

Example

A formula may originally be:

=A2+B2

If a referenced cell or range is deleted in a way that makes the reference invalid, Excel can produce:

#REF!

Error-Handling Example

For an operation that may produce an error:

=IFERROR(A2/B2,"Calculation Error")

Output

If the calculation is valid:

50

If the calculation produces an error:

Calculation Error

Explanation

#REF! means that a formula contains an invalid cell reference.

IFERROR can return an alternative result when the formula being evaluated produces an error.

Concept Covered

• #REF!
• Invalid references
• IFERROR
• Formula troubleshooting

Q5. Handle #N/A in a Student Marks Lookup

Problem Statement

You have student marks and want to find the marks of a particular student. If the student is not present, display "Student Not Found".

Sample Data

Suppose cell E2 contains the student’s name.

Excel Formula

=IFERROR(VLOOKUP(E2,A2:B6,2,FALSE),"Student Not Found")

Example 1

If:

E2 = Priya

Output:

92

Example 2

If:

E2 = Rohan

Output:

Student Not Found

Explanation

VLOOKUP returns #N/A when it cannot find the student.

IFERROR replaces that error with a readable message.

Concept Covered

• VLOOKUP
• #N/A
• IFERROR
• Lookup error handling

Q6. Handle Errors in a Discount Calculation

Problem Statement

Calculate the discount amount using:

Price × Discount %

If either value causes a formula error, display "Check Input".

Sample Data

Excel Formula

=IFERROR(B2*C2,"Check Input")

Output

Explanation

For the laptop:

55000 × 10% = 5500

If the price or discount value contains invalid data that causes an error, IFERROR displays:

Check Input

Concept Covered

• IFERROR
• Percentage calculations
• Error handling
• Sales calculations

Q7. Handle Errors in a Profit Calculation

Problem Statement

Calculate profit using:

Selling Price - Cost Price

Use IFERROR to handle unexpected formula errors.

Sample Data

Excel Formula

=IFERROR(C2-B2,"Invalid Data")

Output

Explanation

For Laptop:

55000 - 45000 = 10000

The formula calculates the profit while providing an alternative message if an error occurs.

Concept Covered

• IFERROR
• Profit calculation
• Formula error handling

Q8. Use IFERROR with INDEX and MATCH

Problem Statement

Find the price of a product using INDEX and MATCH. If the product is not available, display "Not Available".

Sample Data

Suppose the product name is entered in E2.

Excel Formula

=IFERROR(INDEX(B2:B5,MATCH(E2,A2:A5,0)),"Not Available")

Example 1

If:

E2 = Keyboard

Output:

2500

Example 2

If:

E2 = Webcam

Output:

Not Available

Explanation

The formula has two main parts:

MATCH(E2,A2:A5,0)

finds the position of the product.

Then:

INDEX(B2:B5,...)

returns the corresponding price.

If MATCH cannot find the product, it produces an error. IFERROR handles that error.

Concept Covered

• INDEX
• MATCH
• IFERROR
• Lookup error handling

Q9. Display Different Messages for Valid and Invalid Calculations

Problem Statement

Calculate the percentage:

Obtained Marks ÷ Total Marks × 100

If Total Marks is zero, display "Cannot Calculate".

Sample Data

Excel Formula

=IFERROR(B2/C2*100,"Cannot Calculate")

Output

Explanation

For Rahul:

450 ÷ 500 × 100 = 90%

For Amit:

0 ÷ 0

causes:

#DIV/0!

IFERROR replaces the error with:

Cannot Calculate

Concept Covered

• IFERROR
• Division by zero
• Percentage calculation
• Error messages

Q10. Create an Error-Safe Sales Lookup

Problem Statement

Create a small product lookup system.

The user enters a product name and Excel should return:

1. Product price
2. Available stock
3. Total inventory value

If the product does not exist, display "Product Not Found" instead of an error.

Sample Data

Suppose the product name is entered into:

E2

1. Find Product Price

=IFERROR(VLOOKUP(E2,A2:C6,2,FALSE),"Product Not Found")

2. Find Product Stock

=IFERROR(VLOOKUP(E2,A2:C6,3,FALSE),"Product Not Found")

3. Calculate Inventory Value

=IFERROR(VLOOKUP(E2,A2:C6,2,FALSE)*VLOOKUP(E2,A2:C6,3,FALSE),"Product Not Found")

Example

If:

E2 = Laptop

Output:

Calculation:

55000 × 10 = 550000

If:

E2 = Printer

Output:

Product Not Found

Explanation

This example combines:

• VLOOKUP
• IFERROR
• Multiplication
• Lookup-based calculations

Instead of showing technical Excel errors to the user, the formulas return a meaningful message.

Concepts Covered

• IFERROR
• VLOOKUP
• Error-safe formulas
• Inventory calculations
• Lookup-based analysis

Key Takeaways

• Excel displays different error codes for different formula problems.
• #DIV/0! commonly occurs when dividing by zero.
• #N/A commonly occurs when a lookup cannot find a matching value.
• #VALUE! often occurs when a formula receives an unexpected data type.
• #REF! indicates an invalid cell reference.
• #NAME? can occur when Excel does not recognize a function or name.
• #NUM! indicates a problem with a numeric calculation.
• IFERROR lets you provide an alternative result when a formula returns an error.
• IFNA is useful when you specifically want to handle #N/A.
• Error handling makes Excel reports easier for users to understand.
• During troubleshooting, it is often better to identify the original error before hiding it with IFERROR.

FAQs

1. What is IFERROR in Excel?

IFERROR is an Excel function that checks whether a formula produces an error. If an error occurs, it returns the alternative value you specify.

Example:

=IFERROR(A2/B2,0)

2. What does #DIV/0! mean in Excel?

#DIV/0! usually means that a formula is trying to divide a number by zero or by an empty cell.

For example:

=100/0

produces a #DIV/0! error.

3. How can I remove #N/A from a VLOOKUP formula?

You can use IFERROR:

=IFERROR(VLOOKUP(E2,A2:B10,2,FALSE),"Not Found")

4. What does #VALUE! mean in Excel?

#VALUE! generally indicates that a formula is receiving a value of an unexpected type. For example, trying to perform a numeric calculation using incompatible text can cause this error.

5. What does #REF! mean in Excel?

#REF! means that a formula contains an invalid cell reference. This can happen when cells or ranges referenced by a formula are deleted or otherwise made invalid.

6. What is the difference between IFERROR and IFNA?

IFERROR handles errors generally, while IFNA specifically handles the #N/A error.

7. Should I use IFERROR on every Excel formula?

No. IFERROR should be used when an error is an expected possibility and you want to provide a useful alternative result. During troubleshooting, hiding errors can make it harder to identify the real problem.

Written by Shubhranshu Shekhar, who has trained 20000+ students in coding.

Rishabh Kumar