Excel #VALUE! Error: Causes and Fixes

If Excel keeps showing #VALUE!, a formula is usually trying to use text, a date, or another incompatible value as a number—or one of its arguments is invalid. Check the formula’s referenced cells and argument types first, then correct the source data or adjust the formula; avoid simply hiding the error until you know what caused it.

What the #VALUE! error means

#VALUE! is Excel’s way of saying that a formula received a value of the wrong kind or could not use a value as requested. It is not the same as a misspelled function name or a missing cell reference. For example, multiplying a number by text that looks like a number can fail if Excel has stored that text as characters rather than as a numeric value.

The displayed cell may not contain the bad value itself. A formula can refer to several cells, combine functions, or use an intermediate result, so the source of the error may be somewhere upstream. If a cell shows #VALUE!, select it and read the formula bar. Then inspect each referenced cell, including cells used by other formulas in the chain.

There is no single fix for every #VALUE! error. A formula such as FIND can return this error when it cannot find requested text, while arithmetic formulas commonly produce it when text appears where a number is expected. The right repair depends on what the formula is meant to calculate.

Quick checks to find the cause

Start with the smallest investigation that can confirm or rule out a bad input. On Excel for Microsoft 365 for Windows, select the error cell and use the formula bar to review the expression. You can also select Formulas > Formula Auditing > Evaluate Formula to step through parts of a formula and see where the result changes into an error.

Check the formula’s inputs. Look at the cells it refers to, not just the result cell. Check for words, blanks, error values, and numbers stored as text.
Test a simple part. If the formula has several operations or functions, temporarily evaluate one input or expression at a time. Work on a copy if changing the formula could affect important work.
Check the expected type. Ask whether each argument should be a number, text string, date, or range. A function may accept one type but not another.
Look for invisible characters. Imported or copied data can contain leading spaces, nonbreaking spaces, or other characters that are hard to see in the cell.
Review the formula’s dimensions. In functions that combine ranges, check that the ranges have compatible shapes and sizes.

Changing a cell’s number format is not the same as converting text to a number. Formatting changes how a value is displayed; it does not necessarily change the underlying value. If the formula still fails after applying a number format, inspect and convert the source data instead.

Numbers stored as text

This is one of the most common causes when an arithmetic formula returns #VALUE!. A cell may display 1250 but contain text, perhaps because it was imported from a website, a text file, or another system. A leading apostrophe can also make an entry text. Excel may show a green warning indicator; when it does, selecting the cell’s warning button may offer Convert to Number.

For a direct conversion, suppose A2 contains the text 1250. The formula =VALUE(A2) returns the number 1250, provided the text is a number Excel recognizes under the workbook’s regional settings. This is useful when conversion should happen in a formula; converting the source column may be better when you need to repair many imported values.

Spaces and hidden characters can prevent conversion. Suppose A2 contains a number surrounded by ordinary spaces or a nonbreaking space, a character often introduced by web pages. The formula =VALUE(TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," ")))) removes common control characters and replaces nonbreaking spaces before converting the result. If A2 contains text representing 1,250 in a regional format Excel recognizes, the result is the number 1250. The comma and decimal conventions depend on the computer’s regional settings, so verify the result when data uses a different locale.

For a whole column of simple imported values, select the cells and use Data > Text to Columns. In the wizard, choose the appropriate options and finish the conversion; for a straightforward conversion, the default settings may be enough. Check a sample before applying this to a large or mixed-format column, because the wizard can also split data depending on the choices made.

Text, spaces, and function arguments

Some formulas are supposed to work with text, but still return #VALUE! when an argument is invalid. For example, FIND returns the position of one text string inside another, and it returns #VALUE! when the text being searched for is absent. If A2 contains North, =FIND("th",A2) returns 4. If A2 contains South, the same search does not find the exact lowercase sequence th, so FIND returns #VALUE!; FIND is case-sensitive.

If not finding a match is an expected possibility, decide what result makes sense for the task. You could return a clear message, leave the result blank, or use a different search approach. Do not replace every error with zero without considering what zero would mean: a missing match and a genuine numeric zero are different results.

Other text functions can fail when an argument is outside its valid range. For example, LEFT expects a nonnegative number of characters. If a formula calculates that number from another cell, check the calculated argument as well as the text. A negative value may be the real cause even when the source text looks normal.

In addition, pasted data may contain characters that TRIM does not remove. TRIM is designed for ordinary spaces; it does not remove every invisible Unicode character. When cleaning a value does not fix the error, inspect the data source or test for specific characters rather than repeatedly applying formatting.

Dates and regional settings

Excel stores recognized dates as serial numbers, but imported dates may arrive as text. A cell displaying something date-like is not proof that Excel recognizes it as a date. This can cause errors in calculations that expect dates or numbers, especially when a file was created using a different date or regional convention.

Suppose A2 contains text in a date format that the current regional settings recognize, such as 3/14/2025 on a system using month/day/year conventions. The formula =DATEVALUE(A2) converts that text to an Excel date serial; format the result cell as a date to display it as a calendar date. If the source uses day/month/year while the computer expects month/day/year, conversion may fail or interpret the value differently. Confirm the intended order rather than assuming the displayed date is correct.

For dates imported in a consistent but unfamiliar layout, converting the source column deliberately is safer than relying on an automatic guess. If the original data includes separate year, month, and day fields, consider building a date from those components only after verifying which field represents each part. Preserve the original column until the converted results have been checked.

Range and calculation mismatches

Some formulas operate on multiple ranges at once. Those ranges may need to have matching dimensions. For example, with values in A2:A4 and corresponding quantities in B2:B4, =SUMPRODUCT(A2:A4,B2:B4) multiplies the corresponding entries and adds the products. If one range is accidentally extended to B5, the ranges no longer match in size and the formula can return #VALUE!.

This is especially easy to miss when ranges are edited, copied, or assembled from different sheets. Check the first and last cell of every range, and confirm that each row or column represents the same records. Also inspect any cells inside the ranges for existing errors or unexpected text. A formula can be syntactically valid and still fail because its inputs do not fit the calculation.

In Microsoft 365, dynamic-array formulas can also return errors when their inputs or calculated arrays are incompatible. If an error appears after changing a formula that spills results, review the dimensions and types of the arrays being combined. Do not assume every #VALUE! is caused by a single text entry; the function’s requirements matter.

Repair the data or change the formula?

Choose a fix that reflects the meaning of the data. If a value represents a quantity but was imported as text, converting it to a number addresses the underlying problem. If the cell contains a label such as Pending, coercing it into a number is not appropriate; the formula should account for that status or the data should be organized so numeric and descriptive values are handled separately.

When a formula should tolerate a known error condition, use a targeted test or error-handling approach and make the fallback meaningful. For instance, if a missing text match is normal, handle that case specifically rather than suppressing every possible error. Broad error handling can make a workbook look clean while hiding typos, broken references, or incomplete data.

Before changing formulas across a workbook, save a separate copy. Repair a small sample first, compare its results with the source, and then apply the change to the full range. This is particularly important for imported dates and financial or inventory data, where a conversion that looks plausible can still change the meaning of a value.

Why not just hide the error?

Error-handling formulas can be useful, but they should not replace diagnosis. A formula such as IFERROR can return a chosen fallback when its calculation errors. That may be appropriate in a finished report when the fallback is explicitly intended, but it can also conceal the fact that a formula is using text instead of a number or searching for the wrong string.

Before adding error handling, determine which errors are expected and what each one should mean. A blank result may be clearer than zero for an unavailable value; a label such as Check input may be safer when the source needs correction. Keep the underlying data auditable so that a clean-looking summary does not disguise missing or invalid records.

Also distinguish #VALUE! from other Excel errors. #N/A commonly indicates a value is unavailable to a lookup; #DIV/0! indicates division by zero; and #REF! indicates an invalid reference. The message is a clue, not a complete diagnosis, but identifying the exact error helps narrow the investigation.

Frequently Asked Questions

Why does Excel show #VALUE! when I multiply cells?
One of the referenced cells may contain text, an error, or a value Excel cannot interpret as a number. Check the actual contents of both inputs; a number displayed as text can look identical to a numeric value.

Why does a cell show #VALUE! even though it looks like a number?
The cell may contain text, leading or trailing spaces, or an invisible character. Try converting a sample with VALUE, check for Excel’s number-stored-as-text warning, and verify regional separators such as commas and decimal points.

Can changing the cell format fix #VALUE!?
Usually not by itself. Number formatting changes the display, not necessarily the stored value. Convert text to a number or correct the formula input, then apply the desired format.

Why does FIND return #VALUE!?
FIND returns #VALUE! when it cannot locate the requested text, and it is case-sensitive. Check the spelling and capitalization of both strings, and decide how the formula should behave when there is no match.

Should I use IFERROR to remove #VALUE!?
Only when you have decided what the error means and the fallback is appropriate. IFERROR can hide genuine data problems, so first identify the source and use a specific, understandable fallback for expected cases.

Why does DATEVALUE fail on an imported date?
The text may use a date order or separator that Excel does not recognize under the current regional settings, or it may contain extra characters. Confirm the intended date order and clean or convert the source before relying on the result.

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.