In Excel: a formula receives the wrong data type — most often a text cell where a number is expected; fix by converting the text to a number or by wrapping the formula in IFERROR.
On this page8
Fix: #VALUE!
What causes #VALUE!
#VALUE! means "wrong value type." Excel raises it when a formula receives a data type it cannot process — most commonly when a math operation (addition, subtraction, multiplication, or division) encounters a cell that contains text or a blank instead of a number. Hidden spaces, apostrophes used to force text formatting, and cells imported from external systems as text are the most frequent culprits. Date arithmetic can also produce #VALUE! when a date-formatted cell is actually stored as text rather than a real Excel date serial number.
How to fix #VALUE!
- Identify which input cell contains the wrong type: click the cell showing
#VALUE!and read the formula; then click each argument cell and check its alignment (left = text, right = number). - Use
=ISNUMBER(cell)or=ISTEXT(cell)on each suspect input to confirm the type programmatically. - To convert a text-formatted number, use
=VALUE(cell), or select the column, go to Data ▸ Text to Columns, and click Finish without changing any settings — this forces Excel to reparse the column. - Remove hidden leading/trailing spaces with
=TRIM(cell)before the conversion step. - If the text source cannot be fixed, wrap the formula with
IFERROR:=IFERROR(A1+B1+C1, 0)so the cell shows a safe fallback instead of the error. - For date arithmetic errors, verify that the date cell holds a real numeric serial (
=ISNUMBER(date_cell)= TRUE) and useDATEVALUE()to convert a text date.
Identify which input cell contains the wrong type: click the cell showing #VALUE! and read the formula; then click each argument cell and check its alignment (left = text, right = number).
Use =ISNUMBER(cell) or =ISTEXT(cell) on each suspect input to confirm the type programmatically.
To convert a text-formatted number, use =VALUE(cell), or select the column, go to Data ▸ Text to Columns, and click Finish without changing any settings — this forces Excel to reparse the column.
Remove hidden leading/trailing spaces with =TRIM(cell) before the conversion step.
If the text source cannot be fixed, wrap the formula with IFERROR: =IFERROR(A1+B1+C1, 0) so the cell shows a safe fallback instead of the error.
For date arithmetic errors, verify that the date cell holds a real numeric serial (=ISNUMBER(date_cell) = TRUE) and use DATEVALUE() to convert a text date.
What this does
#VALUE! means "wrong value type." Excel raises it when a formula receives a data type it cannot process — most commonly when a math operation (addition, subtraction, multiplication, or division) encounters a cell that contains text or a blank instead of a number. Hidden spaces, apostrophes used to force text formatting, and cells imported from external systems as text are the most frequent culprits. Date arithmetic can also produce #VALUE! when a date-formatted cell is actually stored as text rather than a real Excel date serial number. Most people learn this as a sequence of clicks and forget it by next week; learning it as a pattern instead is what lets you apply it to the next, slightly different version of the problem without starting from scratch. That is the difference this page is trying to make. For “#value error in excel”, the reliable version is a short checking loop, not just the first command that appears to work. Run it on a deliberately small range first, watch how the affected cells change, and only then apply the same setup to the full sheet. When this is a ribbon command, the selection matters more than the button: confirm the range, apply the command, then spot-check the output before saving. That is what makes a calculation you can defend to a CFO or an auditor useful in real work: repeatable, auditable, and not dependent on memory or luck.
A worked example
Scenario: you have a SUM formula =A1+B1+C1 and cell B1 contains the text "N/A" (not the #N/A error, but the literal string). Excel cannot add text to numbers, so the formula returns #VALUE!. To diagnose: click each input cell and check whether its value is left-aligned (text) or right-aligned (number) in the cell — text is left-aligned by default. Use =ISNUMBER(B1) to confirm. To fix: use =VALUE(B1) to convert the text to a number, or replace "N/A" with a real numeric value or blank. If the source data is imported, use Data ▸ Text to Columns ▸ Finish to force Excel to re-parse the column as numbers. Investigate #VALUE! whenever you add a new data source or paste data from another system — those are the moments when text-disguised numbers silently enter your workbook. Catching it early prevents aggregated totals from under-counting rows that quietly resolve to zero inside SUM() while a raw addition would have flagged the type mismatch immediately. Check the referenced cells, argument order and result type in your workbook. Start with the worked example before changing an input, then compare the changed result with the calculation you expected. An error value or a result of a different type is a reason to inspect the inputs and arguments.
In Google Sheets
Google Sheets support for this task has not been verified. Check the function, arguments and workflow in the target product before transferring the example. Matching formula notation does not establish identical behaviour. Keep this page bookmarked for the next time the same question comes up. Better still, repeat the steps once in your own workbook — doing it yourself is what turns a copied answer into something you remember. For “#value error in excel”, keep the input cells and the result visible while you check the formula in Excel. Follow the worked example and change one input at a time. The steps explain the workbook calculation; this page does not run a live calculator for that task.
Common mistakes
- Summing a column that contains column headers or category labels — if a header row is accidentally included in a
SUMrange, the text label causes#VALUE!. Always ensure the range starts below the header. - Importing numbers from CSV or a database without forcing re-parsing — the imported cells look numeric but are stored as text. Run Data ▸ Text to Columns on every imported numeric column before using it in formulas.
- Using arithmetic operators (+, -, *, /) on ranges that include errors — use
SUMIForAGGREGATEto skip error cells instead of plainSUM.
Frequently asked questions
Why does SUM work but + gives #VALUE!?
SUM() ignores text cells and treats them as zero, while the + operator raises #VALUE! when it encounters text. This is by design: SUM is tolerant of mixed ranges, but the arithmetic operators enforce strict number types. Switch from =A1+B1+C1 to =SUM(A1:C1) to handle mixed-type ranges gracefully.
How do I convert text-formatted numbers to real numbers?
Select the affected cells, open Data ▸ Text to Columns, and click Finish without changing any settings — Excel reparses the cells as numbers. Alternatively use =VALUE(A1) in a helper column, then copy-paste as values over the original column. The green triangle warning in the corner of text-number cells is another signal that this conversion is needed.
Can I hide #VALUE! while still getting a useful result?
Use =IFERROR(formula, fallback) to return a fallback value (0, blank, or a descriptive string) when the formula would otherwise produce an error. But always investigate the root cause: if the text appears because of bad imported data, fix the import process rather than permanently hiding the problem.
Other ways people ask this
People reach this page typing “value error in excel”, “what does #value mean in excel”, “#value excel” and “if value error excel”, among other phrasings; whichever wording you used, the fix above is the one you want.
Why do people search for this in so many different ways?
Because the same task has many names. “value error in excel”, “what does #value mean in excel”, “#value excel” all point at the one operation explained on this page, which is why they all lead here.