The #VALUE! error in Excel appears when a formula contains a value of the wrong type, such as text where a number is expected. The cause is almost always hiding in a cell the formula points to, and the fix usually takes under a minute once that cell is found. This guide shows how to locate it and correct it.
Key Takeaways
- #VALUE! means a formula received the wrong kind of data, most often text instead of a number or date.
- Evaluate Formula on the Formulas tab pinpoints the exact part of the formula that fails.
- Invisible spaces and text-formatted numbers are the two most frequent hidden culprits.
- TRIM, VALUE, and SUBSTITUTE clean the bad data without retyping anything.
- IFERROR should be added only after the real problem is fixed, never as a replacement for fixing it.
How to Fix the #VALUE! Error in Excel Step by Step
- Step 1: Click the cell that shows #VALUE! and read the formula in the formula bar. Note every cell reference it uses, because one of them holds the problem.
- Step 2: Click the small warning icon that appears beside the cell, then choose Show Calculation Steps. This opens the Evaluate Formula dialog directly. If no icon appears, go to the Formulas tab, find the Formula Auditing group, and select Evaluate Formula.
- Step 3: Click Evaluate repeatedly. Excel resolves the formula one piece at a time, and the piece that turns into #VALUE! is the one to fix.
- Step 4: Close the dialog and check the referenced cells. Select the formula cell, then choose Trace Precedents on the Formulas tab to draw arrows to every cell feeding it. Look for numbers aligned to the left of the cell, which usually means they are stored as text.
- Step 5: Clean the offending cell. Use VALUE(A2) to turn text that looks like a number into a real number, or TRIM(A2) to strip extra spaces. If the data was copied from a website or PDF, use SUBSTITUTE(A2,CHAR(160),CHAR(32)) to replace non-breaking spaces with normal ones.
- Step 6: Replace the original input with the cleaned result. Copy the cleaned cells, then use Paste Special and choose Values so the formula keeps working without depending on a helper column.
- Step 7: Press Enter on the original formula, then drag the fill handle down to apply the fix to the rest of the column.
Tip: Press Ctrl + backtick (the key left of the number 1) to show every formula on the sheet at once. This makes mismatched references easy to spot.
Most Common Causes and Matching Fixes
- Text in arithmetic: A formula like =A2*B2 fails if either cell contains text. Convert the text with VALUE, or use SUM, which ignores text instead of failing.
- Cells that look empty but are not: A single space counts as text. Select the cell and press Delete, or clean the range with TRIM.
- Dates stored as text: Date math fails when a date is really a text string. Use DATEVALUE(A2) to convert it, or select the column and use Data, then Text to Columns, then Finish to force conversion.
- Wrong argument type: A function that expects a single value may receive a range, or a function expecting a number may receive a word. Check the function’s argument list and match each argument to the expected type.
- Mismatched array sizes: Functions such as SUMPRODUCT return #VALUE! when the ranges have different numbers of rows or columns. Resize the ranges so they match.
- Lookup text that is too long: MATCH and similar lookups fail when the search value runs beyond 255 characters. Shorten the lookup value.
Common Problems and Fixes
The formula still shows #VALUE! after using VALUE
The cell likely contains a hidden character, often a non-breaking space from copied web data. Wrap the cleanup together: VALUE(TRIM(SUBSTITUTE(A2,CHAR(160),CHAR(32)))). This swaps the odd space for a normal one, trims it, and converts the result to a number.
Evaluate Formula is greyed out
The command is unavailable when a cell is not selected or when the sheet is protected. Click a formula cell first. If the sheet is protected, unprotect it from the Review tab.
The error disappears in one cell but returns when the formula is filled down
Some rows in the column contain bad data while others do not. Run Trace Precedents on a failing row and compare it with a working row to see what differs, such as a stray space or a text entry.
The error appears after opening a shared file
The formula may depend on a workbook that is closed or moved. Open the linked file, or check Data, then Edit Links, to confirm the source path is still valid.
Hiding the error with IFERROR leaves wrong totals
IFERROR(A2*B2,0) replaces the error with zero, which can quietly understate totals. Fix the source data first, and use IFERROR only for cases where a blank or zero result is truly acceptable.
Tip: Before wrapping any formula in IFERROR, confirm the error is gone without it. A hidden error is still an error.
Frequently Asked Questions
What does the #VALUE! error mean in Excel?
It means a formula contains a value of the wrong type or an argument Excel cannot use. The most common example is a formula trying to do math on a cell that holds text.
Why does Excel show #VALUE! when adding two cells with the plus sign?
One of the cells probably contains text or an invisible space. The plus operator cannot add text, while the SUM function skips text, which is why SUM often works on the same data.
How do I find which cell is causing the #VALUE! error?
Select the formula cell and use Evaluate Formula on the Formulas tab, stepping through until the error appears. Trace Precedents also draws arrows to every cell the formula depends on.
Can a blank-looking cell cause the #VALUE! error?
Yes. A cell containing only a space, or a formula returning an empty text result, counts as text. Deleting the contents or applying TRIM clears the problem.
How do I fix #VALUE! caused by numbers stored as text?
Wrap the reference in VALUE, or select the column and use Data, then Text to Columns, then Finish. Both convert text-formatted numbers into real numbers.
Why do dates cause the #VALUE! error?
Date calculations fail when a date is stored as text rather than a true date value. DATEVALUE converts a text date into a proper date that formulas can calculate with.
Should I use IFERROR to remove the #VALUE! error?
Only after the real cause is fixed. IFERROR hides the message but does not repair the data, so totals built on the hidden errors can be wrong without any warning.
Why does a copied value from a website cause #VALUE!?
Web data often includes non-breaking spaces that TRIM cannot remove. Use SUBSTITUTE(A2,CHAR(160),CHAR(32)) to replace them with normal spaces, then convert the result with VALUE if needed.
Why does SUMPRODUCT return #VALUE!?
Its ranges must have the same dimensions. If one range has more rows or columns than another, Excel returns #VALUE!, so resize the ranges to match.
