how to fix formula in excel getting #VALUE

#VALUE! usually means the formula is using text, spaces, hidden characters, or mismatched data instead of valid numbers.

Try this in order:

1. Find which referenced cell is causing the error
   • Select the cell showing #VALUE!
   • Go to Formulas > Evaluate Formula > Evaluate
   • Step through the formula to see which part fails
2. Check for spaces or hidden characters in the cells used by the formula
   • Select the referenced cells or the whole column
   • Go to Home > Find & Select > Replace
   • In Find what, type one space
   • In Replace with, leave it blank
   • Choose Replace All if all spaces should be removed
3. Check for cells that look blank but are not really blank
   • Go to Home > Sort & Filter > Filter
   • Filter for Blanks
   • Select unnamed blank-looking entries and delete them
   • Clear the filter
4. Check whether numbers are stored as text
   • Text values in a calculation can cause #VALUE!
   • If a number is left-aligned or includes text like units, remove the text from the cell value
   • Keep only the number in the cell
   • If needed, display units using formatting instead of typing them into the cell
5. Use functions that ignore text when appropriate
   • Instead of =A2+B2+C2, use =SUM(A2:C2)
   • Instead of =A2*B2, use =PRODUCT(A2,B2)
   • If one cell sometimes contains words instead of numbers, SUM can avoid the #VALUE! error in some cases
6. If using SUMPRODUCT, make sure ranges match
   • All array ranges must have the same size
   • Example: =SUMPRODUCT(D2:D13,E2:E13) works, but mismatched row counts can return #VALUE!
7. If AVERAGE or SUM includes cells that already contain errors
   • Use an error-ignoring formula such as:
     • =AVERAGE(IF(ISERROR(B2:D2),"",B2:D2))
     • =SUM(IF(ISERROR(B2:D2),"",B2:D2))
   • In Microsoft 365, press ENTER
   • In older Excel versions, confirm with CTRL+SHIFT+ENTER
8. Use IFERROR only if hiding the error is acceptable
   • Example: =IFERROR(your_formula,"")
   • Warning: IFERROR hides all errors, so fixing the source problem is better when possible

If the formula is a simple total, replacing manual addition like =A1+A2+A3 with =SUM(A1:A3) is often the quickest fix.

• How to avoid broken formulas in Excel
• VALUE is Excel's way of saying, "There's something wrong with the way your formula is typed. Or, there's something wrong with the cells you are referencing." The error is very general, and it can be hard to find the exact cause of it. The information on this page shows common problems and solutions for the error.
• VALUE is Excel's way of saying, "There's something wrong with the way your formula is typed. Or, there's something wrong with the cells you are referencing." The error is very general, and it can be hard to find the exact cause of it. The information on this page shows common problems and solutions for the error.
• VALUE is Excel's way of saying, "There's something wrong with the way your formula is typed. Or, there's something wrong with the cells you are referencing." The error is very general, and it can be hard to find the exact cause of it. The information on this page shows common problems and solutions for the error.
• VALUE is Excel's way of saying, "There's something wrong with the way your formula is typed. Or, there's something wrong with the cells you are referencing." The error is very general, and it can be hard to find the exact cause of it. The information on this page shows common problems and solutions for the error.
• How to correct a #VALUE! error in the SUMPRODUCT function
• How to correct a #VALUE! error in AVERAGE or SUM functions
• Correcting a #Value! error when words are sometimes used instead of numbers - Microsoft Q&A
• I am getting a #VALUE! and I have tried of the suggestion offered, but nothing works. Can you help me please? - Microsoft Q&A