Fixing Broken Models
Swipe to show menu
Small data issues can quickly break calculations, summaries, and dashboards. In this chapter, add error handling to make the workbook more stable and easier to debug.
IFERROR Structure
=IFERROR(value, value_if_error)
Formula used in this chapter:
=IFERROR(XLOOKUP(D2, Products!A:A, Products!D:D) * G2, 0)
value: formula being evaluated;value_if_error: fallback value returned when an error occurs.
Common Excel Error
#VALUE!
This error usually appears when Excel tries to calculate using the wrong data type, such as text instead of numbers.
Inside the Units column, replace one numeric value with text.
Example:
Four
Notice that the Revenue formula now returns:
#VALUE!
Review the affected row and summary calculations. Notice that broken data can propagate errors through dependent formulas.
Update the Revenue formula in H2:
=IFERROR(XLOOKUP(D2, Products!A:A, Products!D:D) * G2, 0)
XLOOKUP(...) * G2: main calculation;0: fallback value if the formula fails.
Press Enter and apply the formula down the column.
Replace the fallback value with a custom message:
=IFERROR(XLOOKUP(D2, Products!A:A, Products!D:D) * G2, "Check data")
This makes broken rows easier to identify during debugging.
Inside the dataset, test a product that does not exist in the Products table.
Example:
Airpods
Update the formula:
=XLOOKUP(D2, Products!A:A, Products!D:D, 0)
0: value returned if the product is not found.
1. What is the main purpose of IFERROR in a formula?
2. What happens when a value like "Four" is used in a numeric multiplication?
3. Why would you use if_not_found in XLOOKUP?
Thanks for your feedback!
Ask AI
Ask AI
Ask anything or try one of the suggested questions to begin our chat
