Formula Errors

How to Return a Blank Value for Excel Formula Errors

Elise Williams Sep 30, 2026 869 views

Question details

The user wants to hide formula errors in Excel by returning a blank cell instead of standard error values like #DIV/0! or #VALUE!.

How to Return a Blank Value When an Excel Formula Returns an Error

Product: Microsoft Excel
Device & OS: not provided
Scenario: Creating spreadsheets where some formulas might fail due to empty reference cells, missing lookups, or division by zero.
Observed behavior: Formulas currently display unsightly error codes which disrupt the visual flow of the spreadsheet data.

Before you start

Identify which specific formulas in your spreadsheet are generating the errors and ensure that the errors are expected (such as division by zero on an empty row) rather than a symptom of a deeper logic flaw.

Solution 1Recommended

Use the IFERROR Function to Return a Blank Cell

The IFERROR function is the most direct and efficient way to catch errors and replace them with a blank string.

The IFERROR function evaluates an expression and returns a custom value if the expression results in an error. By supplying an empty string ("") as the custom value, the cell will appear completely blank when an error occurs.

Select the target cell

Click on the cell containing the formula that is producing an error.

Edit the formula

Click into the formula bar at the top of the worksheet to edit your existing formula.

Wrap with IFERROR

Wrap your existing formula with IFERROR. For example, change =B1/A1 to =IFERROR(B1/A1, "").

Apply the new formula

Press Enter to apply the updated formula. The cell will now display as blank if it encounters an error.

Use the IFERROR Function to Return a Blank Cell

Supported Errors: IFERROR handles #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, and #NULL! errors automatically.

Solution 2

Use IF and ISERROR Functions (For Older Versions)

If you are using Excel 2003 or earlier, or need compatibility with older systems, use a combination of IF and ISERROR.

Effortless Spreadsheet Management

Handle Formula Errors Easily with WPS Spreadsheet

WPS Office offers a powerful, free alternative to Microsoft Excel with full support for advanced functions like IFERROR. You can easily manage complex data and keep your spreadsheets error-free and professional.

1. Open your document: Launch WPS Spreadsheet and open the document containing the formula errors.
2. Locate the formula: Click on the cell where the error is displayed.
3. Apply IFERROR: In the formula bar, type =IFERROR( before your existing formula, and append , "") to the end.
4. Fill the column: Press Enter and drag the fill handle downward to apply this clean formula to the rest of your column.

Fully compatible with Microsoft Excel formulas and functions, including IFERROR.Free, lightweight, and fast-loading office suite.Clean, intuitive tabbed interface for seamless spreadsheet management.Cross-platform support for Windows, Mac, Linux, and mobile.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my cell show #DIV/0! instead of a blank?

This happens when a formula attempts to divide a number by zero or by an empty cell. Wrapping your formula in IFERROR will catch this specific division error and return a blank instead.

Can I return text like 'Not Found' instead of a blank?

Yes. Inside the IFERROR function, replace the empty string ("") with your desired text wrapped in quotation marks, such as =IFERROR(VLOOKUP(...), "Not Found").

Is there a difference between the IFERROR and IFNA functions?

Yes. IFERROR catches all formula errors (including #DIV/0! and #VALUE!), while IFNA specifically only catches the #N/A error. IFNA is often used with VLOOKUP when a match isn't found, but you still want to be alerted to other types of calculation errors.

Excel & Data Problems