Excel #VALUE! Error: How to Test for It in a Cell

To test whether cell A2 specifically contains Excel’s #VALUE! error, use =IFERROR(ERROR.TYPE(A2)=3,FALSE). It returns TRUE for #VALUE!, FALSE for other errors or ordinary values; use ISERROR instead if you want to detect any error.

What the test tells you

Excel displays #VALUE! when a formula receives an argument or value of the wrong kind for an operation. For example, a formula may try to add text that is not a number, or a function may receive text where it expects a numeric position. The error is a signal that the calculation could not be completed as written; it is not the same as a blank cell or a zero result.

ERROR.TYPE reports a number for recognized Excel errors. The code for #VALUE! is 3. Comparing that result with 3 identifies this particular error rather than treating every error alike. The outer IFERROR is important: when A2 contains an ordinary value, ERROR.TYPE(A2) itself returns #N/A, so IFERROR changes that outcome to FALSE. The same protection makes other errors return FALSE.

This check reports what is in the cell; it does not repair the formula that produced the error. It is useful in a status column, an audit, or a larger formula that needs to respond differently to a value error than to other problems.

How it differs from ISERROR and ISERR

Excel includes several error-checking functions, and their names are easy to confuse. Choose one based on whether you need to find a specific error or any error:

ISERROR returns TRUE for any Excel error value, including #N/A. It is the straightforward choice for a general error check.
ISERR returns TRUE for errors other than #N/A. It is not a test for #VALUE! alone.
ERROR.TYPE distinguishes recognized error types by number. Comparing its result with 3 isolates #VALUE!; use IFERROR around that comparison to handle non-error cells cleanly.
IFERROR replaces any error in its first argument with a fallback result. It does not identify which error occurred.
IFNA handles #N/A specifically, rather than #VALUE!.

For example, with a value or formula result in A2, the general test is =ISERROR(A2). It answers “is this an error?” but cannot tell you whether the error is specifically #VALUE!. Conversely, testing only for #VALUE! means that a different error in A2 will not be classified as a match.

Use the test in a worksheet

Enter the test in an empty cell beside the cell you want to inspect. With the value or formula result to check in A2, enter =IFERROR(ERROR.TYPE(A2)=3,FALSE) in B2. A result of TRUE means A2 contains #VALUE!; FALSE means it contains another error or no error. If you have more rows, copy the formula down so each row checks the cell beside it.

For a readable status label rather than TRUE or FALSE, you can use a nested test. With the item being inspected in A2, enter =IFERROR(IF(ERROR.TYPE(A2)=3,"#VALUE!","Other error"),"No error"). It displays “#VALUE!” for that error, “Other error” for a different recognized error, and “No error” when A2 is not an error. The wording is a label produced by the formula; it does not change the contents of A2.

For a large list, place the test beside the data and filter the result column for TRUE or for the “#VALUE!” label. This can help locate affected rows without changing the original formulas. If you only want to flag errors visually, a separate status column is often easier to review than replacing the original result with a message.

Common reasons Excel returns #VALUE!

The test can locate the error, but tracing its source requires looking at the formula and its inputs. Common causes include:

Text used in arithmetic. A cell may look numeric but contain text such as “12 units,” or contain a word where the formula expects a number.
Unexpected spaces or characters. Imported data can include leading, trailing, or nonprinting characters that interfere with comparisons or calculations.
Arguments in the wrong form. A function may expect a number, date, or position but receive text or an incompatible value.
Ranges of incompatible sizes. Some formulas that combine arrays or ranges require matching dimensions; mismatched inputs can cause errors depending on the formula and Excel version.
A problem passed from another formula. If a referenced cell already contains an error, a later calculation may carry that error forward.

Start by selecting the cell showing #VALUE! and reading its formula in the formula bar. Check each referenced cell, especially values brought in from copied data or external sources. Excel’s error indicator may offer an error-checking menu when you select the cell, but its suggestions are prompts to investigate, not a substitute for checking whether the formula matches your intended calculation.

Fix text that should be numeric

If a cell contains text that represents a number, convert it only after confirming that the text is genuinely numeric. With text such as 125 stored in A2, =VALUE(A2) returns the number 125. The result can then be used in arithmetic. The conversion depends on Excel recognizing the text as a number under the workbook’s regional settings; text with units or other extra characters will not automatically become a clean number.

For ordinary extra spaces around text, a helper formula can remove leading and trailing spaces. With the text in A2, =TRIM(A2) returns a cleaned text value. It does not convert text to a number, and it does not remove every possible nonprinting character. When data comes from a web page or another system, inspect suspicious values rather than assuming that TRIM alone will solve the problem.

Another useful check is to compare the cell’s displayed appearance with its actual contents. A value that looks like a date or number may be stored as text. Changing the number format affects display, but it does not necessarily convert text into a numeric value. Choose a conversion method that preserves the intended meaning, especially for dates, identifiers, and codes where leading zeros may matter.

When to use IFERROR—and when not to

It can be tempting to wrap a formula in IFERROR to make the visible error disappear. That is appropriate when a fallback is meaningful, such as showing a blank when a calculation is intentionally unavailable. It can also conceal a broken reference, invalid input, or an unexpected change in source data. Use a specific test when different errors should lead to different actions.

For example, with the calculation in A2, =IFERROR(A2,"Check formula") replaces every error in A2 with the same message. It does not tell you whether the original problem was #VALUE!, #REF!, or another error. During troubleshooting, leave the original error visible or use the specific ERROR.TYPE test in a separate cell. Once the cause is understood, decide whether a fallback is appropriate.

A useful rule is to separate detection from correction: first identify the error type, then inspect the calculation and inputs, and only then decide how the workbook should behave. A formula that returns a tidy label is not necessarily a correct calculation.

Common mistakes when testing for the error

One frequent mistake is assuming ISERR means “is an error of any kind.” It excludes #N/A, and it still does not identify #VALUE! specifically. Another is using ISERROR and interpreting TRUE as proof that the problem is a value error. That function reports only that some error is present.

Also avoid comparing the cell to the text string “#VALUE!” as though the displayed error were ordinary text. An actual Excel error value is different from text that merely looks like an error message. ERROR.TYPE is designed to inspect error values; a text comparison can miss the real error and may produce misleading results.

Finally, remember that the test checks the result currently held by the referenced cell. If A2 contains a formula, the test checks that formula’s current result, not the formula text itself. If the source data changes or the workbook recalculates, the test result can change too.

Frequently Asked Questions

Can I test only for #VALUE! and ignore other errors?

Yes. With the value to inspect in A2, use =IFERROR(ERROR.TYPE(A2)=3,FALSE). It returns TRUE only for #VALUE!; other errors and non-error values return FALSE.

Does ISERROR detect #VALUE!?

Yes. ISERROR returns TRUE for #VALUE!, but also for other Excel errors. Use it when any error matters, and use the ERROR.TYPE comparison when you need to distinguish #VALUE!.

Why does ERROR.TYPE return #N/A for a normal cell?

ERROR.TYPE is intended to report an error code. If its argument is not an error, it returns #N/A. Wrapping the comparison in IFERROR, as in the main test, converts that result to FALSE.

Will IFERROR fix the cause of #VALUE!?

No. IFERROR substitutes a result when an error occurs; it does not correct the inputs or formula that caused the error. Investigate the underlying calculation before deciding whether to use a fallback.

Can the test check a formula result in another cell?

Yes. Reference the cell containing the result, such as A2. Excel evaluates the cell’s current value, so the test works whether A2 contains a typed value or a formula that currently returns an error.

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.