Excel Replace #VALUE! Errors With Blank Cells in Formulas

To show a blank instead of an Excel #VALUE! error, wrap the formula that produces it in IFERROR. For an error already sitting in a cell, you can clear that cell with Go To Special, but doing so deletes its contents; it does not preserve a formula and make the formula result blank.

Choose the right kind of fix

There are two different situations behind the request to replace a value error with a blank. A formula may be returning #VALUE!, in which case the usual fix is to adjust the formula or handle its error. Or a cell may contain an unwanted error value that you are willing to remove. Those cases call for different actions: changing a formula keeps the calculation, while clearing a cell removes its contents.

For the formula method, assume A2 contains a formula that currently returns #VALUE!. Enter this in another cell, such as B2:

=IFERROR(A2,"")

B2 displays as blank when A2 contains an error. The empty text between the quotation marks is the formula’s result; it is not the same as a genuinely empty cell. If you can edit the original formula, it is usually clearer to put IFERROR around the calculation itself rather than add a separate helper column.

Use IFERROR to hide formula errors

IFERROR evaluates an expression and returns the expression’s result if it does not produce an error. If it does produce an error, the function returns the alternative you provide. To display nothing for an error, use an empty text string, written as "". This handles #VALUE! as well as other Excel errors, including #DIV/0!, #REF!, and #N/A.

For example, suppose A2 contains a quantity and B2 contains a price, and the multiplication sometimes fails because one of the cells contains text that cannot be used as a number. Put the calculation in C2 like this:

=IFERROR(A2*B2,"")

If A2 is 12 and B2 is 3, C2 returns 36. If the multiplication produces an error, C2 displays blank instead. This pattern works in Microsoft 365 for Windows and in current Excel for Mac and the web. Copy the formula down to apply it to other rows; relative references will adjust automatically.

Hiding an error is not always the same as fixing it. If a report should never contain text in a quantity column, correcting the source data may be better than masking the symptom. Consider adding a note, checking the input, or using a more specific test if an error could signal a real problem that someone needs to investigate.

Use IFNA when only #N/A should be blank

If the only error you want to suppress is #N/A, use IFNA rather than IFERROR. This distinction matters in lookup formulas: a missing match may be expected, while a different error may indicate a broken reference or unsuitable input. IFNA leaves those other errors visible instead of concealing them.

Suppose A2 contains a lookup value and a lookup formula in B2 returns #N/A when there is no match. Wrap that existing lookup formula in IFNA, with the intended formula in place of the example expression:

=IFNA(your_lookup_formula,"")

Replace your_lookup_formula with a real Excel formula before using it; the words are a placeholder, not a function name. For instance, if a lookup formula returns a match successfully, the result remains visible. If it returns #N/A, the cell displays blank. A #VALUE! error still appears, which can help reveal a separate problem.

Clear existing error cells without keeping formulas

If you have reviewed the errors and genuinely want to remove their cell contents, Excel’s Go To Special feature can select error cells. On Windows in Microsoft 365, select the relevant range, then choose Home > Find & Select > Go To Special. Choose Formulas and select only Errors under the formula options, or choose Constants and select Errors for error values that are constants. Click OK to select matching cells.

Press the Delete key to clear the selected contents. Check the selection carefully first: clearing a formula cell deletes its formula, not just its displayed error. If you clear a formula by mistake, use Ctrl+Z immediately to undo the change. For a permanent cleanup, save a copy of the workbook before removing formulas or large groups of data.

Go To Special selects cells that contain errors; it does not replace their results with formula-generated blanks. It can also select different error types, not just #VALUE!. If only one error type should be changed and formulas must remain, edit or wrap the relevant formulas instead of clearing every selected error cell.

Why Ctrl+H is usually not the best solution

Find and Replace, opened with Ctrl+H on Windows, is useful for replacing text in cell contents or formulas. It is not a dependable way to turn formula errors into blank results while preserving the calculations. A formula’s displayed error is the result of evaluating that formula; replacing text in the formula is a different operation and may damage the calculation. A blank replacement can also remove cell contents rather than provide an empty-text result.

For a formula that returns an error, use IFERROR or IFNA. For error cells that can be discarded, use Go To Special and clear the contents after reviewing the selection. If you are using Find and Replace for literal text such as the word error, check the dialog’s search scope and selection carefully so the change does not affect formulas or unrelated cells.

What causes #VALUE! in the first place?

#VALUE! generally means that a formula received a value or argument it cannot use in the requested calculation. The exact cause depends on the formula, so replacing the display alone may leave the underlying data issue unresolved. Common examples include:

Text used in arithmetic: a cell looks like a number but contains text, perhaps because it includes a word or an unexpected character.
Unexpected spaces or characters: pasted data may include leading spaces or other characters that prevent a value from being interpreted as intended.
Incompatible formula arguments: a function may receive text where it expects a number, date, or other kind of value.
Incorrect ranges or references: a formula may combine inputs in a way that does not fit the operation or function being used.

To investigate, select the cell and inspect the formula bar, then check the referenced cells and their underlying contents. Excel’s formula auditing tools, including Formulas > Evaluate Formula, can help show how a calculation is evaluated. Correcting the source value or formula is preferable when the error indicates bad data; use a blank result when the error is an expected possibility in the workbook’s design.

What a formula-generated blank means

A cell displaying nothing because its formula returned "" is not truly empty. The formula remains in the cell, and the cell contains a zero-length text result. This difference can matter when another formula checks whether a cell is empty, when data is filtered, or when a range is used for reporting.

For example, ISBLANK checks whether a cell is actually empty; it returns FALSE for a cell containing a formula, even when that formula displays a blank string. A formula that counts numeric results, such as SUM, generally ignores text results, but other functions may treat zero-length text differently. If downstream formulas depend on empty-cell behavior, test the workbook using realistic inputs rather than assuming a displayed blank behaves exactly like a cleared cell.

Blanks can also affect charts and exports. A formula returning "" may be handled differently from a truly empty cell depending on the chart, function, or destination application. If a blank is intended to mean “not available,” document that meaning so another user does not mistake it for a zero or a missing calculation.

How to decide which method to use

Use the method that matches what should happen to the data, not just what looks tidy on the screen:

Keep the calculation and hide any error: wrap the formula in IFERROR and return "".
Hide only a missing lookup match: use IFNA, so other errors remain visible.
Remove unwanted error cells entirely: select them with Go To Special and clear contents only after confirming that no needed formulas will be lost.
Find the cause: inspect the formula and input values, then correct the source of the #VALUE! result.

For shared workbooks, consider whether other users need to see errors during review. Suppressing every error can make a polished report easier to read, but it can also hide a broken calculation. A common compromise is to keep a diagnostic or source-data sheet where errors remain visible, while using a presentation sheet that displays blanks for expected exceptions.

Frequently Asked Questions

How do I replace #VALUE! with blank in Excel?
Wrap the formula that returns the error in IFERROR and use "" as the alternative result. For example, a formula cell that references A2 can use =IFERROR(A2,""). This shows a blank while keeping the formula.

Can I replace an error with a blank without deleting the formula?
Yes. Change the formula to handle the error with IFERROR, or use IFNA if only #N/A should be hidden. Clearing the cell with Delete removes its formula, so it is not the right choice when the calculation must remain.

Does IFERROR make the cell truly empty?
No. The cell still contains a formula, and the formula returns zero-length text when an error occurs. It looks blank, but functions such as ISBLANK do not treat it as a genuinely empty cell.

Should I use IFERROR or IFNA?
Use IFERROR when any error should produce a blank. Use IFNA when only #N/A is an expected missing-match result and other errors should stay visible for troubleshooting.

Can Find and Replace remove #VALUE! errors?
Find and Replace is not the recommended way to hide formula errors while retaining their calculations. Use an error-handling formula for that. If you intend to discard error cells and their formulas or values, Go To Special can select errors so you can clear them deliberately.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Errors & fixes

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.