How to hide formula errors (show 0 or blank instead of #DIV/0!, #N/A)
✓ Verified in LibreOffice 25.8.7.3 ✓ Verified in Google Sheets (2026-08-30)Replace ugly error values like #DIV/0! or #N/A with a 0, a blank, or a friendly message.
The formula
| App | Formula | Notes |
|---|---|---|
| Excel | =IFERROR(A2/B2,0) | Return 0 on any error. Use "" for blank, or "Not found" for text. |
| Google Sheets | =IFERROR(A2/B2,0) | Identical. IFNA() catches only #N/A. |
| LibreOffice Calc | =IFERROR(A2/B2,0) | Identical. |
How it works
IFERROR(value, value_if_error) returns the second argument whenever the first would be any error. 10/0 is #DIV/0!, so this returns 0. Use IFNA() if you only want to trap #N/A and still see other errors.
Verified, not just documented
We ran =IFERROR(A2/B2,0) in LibreOffice 25.8.7.3 (headless, with forced recalculation) and it returned 0 — exactly the expected result. We then ran the same formulas in Google Sheets, executed 2026-08-30: a formula-only workbook goes into Google Drive, which converts it to a Sheet and recalculates every formula with Google’s own engine, and comes back out as .xlsx carrying the values Google computed. It returned 0 for the worked example, the same value LibreOffice produced. Both engines’ numbers on this page are executed results. The Excel formula follows Microsoft’s official documented syntax — we do not run Excel.
Functions used
IFERROR — see full Excel, Google Sheets & LibreOffice compatibility for each.
Related recipes
- How to extract numbers from text in a cell
- How to use IFERROR
- How to reference a cell on another sheet