Excel #VALUE! Error: Why It Happens and How to Fix It

You keep getting a #VALUE! error in Excel because a formula is receiving a value or argument it cannot use in that calculation—often text where a number is expected, an invalid date, or mismatched input ranges. Check the formula’s inputs first, then correct the underlying data or argument; wrapping the formula in an error-hiding function may conceal the problem without fixing it.

What the #VALUE! error means

#VALUE! is Excel’s way of saying that a formula has a value of the wrong type or shape for the operation it is trying to perform. It is different from #DIV/0!, which signals division by zero, and #REF!, which means a reference is invalid. The message is broad: it does not identify the troublesome cell for you, so you need to inspect the formula and the values it uses.

A cell can look numeric while actually containing text. For example, a value pasted from a website may include a word, an invisible character, or a character used as a thousands or decimal separator. A formula may also be valid in general but fail for one particular row because that row contains an unexpected entry. If an error appears after you fill a formula down, compare the first failing row with a nearby row that calculates correctly.

Common causes to check first

Start with the part of the formula that performs the operation, not with a general search for every possible Excel problem. These are frequent triggers:

Text in a calculation: A multiplication or subtraction may fail if a referenced cell contains words such as “pending” instead of a number.
Extra characters in data: Spaces, nonbreaking spaces, line breaks, currency symbols, and punctuation can prevent text from being interpreted as a number or date.
Wrong argument type: A function may expect a number, date, or text string but receive something else.
Invalid function input: Some functions return #VALUE! when an argument is outside the permitted range. For example, LEFT returns this error if its character-count argument is negative.
Incompatible array dimensions: SUMPRODUCT can return #VALUE! when its array arguments have different sizes.
Text or date not found or recognized: FIND returns #VALUE! if the searched text is absent, while date calculations can fail when a date is stored as unrecognized text.

Do not assume that every text value is a problem. Functions such as SUM generally ignore text in referenced cells, whereas a direct arithmetic operation may not. The result depends on the formula and on whether the text is in a referenced range or supplied as an argument.

Numbers stored as text

Imported data is a common source of trouble. A cell may display 12 but store the characters “12” as text, or it may contain “12 units,” which cannot be multiplied as a number. Formatting a text value as Number does not necessarily convert its contents; formatting changes how Excel displays suitable values, not what characters are stored in the cell.

For a concrete example, assume A2 contains the text 12 units and B2 contains the number 3. With those values in A2 and B2, the formula =A2*B2 returns #VALUE! because Excel cannot multiply the text “12 units” by 3. Removing the word and any unwanted characters, then converting the remaining numeric text, is the appropriate fix.

For a conversion example, assume A2 contains the text 12, with no extra characters. In B2, the formula =VALUE(A2) returns the number 12. VALUE is useful when text is a valid number in the workbook’s regional settings. If the source contains units, symbols, or unusual separators, clean those characters first; VALUE cannot reliably guess which parts of arbitrary text you intended to calculate.

To inspect a suspicious cell, select it and look at the formula bar, then try changing the cell format to General and checking whether the value behaves like a number. You can also test a cell with ISNUMBER. Assume A2 is the value being checked; =ISNUMBER(A2) returns FALSE if A2 is stored as text, including text that merely looks numeric. That test identifies the type, but it does not itself convert the contents.

Spaces, hidden characters, and imported data

Data copied from a web page, PDF, or another system may contain characters that are hard to see. TRIM removes ordinary leading and trailing spaces and reduces repeated ordinary spaces between words. CLEAN removes many nonprinting characters. Neither function removes every character that can appear in imported data: a nonbreaking space, for instance, is different from an ordinary space.

Assume A2 contains a number represented as text with nonbreaking spaces around it. In B2, =VALUE(SUBSTITUTE(A2,CHAR(160),"")) replaces character 160 with an empty string, then converts the result to a number. This is a targeted cleanup, not a universal repair: if A2 also contains a currency label or an unexpected decimal separator, you may need to handle that separately. Keep the original imported column until you have checked that the cleaned values are correct.

Regional settings matter. In one locale, a comma may mark decimals; in another, it may separate thousands. A string that looks like 1,25 may be a valid decimal in one setup and not in another. Avoid deleting punctuation automatically until you know what the source uses, or values can be converted to the wrong amount.

Function arguments and text-search errors

Sometimes the input cells are fine and the problem is an argument in the formula. Check commas, parentheses, quotation marks, and the order of the arguments against the function’s intended use. For example, FIND is case-sensitive and returns #VALUE! when the text it is asked to find does not occur in the text being searched. SEARCH is not case-sensitive, but it also returns #VALUE! when there is no match.

If not finding a match is an ordinary outcome rather than a fault, handle that outcome deliberately. Assume A2 contains a description and B2 contains the text to look for. The formula =IF(ISNUMBER(SEARCH(B2,A2)),"Found","Not found") returns Found when B2 occurs in A2 and Not found when it does not. This avoids treating a routine missing match as an unhandled search error. It still will not repair unrelated errors in the inputs.

Some functions reject values outside their allowed range. For instance, LEFT needs a nonnegative number of characters. If A2 contains text and B2 contains the requested character count, =LEFT(A2,B2) can return #VALUE! when B2 is negative. Check whether a referenced count, position, or date component can become invalid for some rows, especially when the formula is filled down or depends on user-entered values.

Dates that are actually text

Excel stores recognized dates as serial numbers and displays them using a date format. A date-looking entry imported as text may not participate in date arithmetic as expected. Recognition depends in part on regional date conventions: 04/05/2026 can be interpreted differently depending on whether the system expects month/day/year or day/month/year. A formula that works for dates entered directly may therefore fail on imported text.

Check a questionable date by changing its display format to General or testing whether Excel recognizes it as a number. If it remains text, convert it using a method that matches the source format, such as Text to Columns or a carefully chosen date-conversion formula. Do not assume that simply applying a date number format converts the underlying text. If a column mixes formats, standardize and verify the source values before calculating elapsed days or sorting chronologically.

Range size and array-related problems

When a formula uses multiple ranges together, confirm that corresponding ranges have the same number of rows and columns. SUMPRODUCT is a common example: its array arguments need compatible dimensions. If A2:A10 contains nine rows but B2:B9 contains eight, the formula =SUMPRODUCT(A2:A10,B2:B9) returns #VALUE! because the arrays do not line up. Check the first and last cell in each range, particularly after inserting or deleting rows.

Array formulas and dynamic-array functions can also behave differently depending on the expression and the Excel version. A #VALUE! message is not automatically a spill problem: a blocked spill range usually produces #SPILL!, a distinct error. When a formula uses arrays, inspect the size and shape of each input and check whether the function supports the way those arrays are being combined.

How to find the exact source of the error

Use a methodical check rather than replacing the formula immediately. In Excel for Microsoft 365 on Windows:

Select the cell showing #VALUE! and read the formula bar. Note every referenced cell, range, and function argument.
Open the Formulas tab and choose Evaluate Formula. Step through the calculation to see which expression first produces the error.
Inspect that expression’s inputs. Look for text, blanks, unexpected spaces, inconsistent dates, negative counts, or ranges with different dimensions.
Compare the failing row with a nearby row that works. A difference in one input often reveals the cause.
Correct or clean the source value, then confirm the result and test other rows that use the same formula.

You can also select a referenced cell and examine the formula bar to see its actual contents. On Mac, the Formulas tab includes formula-auditing tools, though the ribbon layout and keyboard shortcuts can differ. In Excel for the web, available ribbon options may depend on the interface and workbook; if you cannot use a particular auditing command, inspect the formula and inputs directly or open the workbook in the desktop app.

Fix the cause instead of hiding the message

The strongest fix is usually to make the input data consistent: convert numeric text, remove unwanted characters, correct a bad date, or align the formula’s ranges. If a text entry such as “pending” is meaningful, decide how the calculation should treat it rather than converting it blindly. A helper column can make cleanup visible and easier to audit, especially when the imported source must be retained unchanged.

IFERROR can be appropriate when an error is an expected outcome and you have chosen a clear substitute, but it catches many error types, not just #VALUE!. Assume A2 and B2 are intended to be numbers and C2 is the result cell. The formula =IFERROR(A2/B2,"Check inputs") displays Check inputs if the division produces an error. That message can improve a report, but it does not fix the bad value or explain whether the issue was text, division by zero, or another error. During troubleshooting, keep the underlying error visible until you know why it occurs.

IFERROR is not the same as correcting a formula. If a workbook feeds another calculation or a business report, silently replacing an error with zero can make the output look plausible while changing its meaning. Choose a fallback only when zero, blank, or a warning is genuinely the intended result, and document what that result means.

Frequently Asked Questions

Why does Excel show #VALUE! when my numbers look correct?
One or more cells may contain text, an invisible character, or punctuation that is not valid under the workbook’s regional settings. Check the formula bar and test the suspected input with ISNUMBER; then clean or convert it if appropriate.

Why did I start getting #VALUE! after copying data into Excel?
Copied or imported data can include nonbreaking spaces, line breaks, labels, or numbers stored as text. Compare a failing value with one entered directly in Excel, and clean a copy of the source data before using it in calculations.

Does formatting a cell as Number fix #VALUE!?
Not necessarily. Number formatting changes the display of values Excel already recognizes as numbers; it does not reliably turn arbitrary text into a numeric value. Convert valid numeric text or remove the characters preventing conversion.

Will IFERROR fix a #VALUE! error?
It can replace the displayed error with a chosen result, but it does not repair the underlying data or formula. Use it when an error is an expected condition and the replacement is meaningful, not as the first troubleshooting step.

Why does only one row in a copied formula return #VALUE!?
That row may contain an unusual input, such as text in a numeric column, an invalid date, or a negative argument. Compare its referenced cells with the same cells in a working row, and check whether relative references shifted as intended.

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.