Excel #VALUE! Error: How to Find and Fix the Cause

Excel’s #VALUE! error means a formula received a value or argument in a form it cannot use. Find the cell or function argument causing the mismatch, then correct the input—often by converting text to a number or date, fixing an argument, or cleaning unwanted characters. Avoid simply hiding the error until you know why it occurs.

What the #VALUE! error means

Excel formulas work with different kinds of data, including numbers, text, dates, logical values, and references to cells or ranges. A formula returns #VALUE! when an operation or function cannot use the supplied value as expected. For example, adding a number to a word is not a meaningful arithmetic operation, so Excel reports an error instead of calculating a result.

The error identifies a problem in the calculation, but it does not by itself explain which input is wrong. It can originate in the formula cell, in a cell referenced by that formula, or deeper in a chain of formulas. A cell displaying a date or number may also contain text that merely looks like a date or number, which is why inspecting the underlying value matters.

Not every visible error points to the same issue. #VALUE! commonly indicates an unsuitable value or argument. Other errors, such as #DIV/0! or #N/A, have different meanings and often need different fixes. Treat the exact error as a clue rather than replacing all errors with a blank result immediately.

Quick checks to locate the cause

Start with the formula that displays the error, then work backward through its references. In Microsoft 365 for Windows, select the error cell and inspect the formula bar. Check each referenced cell for unexpected text, blanks, errors, or values that look numeric but are stored as text.

Read the formula from the inside out, especially if it contains several nested functions.
Check whether the function’s arguments are the types it expects. A function may require a number where the formula supplies text.
Look for extra spaces, line breaks, or nonbreaking spaces copied from a website or another system.
Test whether a date is a genuine Excel date value or a text string that only resembles one.
Check that ranges used together have compatible shapes and that referenced cells do not already contain errors.

For a longer formula, use Formulas > Formula Auditing > Evaluate Formula to step through its calculation. The Formula Auditing group also offers Trace Precedents, which can help show which cells feed the selected formula. These tools are useful for isolating where the result first becomes an error; they do not automatically repair the underlying data.

Text where a calculation expects a number

A frequent cause is a text value in a calculation that expects numbers. A cell might contain a word such as pending, a number imported as text, or a value with a stray character. The way Excel treats text can depend on the operation: direct arithmetic with an ordinary word generally produces #VALUE!, while some functions, such as SUM, ignore text in referenced cells. That difference can make a formula appear to work while leaving some data out.

With the text word twelve in A2 and the number 3 in B2, this formula returns #VALUE! because Excel cannot add the word to 3:

Assuming A2 contains the text twelve and B2 contains the number 3:

=A2+B2

If a cell contains text that represents a valid number, converting it may solve the problem. For example, VALUE converts numeric text into a number when Excel can interpret its format. With the text 12 in A2 and the number 3 in B2, this formula returns 15:

Assuming A2 contains text that Excel recognizes as the number 12 and B2 contains the number 3:

=VALUE(A2)+B2

Conversion is not a cure for every input. VALUE cannot turn arbitrary words into numbers, and number formats may be interpreted differently depending on regional settings. If imported data includes currency symbols, unusual separators, or labels mixed into a column, inspect and standardize those inputs rather than assuming a conversion will succeed. In the worksheet, you can also select affected cells and use the warning icon, when Excel offers it, to convert numbers stored as text.

Dates and times stored as text

Excel stores valid dates and times as numeric values, then displays them using a date or time format. A date copied from an email, CSV file, or web page may instead be text. It can look identical to a real date but fail when a formula tries to add days, subtract dates, or extract date parts.

With the text string not a date in A2 and the number 7 in B2, adding them produces #VALUE!. The example illustrates the type mismatch; a real worksheet may contain a plausible-looking date string instead.

Assuming A2 contains the text not a date and B2 contains the number 7:

=A2+B2

Check a suspicious date by changing the cell’s number format or comparing it with a known valid date. Formatting alone changes how a value is displayed; it does not reliably convert text into a date. If the text follows a consistent date pattern, Excel’s date-conversion tools or a formula designed for that pattern may be appropriate. Confirm the intended day, month, and year order first, especially when data comes from a region that uses a different date convention.

Function arguments, text functions, and ranges

A formula can contain valid-looking references but still pass the wrong kind of argument to a function. Text functions are a common example: functions that extract characters may require a number for the character position or length. If a referenced cell contains words where the function expects a numeric position, the result may be #VALUE!.

With the text ABC123 in A2, this formula returns #VALUE! because the starting position supplied to MID is zero; MID requires a starting position of at least 1:

Assuming A2 contains ABC123:

=MID(A2,0,3)

In a formula with several functions, inspect each function’s arguments against its purpose and required input. A function that searches for text may return #VALUE! when the searched text is absent; in that case the result can be an expected signal rather than evidence that the workbook is damaged. Decide how the formula should handle that situation—for example, show a message or take another calculation path.

Also check ranges used in array calculations. Formulas that combine arrays or corresponding ranges may require compatible dimensions. A range with a different number of rows or columns can prevent a calculation from producing a single consistent result. In Microsoft 365, dynamic-array formulas can return results across neighboring cells; if the formula itself is valid but the intended output cannot be placed, Excel may show a different error, so distinguish that situation from #VALUE!.

Clean characters copied from other sources

Imported text may contain leading or trailing spaces, control characters, or nonbreaking spaces. These can be difficult to spot on screen and can stop a value from being recognized as a number or make a text comparison fail. TRIM removes ordinary extra spaces, while CLEAN removes certain nonprinting characters. A nonbreaking space, often copied from a web page, may need to be replaced explicitly before those cleanup steps.

With imported text in A2, this formula replaces nonbreaking spaces with ordinary spaces, removes nonprinting characters, and trims extra ordinary spaces:

Assuming A2 contains the imported text to clean:

=TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," ")))

The result is still text. If it represents a number and a later calculation needs a numeric value, wrap the cleanup steps in VALUE, provided the cleaned result is a valid number in the workbook’s regional format. Keep the original imported column until you have checked the cleaned results; a cleanup formula can remove characters but cannot determine whether an ambiguous value was intended to mean a date, an identifier, or something else.

A reliable troubleshooting workflow

Use a repeatable process instead of changing several cells at once. This makes it easier to identify the actual cause and reduces the risk of altering correct data.

Select the error cell and read the complete formula in the formula bar.
Identify each referenced cell and check whether its contents match the type the calculation needs.
Use Evaluate Formula for a complex formula, or temporarily test a smaller part of the calculation in an empty cell.
Correct the underlying input or formula argument, then check whether the result makes sense—not merely whether the error disappeared.
Test the fix on other rows, including blanks and unusual inputs, before filling the formula through a larger dataset.

For a formula copied down a column, inspect whether its references shift as intended. Relative references change when copied; absolute references marked with dollar signs remain fixed. A misplaced reference can point to a text label or an unrelated cell in later rows, creating errors that were absent in the first row.

If the formula is part of a workbook used by other people, add a note or validation rule where appropriate so the source of the expected input is clear. For instance, a column intended for numeric quantities should not also contain status words unless the formulas are specifically designed to handle them.

When IFERROR is appropriate—and when it is not

IFERROR can display a friendlier result when a formula produces an error. It is useful when an error is an expected possibility, such as a lookup that may not find a match, and when the alternative message helps the user understand what happened. But using it to replace every error with a blank can conceal bad inputs and make incomplete calculations look finished.

With a text value in A2 and a number in B2, this formula displays the message Check inputs instead of showing an error. It does not fix the incompatible values, so use a message that prompts someone to investigate:

Assuming A2 and B2 are the inputs being added:

=IFERROR(A2+B2,"Check inputs")

Prefer a targeted fix when you know the cause, such as converting a valid numeric text value or correcting an invalid function argument. Use IFERROR only when the fallback is a sensible part of the worksheet’s design, and make sure it does not hide unrelated errors that should be corrected.

Frequently Asked Questions

Why does Excel show #VALUE! when I add cells?
At least one input may contain text or another value that cannot be used in that arithmetic operation. Inspect the referenced cells, including values that look like numbers but were imported as text, and check for words or hidden characters.

Why does my date formula return #VALUE!?
A date may be stored as text rather than as an Excel date value, or the text may not match the date pattern Excel expects. Confirm the intended date order and convert the source data appropriately; changing the cell’s display format alone may not convert it.

Can extra spaces cause #VALUE!?
They can contribute to the problem, especially when a formula expects a number or when text is being compared. Ordinary spaces and nonbreaking spaces are not always the same character, so imported data may need both trimming and explicit replacement.

Does SUM ignore text cells?
SUM generally ignores text in cells referenced as a range, which differs from direct arithmetic between a text value and a number. That behavior can cause a total to omit data without displaying #VALUE!, so verify that the cells included in a sum are genuinely numeric.

Should I use IFERROR to remove the error?
Only when the alternative result is meaningful and the error is an expected possibility. If the error points to invalid source data or a broken calculation, fix that cause rather than hiding it with a blank or generic value.

Excel for Mac and the web

The underlying meaning of #VALUE! and the core formula checks are the same in Excel for Microsoft 365 on Windows, Mac, and the web. Ribbon layout and some auditing controls can differ by platform or update, so if a named command is not visible, look for the equivalent formula-auditing option on the Formulas tab. The examples use standard Excel formulas with comma separators; some regional configurations use different list separators in formulas.

When collaborating, remember that a workbook can contain values imported or edited on different devices and from different regional settings. A formula that works with clean numeric inputs may expose text or date-format issues after another data source is pasted in. Checking the source values and the intended data type is more dependable than relying on the appearance of the cell or suppressing the 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.