Excel #VALUE! Error
In Microsoft Excel, #VALUE! means a formula got the wrong type of value, often text or a hidden space in a cell it uses for math. In Excel for Windows, select the cell and choose Formulas > Evaluate Formula to see which part fails. Then remove the stray spaces or text, or use =SUM(A2:C2) instead of =A2+B2+C2.
Try these quick fixes first
- Select the cell, select the warning icon next to it, and read the first line of the menu.
- Look for numbers or dates that sit on the left side of their cells. They are probably stored as text.
- Replace
+or*withSUMorPRODUCT, such as=SUM(A2:C2). - Check the cells the formula uses for spaces that make them look empty.
- If the formula links to another workbook, open that workbook and press
F9. - Fix any other error values in the cells the formula refers to.
Find the part of the formula that fails
Evaluate Formula works through a formula one piece at a time, so you can see exactly where #VALUE! first appears.
- In Excel for Windows, select the cell with the error.
- Select Formulas > Evaluate Formula.
- Select Evaluate again and again. Each click works out the underlined part and shows its result.
- Watch for the step where a cell’s value shows up as text, such as
" ", or where #VALUE! first appears. That is the cell to fix. - Select Close.
On a Mac, select the warning icon next to the cell instead. The first line of its menu describes the error.
Remove spaces that make cells look blank
A cell that holds only a space looks empty but isn’t, and math operators such as + may not be able to calculate it.
- Save your work first, because Replace All changes every match at once. Then select the cells the formula uses. If the column holds only numbers, select the whole column.
- Select Home > Find & Select > Replace, or press
Ctrl+H. - In Find what, type one space. Leave Replace with empty.
- Select Replace All, or use Find Next and Replace to check each one. Don’t do this on columns of names or other text, where spaces belong.
- If the error stays, select Home > Sort & Filter > Filter. Open the column’s filter arrow, clear (Select All), then check (Blanks) and any box with nothing next to it.
- Select the cells that appear and press
Delete. Then open the filter arrow and choose Clear filter from to show all rows again.
Find text and hidden characters in number cells
A stray letter, symbol or character you can’t see turns a number into text, and Excel can’t do math with it.
- In an empty column, type
=ISTEXT(E2), using the first cell your formula refers to, and copy it down. - Any row that shows
TRUEholds text, not a number. ISTEXT only finds the problem; it doesn’t fix it. - Retype the value in that cell if you can.
- For many cells, use
=CLEAN(E2)in a helper column to strip characters that don’t print. Save your work, copy the results, select the original cells, then select Home > Paste > Paste Special > Values. - If cells now show a green triangle, select them, select the warning icon and choose Convert to Number.
- Delete the helper columns.
If a formula shows as text instead of a result, see our guide to Excel formulas not calculating.
Use SUM or PRODUCT instead of + and *
Functions often ignore text and calculate with the numbers, while the + and * operators may fail on a cell that holds text or a space.
- Select the cell and look for math operators between cell references, such as
=A2+B2+C2. - Change adding to
=SUM(A2:C2). - Change multiplying, such as
=A2*B2, to=PRODUCT(A2,B2). - Press
Enter. - Check the result. SUM leaves out the text cell, so fix that cell too if it should count.
Fix dates stored as text
Excel won’t treat text as a date, so subtracting a date stored as text, which sits on the left side of its cell, returns #VALUE!.
- Widen the date column. Dates on the right side of the cell are real dates. Dates on the left are text.
- Double-click a left-aligned date and check for spaces in front of it.
- If you find spaces, save your work, select the column header, then select Data > Text to Columns.
- Select Next twice. Under Column data format, select Date, choose a date format and select Finish.
- If there are no spaces, the date may be in a different order from your computer’s date settings, such as 31/12/2017 on a computer set to month first. Build a real date in a new column with
=DATE(RIGHT(A1,4),MID(A1,4,2),LEFT(A1,2)), adjusted to your layout.
Open the workbook the formula links to
SUMIF, SUMIFS, COUNTIF, COUNTIFS and COUNTBLANK return #VALUE! when they refer to cells in another workbook that is closed.
- Select the cell and look in the formula for a workbook name in square brackets, such as
[Budget.xlsx]. - Open that workbook.
- Go back to your file and press
F9to recalculate. - If you can’t open the linked file because it’s on a drive or site you can’t reach, ask your IT team.
Check what each function argument expects
Some functions return #VALUE! when an argument is the wrong type, too long or the wrong size.
- Select the cell, then select Formulas > Insert Function to see each argument and what it expects.
- VLOOKUP: the column number must be 1 or more, and the value you look up can’t be longer than 255 characters.
- SUMIFS: the sum range and every criteria range must cover the same number of rows and columns, such as
C2:C12withA2:A12. - SUMIF and COUNTIF: criteria text longer than 255 characters fails. Split it with
&, such as"long string"&"another long string". - DATEVALUE: the text must be a date in a format your computer’s date settings accept.
Fix errors in the cells the formula uses
When a cell that SUM, AVERAGE or IF refers to already shows #VALUE!, the formula shows #VALUE! too, so the first error has to be fixed.
- Select the cell and look at the cells its formula refers to.
- Find any that show #VALUE! or another error, and fix those first with the steps above.
- If those errors are expected and you only want to total the rest, use
=SUM(IF(ISERROR(B2:D2),"",B2:D2)). In Excel for Microsoft 365, pressEnter. In older versions, pressCtrl+Shift+Enter.
Why does this happen?
#VALUE! is Excel’s general signal that something is wrong with how a formula is typed or with the cells it uses. Common reasons:
- A cell used in math holds text, a space or a hidden character.
- A date is stored as text, or typed in a different order from your computer’s date settings.
- A function got the wrong kind of argument, such as a VLOOKUP column number below 1.
- A cell the formula uses already shows an error.
- The formula links to a closed workbook, or to a data connection that isn’t available.
- On Windows, the list separator in the region settings is set to a minus sign, which breaks subtraction.
When should you call IT?
- Even a simple test fails: in a new workbook, 2 in A1, 4 in B1 and
=B1-A1in C1 show #VALUE!. The Windows list separator may be set to a minus sign, a system setting that also affects how CSV files open and save. - The workbook pulls data from a database, SharePoint or another system, and the connection stopped working.
- Linked workbooks sit on a shared drive or site you can’t open.
- Dates work on one computer but show #VALUE! on another, which points to different date settings.
- The file feeds billing, payroll or reporting, and fixing it means changing formulas other people rely on.
If your team shares workbooks in OneDrive or SharePoint, see our Microsoft 365 support page.
OAC Technology’s IT support team fixes problems like this for businesses across the Twin Cities. Clients can call 952-548-5558, Monday to Friday, 8 a.m. to 6 p.m. Central. Not a client yet? Call the same number or send us a message.
Common questions
What causes a #VALUE error in Excel?
A formula received a value of the wrong type, often text or a space in a cell it uses for math. Dates stored as text, wrong function arguments and links to closed workbooks cause it too.
Why am I getting #VALUE! when multiplying in Excel?
One of the cells holds text, a space or a hidden character that Excel can’t treat as a number. Find it with =ISTEXT() and fix it, or use =PRODUCT(A2,B2), which ignores text.
How do I show 0 instead of #VALUE! in Excel?
Wrap the formula in IFERROR, such as =IFERROR(A2*B2,0). Do this only once you know the formula works, because IFERROR hides every error, not just #VALUE!.
What is the difference between #VALUE! and #REF!?
#VALUE! means a formula got the wrong type of value, while #REF! means a cell reference isn’t valid, for example because cells the formula used were deleted. For #REF!, press Ctrl+Z to undo the deletion or rebuild the reference.









