Hiding every error
IFERROR(B2:B13,G2) also replaces division, reference and value errors that must remain visible.
Replace only missing-value errors
Read-only
Step-by-step guide · read-only ·
Loading your interactive spreadsheet…
Preparing the worksheet and formulas…
Data & tools
Task requirements
Enter one formula in G5. Return twelve rows from B2:B13 in their original order. Replace only genuine #N/A with the literal text in G2. Preserve every other real error with its original error type. Preserve numbers, Boolean values and literal text, including strings that look like errors. Normalize an empty source cell to empty text rather than numeric zero. Do not add a heading.
Edit a source value and its type
Text that looks like an error and a genuine error have different types. Apply an explicit type to the current worksheet, then check the complete report again.
The draft matches the current worksheet value and type.
Follow the highlighted cells. Switch to Your turn to edit. Answer cells: G5:G16. Enter one formula in G5; keep the rest of the output area clear. Browser calculations may differ from your Excel version.
Selection & formula search
Practice stays on this device. No worksheet data is uploaded.
Notes & examples
About this lesson
Use one literal fallback for real #N/A while keeping other errors visible and preserving ordinary value types.
THE QUICK ANSWER01
Use one literal fallback for real #N/A while keeping other errors visible and preserving ordinary value types.
ƒx
=IF(ISBLANK(B2:B13),"",IFNA(B2:B13,$G$2))| G |
|---|
| #DIV/0! (formula error) |
| "Missing" |
| #VALUE! (formula error) |
| #REF! (formula error) |
| 0 |
| -4 |
| 2.5 |
| "#N/A" |
| "#DIV/0!" |
| TRUE |
| Empty text ("") |
| Empty text ("") |
The remaining reserved output cells are blank. Keep that area clear when trying the formula.
The formula uses the interactive worksheet above. Adjust ranges and criteria for your own data.
How it works
The first error may be the right answer
The demonstration starts with a genuine #DIV/0!. Keeping that error in G5 is correct for this task; replacing it with Missing would conceal a different failure. Assessment checks the entire spill and its error types.
Use the narrow fallback policy
IFERROR handles all errors. IFNA is appropriate here because only a missing-value error has an approved fallback. Other failures stay visible for investigation. Neither function repairs a broken source or reference.
Change the fallback without changing its type
G2 accepts nonempty literal text within 40 characters. A literal fallback that looks like an error remains text, while a preserved source error remains an error. The source type editor makes this difference explicit.
Read the full step-by-step walkthrough
- Choose one literal fallback
G2 is nonempty text. Even a fallback such as "#DIV/0!" or "0" stays text.
- Handle an empty cell explicitly
ISBLANK identifies empty source cells. The empty-text branch prevents a blank reference from becoming numeric zero.
- Replace only #N/A
IFNA changes only genuine missing-value errors and leaves all other errors visible.
- Keep nonmissing failures visible
The first division error and the later value/reference errors retain their original error types. Only a missing-value error has this fallback policy.
- Retain ordinary values and literal fallback text
The remaining results keep signed numbers, zeroes, booleans and literal strings. A fallback that looks like an error is still text, and the blank branch remains empty text.
Things to watch
Reclassifying error-looking text
The text "#N/A" is an ordinary string and must pass through unchanged.
Turning a blank into zero
An explicit blank rule keeps missing content separate from an observed numeric zero.
About this example
Original typed teaching inputs, independent source/type rules and complete error/spill checks. Native Excel and Sheets recalculation/edit/save remain separate verification steps.
Content version 1. Practice checks compare calculated results across different datasets; they do not certify every possible formula.