Excel #VALUE! Error: How to Fix It

Excel’s #VALUE! error means a formula received a value in a form it cannot use, such as text where it expects a number.

Find the cell or argument causing the mismatch, then use the checks and fixes below to correct it without hiding other errors.

What does the #VALUE! error mean?

#VALUE! is Excel’s way of reporting a problem with a value used by a formula.

The formula may be syntactically valid, but one or more inputs have an incompatible type or shape.

Common examples include adding a number to text, using a date stored as text in a calculation, or giving a function ranges with mismatched dimensions.

The error can occur in Excel for Microsoft 365, Excel for the web, and desktop editions such as Excel 2024, 2021, and 2019.

The underlying cause is usually the same across versions, though some newer functions used to diagnose or fix it may not be available in older releases.

Check the formula and its inputs first

Select the cell showing #VALUE! and review the formula in the formula bar.

Identify each referenced cell, range, and function argument.
Check whether inputs that should be numbers or dates are actually stored as text.

A number aligned to the left by default, or a green error indicator, can be a clue, but formatting alone does not prove the cell’s underlying type.
Use Formulas > Evaluate Formula in desktop Excel to step through the calculation.

You can also click a formula reference while editing to see which cells it points to.
Test a suspicious input in an empty cell.

For example, =ISNUMBER(A2) returns TRUE if A2 contains a numeric value and FALSE if it contains text.

Convert numbers stored as text

A value that looks like 125 may still be text, especially when imported from a CSV, copied from a web page, or entered with a leading apostrophe.

A formula such as =A2+10 can then return #VALUE!.

If A2 contains a simple text number, convert it with =VALUE(A2).

For example, if A2 contains the text 125, the formula returns the numeric value 125.

In many cases, selecting the affected cells and choosing Convert to Number from Excel’s warning menu is a convenient one-time fix.

For imported values with extra spaces, try =VALUE(TRIM(A2)).

If the text includes nonbreaking spaces copied from a webpage, TRIM may not remove them.

In that case, replace the character with =VALUE(SUBSTITUTE(A2,CHAR(160),"")).

This removes nonbreaking spaces before conversion; check the original data first if spaces might be meaningful.

Decimal and thousands separators depend on regional settings.

A text value such as 1,25 may convert differently depending on the system’s number format.

When converting data from another locale, confirm the expected decimal and thousands separators rather than changing values blindly.

Fix dates and numbers that are really text

Dates imported as text may look like dates but fail in date arithmetic.

For instance, subtracting a text date in A2 from a real date in B2 can produce #VALUE!.

Check the cells with =ISNUMBER(A2); Excel stores valid dates as serial numbers, so this returns TRUE for a real date.

If A2 contains a date string Excel recognizes in your regional settings, =DATEVALUE(A2) converts it to a date serial.

Format the result cell as a date to display it clearly.

Date strings are locale-sensitive: 04/05/2026 could mean April 5 or May 4, so verify the source format before converting a column.

For predictable results, use a source date format that clearly separates year, month, and day, such as 2026-05-04, and confirm Excel interprets it as intended.

Do not rely on changing a cell’s display format alone; number formatting changes appearance, not text into a true date.

Correct formula arguments and range sizes

Some functions require compatible arguments.

For example, =FIND("x",A2,0) can return #VALUE! because the starting position must be 1 or greater.

If you want to find the first occurrence, omit the optional starting position: =FIND("x",A2).

Functions that compare or combine ranges may also fail when the ranges do not have compatible dimensions.

Check that corresponding ranges start and end on matching rows.

For example, when using SUMPRODUCT with A2:A10 and B2:B9, the ranges have different heights.

Change them to matching ranges, such as =SUMPRODUCT(A2:A9,B2:B9), if those are the intended records.

In Excel 365 and Excel 2021, dynamic array formulas can return arrays that spill into nearby cells.

A blocked spill usually produces #SPILL!, a different error, but formulas that combine arrays with incompatible shapes can produce other errors.

Check each array’s dimensions and the function’s requirements.

Excel 2019 and earlier do not support all dynamic array behavior or newer functions.

Handle errors without masking data problems

IFERROR can replace an error with a message or blank, but it does not fix the cause.

For example, =IFERROR(VALUE(A2),"Check input") displays a helpful prompt when conversion fails.

This is appropriate when you want a readable result and have a way to investigate the flagged inputs.

Avoid wrapping an entire worksheet in IFERROR just to make errors disappear.

That can conceal misspelled references, invalid data, or incorrect range sizes.

If a formula should handle only a specific condition, use a targeted check.

For example, =IF(A2="","",VALUE(A2)) leaves the result blank when A2 is blank, while still exposing invalid nonblank text as an error.

Common #VALUE! causes and fixes

Text used in arithmetic: Check with ISNUMBER; convert valid numeric text with VALUE.
Extra spaces or copied characters: Clean ordinary spaces with TRIM; replace nonbreaking spaces with SUBSTITUTE and CHAR(160).
Date stored as text: Verify the source format, then try DATEVALUE when the date string is unambiguous and recognized by your locale.
Invalid function argument: Review the function’s required argument types and allowed values, such as a positive starting position for FIND.
Mismatched range dimensions: Make paired ranges the same height and width when the function expects aligned arrays.

Once the input type, date interpretation, argument value, or range dimensions are corrected, recalculate the formula.

If #VALUE! remains, trace each referenced cell in turn; a nested formula may be passing the error onward from an earlier calculation.