Fixes / Excel · Checked

Excel #VALUE! error: what it means and how to fix it

Short answer

#VALUE! means a formula got the wrong kind of value, usually text where it expected a number or a date. The most common cause is a cell that looks empty or numeric but holds a space or text. Type =ISTEXT(A1) to test a cell you suspect, clear or fix that cell, or use =SUM(A1:C1) instead of =A1+B1+C1, because SUM ignores text.

What this message means

#VALUE! is Excel's way of saying "there's something wrong with the way your formula is typed, or with the cells it refers to." In practice it almost always means an argument has the wrong data type: a math operator (+, -, *, /) was given text, a date function was given a date stored as text, or a function was given ranges of mismatched sizes. The error then spreads to every formula that refers to that cell, so the cell showing #VALUE! is often not the one causing it. Excel for Windows, Mac and the web all show the same error for the same reasons.

Common causes

CauseHow to tell
A referenced cell contains text, or a space that makes it look emptyThe formula uses + - * or /, and one of the cells it points to looks blank or shows a number aligned to the left. =ISTEXT(cell) returns TRUE and =LEN(cell) returns 1 or more for a cell that looks empty.
Numbers or dates were imported as text (from a CSV, web page, PDF or another system)Values sit on the left side of the cell, a small green triangle appears in the top-left corner, or dates don't change when you switch the cell format to a different date style. DATEVALUE, DAYS, EDATE or date subtraction return #VALUE!.
Non-breaking spaces or other invisible characters copied from a website or emailTRIM and a normal space Find & Replace do not fix it. =CODE(RIGHT(A1)) returns 160 (non-breaking space) or a number below 32 (a control character).
SUMIF, SUMIFS, COUNTIF, COUNTIFS or COUNTBLANK points to another workbook that is closedThe formula contains a file name in square brackets, like [Budget.xlsx], works while that file is open, and turns into #VALUE! after you close it and recalculate.
Ranges of different sizes in SUMIFS, COUNTIFS or SUMPRODUCT, or a criteria longer than 255 charactersThe ranges in the formula cover different numbers of rows or columns, for example =SUMIFS(C2:C10,A2:A12,"x"). Or the text you are matching is a long string over 255 characters.
Windows regional list separator set to a minus signSimple subtraction like =A1-B1 returns #VALUE! even though both cells hold real numbers, and it happens in every workbook on that PC but not on other computers.

How to fix it

1. Find the cell that is actually causing the error

  1. Click the #VALUE! cell and look at the formula bar to see which cells it refers to.
  2. Double-click the cell (or press F2) so Excel color-outlines each referenced cell or range.
  3. In an empty cell, type =ISTEXT(A1) for each referenced cell you suspect (replace A1). TRUE means that cell holds text.
  4. If the referenced cell itself shows #VALUE!, follow the chain back to the first cell that shows the error. Fix that one and the rest clear.
  5. In Excel for Windows, you can also select the cell and go to Formulas > Evaluate Formula, then click Evaluate repeatedly to see which part fails.

2. Use SUM or PRODUCT instead of + and *

  1. Replace a formula like =A2+B2+C2 with =SUM(A2:C2).
  2. Replace =A2*B2 with =PRODUCT(A2,B2).
  3. Press Enter. SUM and PRODUCT skip text and blank-looking cells instead of returning #VALUE!.
  4. Check the result. If a cell you expected to count is being skipped, it holds a number stored as text; convert it with the next fix.

3. Remove spaces and hidden characters

  1. Select the cells the formula refers to.
  2. Open Replace: press Ctrl+H on Windows, or go to Home > Find & Select > Replace (Windows) / Edit > Find > Replace (Mac).
  3. Type a single space in Find what, leave Replace with empty, and click Replace All.
  4. If the error remains, the cells may contain non-breaking spaces. In a helper column use =TRIM(CLEAN(SUBSTITUTE(A1,CHAR(160),""))) and fill it down.
  5. Copy the helper column, then use Home > Paste > Paste Values over the original cells and delete the helper column.
  6. To find cells that only look empty, turn on Home > Sort & Filter > Filter, open the column's filter arrow, select only (Blanks), and delete the contents of the filtered cells. Clear the filter afterwards.

4. Convert text dates and text numbers into real values

  1. Select one column of dates or numbers that are aligned left.
  2. Go to Data > Text to Columns, choose Delimited, and click Next twice.
  3. For dates, under Column data format choose Date and pick the order the text uses (MDY, DMY or YMD). For numbers, leave it on General.
  4. Click Finish. The values should jump to the right side of the cell.
  5. For a single cell, you can instead click the green triangle warning and choose Convert to Number, or use =DATEVALUE(A1) or =VALUE(A1) in a helper column.

5. Fix SUMIF, SUMIFS and COUNTIF formulas

  1. If the formula refers to another workbook (a name in [brackets]), open that workbook and press F9 to recalculate.
  2. To avoid needing the other file open, rewrite the formula with SUM and IF, for example =SUM(IF([Book2.xlsx]Sheet1!A2:A100="x",[Book2.xlsx]Sheet1!C2:C100,0)). In older Excel versions confirm it with Ctrl+Shift+Enter.
  3. In SUMIFS and COUNTIFS, make every range the same size: A2:A12 and B2:B12 need a sum range like C2:C12, not C2:C10.
  4. If the criteria text is longer than 255 characters, split it with & (for example "first part"&"second part") or shorten it.

6. Check the Windows list separator (subtraction fails everywhere)

Excel for Windows (Windows 10 and 11)

  1. Close Excel.
  2. Open Control Panel > Clock and Region > Region (in Windows 11 you can also reach it from Settings > Time & language > Language & region > Administrative language settings... no, use Control Panel for the Formats tab).
  3. On the Formats tab, click Additional settings.
  4. Look at List separator. If it is a minus sign (-), change it to a comma (,) or, in regions that use a comma as the decimal symbol, a semicolon (;).
  5. Click OK twice, reopen Excel and recalculate the workbook.

Next time, skip the search

Crade is an AI assistant for Mac and Windows that sees your screen. When a message like this pops up, ask “what does this mean?” and it explains the exact error in front of you, step by step.

Download Crade

Still not fixed?

If none of these clears the error, copy the formula into a new blank workbook and rebuild it one reference at a time to see which input triggers #VALUE!. Check the function's own help page (Microsoft Support has separate #VALUE! guides for VLOOKUP, INDEX/MATCH, SUMPRODUCT, DATEVALUE, FIND and others). If the workbook pulls data from an external connection your account can't reach, ask the file's owner for a copy saved with Paste Values instead of live formulas. For company-managed Office installs, your IT team can confirm regional settings and data connection access, and the Microsoft Q&A community is the best place to post a sample formula.

Questions people ask

How do I make Excel show 0 or a blank instead of #VALUE!?

Wrap the formula in IFERROR, for example =IFERROR(A1+B1,0) or =IFERROR(A1+B1,""). This hides every error the formula produces, not just #VALUE!, so fix the real cause first if the numbers matter.

Why does my formula say #VALUE! when the cell looks empty?

The cell probably contains a space or an invisible character, which Excel treats as text. =LEN(A1) returns more than 0 if so. Select the cell and press Delete, or use SUM instead of + so text is ignored.

Why does subtracting two dates give #VALUE!?

At least one of the dates is stored as text, often after importing from a CSV or another system. Convert it with Data > Text to Columns (choose Date in the last step) or =DATEVALUE(A1).

Why does my SUMIF work only when the other file is open?

SUMIF, SUMIFS, COUNTIF, COUNTIFS and COUNTBLANK cannot read ranges from a closed workbook and return #VALUE!. Open the source file and press F9, or rewrite the formula as SUM(IF(...)), which works with closed files.

What is the difference between #VALUE! and #N/A?

#VALUE! means a formula received the wrong type of data, such as text where a number belongs. #N/A means a lookup (VLOOKUP, XLOOKUP, MATCH) could not find the value it was searching for.

Sources

Related errors