Excel #VALUE! Error: Find and Fix the Common Causes

To eliminate Excel’s #VALUE! error, check the cells used by the formula, correct any text or spaces where numbers are expected, and make sure the formula’s operators and ranges fit the data. Start by selecting the cell with the error and reading its formula in the formula bar; then trace each referenced cell and fix the cause rather than simply hiding the error. You’ll need the workbook and a basic understanding of what the formula should calculate.

What You’ll Need

The Excel workbook containing the error, preferably a saved copy if you plan to make many changes.
The formula that returns #VALUE! and the source cells it refers to. Click the error cell to see the formula bar.
A clear idea of the intended inputs: for example, which cells should contain numbers, dates, or text.
Microsoft Excel for Microsoft 365 on Windows for the paths below. Most formula checks also work on Mac and Excel for the web, though ribbon placement can differ.

Step-by-Step

Select the error and read its formula. Click the cell displaying #VALUE! and inspect the formula bar. Note every cell reference, range, operator, and function. For example, a formula that multiplies a price by a quantity requires compatible values in both referenced cells. Do not replace the formula yet: first identify what input or operation is failing. If the formula is long, use the formula bar to select parts of it, or click a referenced cell while editing to see the corresponding range highlighted.
Check the referenced cells one at a time. Look for words mixed into a number, a date entered as plain text, an unexpected error, or a blank that the formula does not handle as intended. A cell may look numeric while actually containing a label such as “12 items.” That is text, not a number Excel can multiply. Also check whether a formula points to the correct row or column; a reference that shifted during copying can make a sound formula use the wrong kind of data.
Confirm that text is not being used in arithmetic. With a quantity in A2 containing the text 12 items and a unit price in B2 containing 3, the intended calculation cannot multiply the full text string by 3. If A2 contains the clean number 12 and B2 contains 3, the formula is valid and returns 36: =A2*B2. Remove labels from the input cell and put them in a separate column, or adjust the formula to refer to a cell that contains only the number. Basic arithmetic may coerce a number stored as text in some circumstances, so test the actual source values rather than assuming every text-formatted number causes this error.
Convert numeric text into numbers where appropriate. If A2 contains text that consists of a number, such as “ 125 ”, and has no extra words, this formula trims ordinary surrounding spaces and converts the result to a number: =VALUE(TRIM(A2)). The result is 125. If conversion fails, inspect the text for units, currency symbols, nonbreaking spaces, or a decimal and thousands separator that does not match your regional settings. In Microsoft 365 on Windows, you can also select affected cells and use the warning icon’s Convert to Number option when Excel offers it. Check the converted result before using it in other calculations.
Remove hidden and nonbreaking spaces if values came from another source. Data copied from a website or exported system can contain character 160, a nonbreaking space that ordinary TRIM may not remove. If A2 contains a number stored as text with that character, this formula replaces it with a regular space, removes nonprinting characters, trims extra spaces, and converts the cleaned value: =VALUE(TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," ")))). For a source value equivalent to 125 with a nonbreaking space before it, the result is 125. This will still return an error if the remaining content is not a valid number; inspect the cleaned text rather than forcing a conversion.
Use the correct operator for the task. The plus sign adds numbers; it does not join text. If A2 contains North and B2 contains Region, adding the cells can return #VALUE!. To combine text, use the ampersand: =A2&" "&B2. With those sample values, the result is North Region. Conversely, if you intend to add values, make sure they are numeric rather than changing the formula to concatenate them. Check for a mistaken plus sign, multiplication sign, or missing separator in copied formulas.
Check function inputs and search results. Some functions return #VALUE! when an input has the wrong type or a required match is missing. For instance, FIND returns #VALUE! if its search text is not present. If A2 contains the text “Invoice 27” and B2 contains the text “PO”, this formula returns #VALUE! because PO is absent: =FIND(B2,A2). Verify the spelling, spaces, and intended search direction. If a missing match is an expected possibility, decide what result should appear in that case instead of treating it as a calculation success.
Make ranges consistent in array calculations. Functions that compare or combine arrays may return #VALUE! if their input ranges have incompatible dimensions. For example, a calculation using A2:A10 alongside B2:B9 has nine cells in one range and eight in the other. Select the formula and compare each range’s first and last row, column, and orientation. Correct the references so the ranges represent matching records. Do not extend a range blindly if the extra cells contain headers, totals, or unrelated data.
Check dates and regional number formats. A date that looks correct may be text, especially after importing a CSV or pasting data from another system. If a date calculation returns #VALUE!, confirm that each input is a real Excel date and not a string containing an unrecognized format. Re-enter one date in the expected regional format or use a controlled conversion process, then verify the result. Similarly, a value such as 1,25 may be read differently depending on the workbook’s decimal separator settings. Avoid replacing separators across an entire column until you know whether commas or periods are decimals or thousands markers.
Test the formula in smaller pieces. For a long expression, temporarily evaluate one operation at a time in an unused cell or use the formula bar to select a portion and press F9. Press Esc after inspecting a selected portion so you do not replace the formula with its displayed result. You can also select the error cell and choose Formulas > Evaluate Formula on the Windows ribbon to step through the calculation. On Mac, use the Formulas tab’s Evaluate Formula command if it is available in your Excel build; ribbon layout can vary. For Excel for the web, inspect the formula and referenced cells directly if that desktop auditing command is unavailable.
Retest, then decide how errors should display. After correcting the source or formula, confirm that the cell now returns the expected value and that nearby rows still calculate correctly. If a missing or invalid input is a normal situation, you may deliberately show a message with IFERROR. With a calculation in A2 and B2, this formula displays a prompt instead of an error: =IFERROR(A2*B2,"Check inputs"). If A2 is 12 and B2 is 3, it returns 36; if the multiplication produces an error, it displays Check inputs. Use this only when that message is useful: it can conceal an underlying problem if applied without checking the inputs.

Common Mistakes

Wrapping everything in IFERROR immediately. A blank or friendly message can make a sheet look tidy, but it may hide a broken reference, mismatched range, or bad import. Find and correct the cause first; use error handling only for conditions the workbook is designed to encounter.
Assuming that changing cell formatting converts the value. Applying Number format changes how a value is displayed, not necessarily the underlying text. Use a real conversion, re-enter the value, or use Convert to Number when available, then verify it.
Deleting spaces without checking what they mean. Leading or trailing spaces can be unwanted, but spaces within a product code or name may be meaningful. Clean a copy or a helper column first and compare the result with the source.
Changing the formula when the input is wrong. If a cell contains “12 items,” stripping characters from it may be less reliable than storing 12 in a dedicated numeric cell and “items” separately.
Copying a fix into every row without checking references. Relative references shift as formulas are copied. Confirm that each row refers to its own inputs and that any fixed reference uses the appropriate dollar signs.
Ignoring separators and locale. Decimal commas, thousands separators, and date formats differ by regional settings. A conversion that works on one computer may fail on another if the source text uses a different convention.

Tips

Keep raw imported data unchanged and clean it in a separate column. That makes it easier to compare the original entry with the corrected value and repeat the process next time.
Use consistent data types in each column: numbers in number columns, dates in date columns, and labels in text columns. Put units in headings or separate fields rather than appending them to numeric values.
Use Formulas > Trace Precedents on Windows to see which cells feed the selected formula. On Mac, look for the same command on the Formulas tab; availability and placement can vary by version.
After correcting one cell, check several neighboring rows and totals. A fix that resolves one error can still produce incorrect results if the source data is inconsistent elsewhere.
If the workbook is shared, note the expected input format in a heading or cell comment, such as “Enter a number only” or “Use dates in YYYY-MM-DD format.” Clear instructions prevent repeat errors.

Troubleshooting

The error remains after converting a value. Check for text before or after the digits, nonbreaking spaces, currency symbols, or a separator that does not match your regional settings. Test the cleaned value in a spare cell and compare it with the original. If the source includes units or notes, do not convert the whole string as though it were a number.

Only one row shows #VALUE!. Compare that row with a working row. Look for an extra space, a text entry among numbers, a different date format, or a copied formula referencing the wrong cell. Use Formulas > Evaluate Formula to isolate the operation that first produces the error.

The formula works on one computer but not another. Check regional settings, imported text formats, and whether the other computer is using Excel for the web, Mac, or a different version. If the formula itself uses a newer function, confirm that the receiving Excel version supports it. For a basic #VALUE! issue, inspect the actual cell contents and formula references on both systems.

IFERROR hides the problem but the total is wrong. Remove or temporarily bypass the error handler, then inspect the original calculation and its inputs. A message or blank is not a correction if the workbook needs a valid numeric result. Restore error handling only after deciding what should happen when an input is genuinely missing or invalid.

The error appears after pasting or importing data. Keep the source intact, check whether the import added spaces or turned dates and numbers into text, and clean a separate copy. If you use Text to Columns, confirm the delimiter and data format choices before finishing so Excel does not split or reinterpret fields incorrectly.

Frequently Asked Questions

What does #VALUE! mean in Excel?
It means a formula received a value or input it cannot use for the requested operation. Common causes include text in arithmetic, invalid function arguments, and incompatible array ranges.

Is #VALUE! the same as #N/A?
No. #VALUE! usually points to an unsuitable value or operation. #N/A commonly indicates that a requested value is unavailable, such as a lookup with no match. The right fix depends on the error and the formula’s purpose.

Can I remove #VALUE! without changing the formula?
You can hide it with IFERROR, but that does not repair the calculation. Use that approach only when an error is an expected condition and the displayed replacement is appropriate; otherwise correct the input or formula.

Why does a number look right but still cause #VALUE!?
It may be stored as text, contain an invisible character, or include a symbol or unit. Check the formula bar and test a cleaned copy. Changing the cell’s number format alone does not convert text into a numeric value.

Why does my formula return #VALUE! only after I copy it?
Relative cell references move when a formula is filled down or across. Check whether the copied formula now points to a heading, label, or mismatched range. Add absolute references where a particular cell must stay fixed.

Can blank cells cause #VALUE!?
A truly empty cell often behaves like zero in basic arithmetic, but a formula that returns an empty text string is not the same as a blank cell. Check whether a referenced cell is empty, contains a formula returning an empty string, or contains spaces, then decide how the calculation should treat that case.

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.