Fix #VALUE! Error in Excel

This error occurs when a formula has the wrong type of argument or operand. It typically indicates a mismatch between expected and provided data types.

Why this happens

  • Entering text when a number is required
  • Including spaces in numeric calculations
  • Referencing cells with incompatible data types
  • Array formula dimension mismatch

How to fix it

  • Ensure all values in calculations are numbers
  • Use VALUE() to convert text to numbers
  • Remove extra spaces with TRIM()
  • Check for hidden characters with CLEAN()

#VALUE! quick fixes

  • Text in a math formula — use VALUE() or fix cell format.
  • Wrong argument type — check dates are real serial numbers, not text.
  • Array formula legacy vs dynamic spill — confirm Excel version behavior.

Functions that often show this error

  • XLOOKUP: Searches a range or an array, and returns an item corresponding to the first match it finds. If a match doesn't exist, XLOOKUP can return the closest (approximate) match.
  • SUM: Adds all the numbers in a range of cells.
  • IF: Returns one value if a condition is TRUE and another value if it's FALSE.
  • SUMIF: Adds the cells specified by a given condition or criteria.
  • INDEX: Returns the value of an element in a table or array, selected by the row and column number indexes.
  • COUNTIF: Counts the number of cells that meet a criterion.
  • LEFT: Returns the specified number of characters from the start of a text string.
  • CONVERT: Converts a number from one measurement system to another.
  • DSUM: Adds the numbers in a field of records in a database that match criteria.
  • XMATCH: Returns the relative position of an item in an array or range of cells. Supports exact match, approximate match, and wildcard matching.
  • OFFSET: Returns a reference offset from a starting point by a specified number of rows and columns.
  • CHOOSE: Returns a value from a list of values based on an index number.
  • IFS: Checks multiple conditions and returns a value corresponding to the first TRUE condition.
  • SWITCH: Evaluates an expression against a list of values and returns the result corresponding to the first matching value.
  • SUMIFS: Adds cells that meet multiple criteria across multiple ranges.
  • SUMPRODUCT: Returns the sum of the products of corresponding ranges or arrays.
  • COUNTIFS: Counts cells that meet multiple criteria across multiple ranges.
  • RIGHT: Returns the specified number of characters from the end of a text string.
  • MID: Returns a specific number of characters from a text string, starting at a specified position.
  • FIND: Finds one text string within another and returns the starting position (case-sensitive).
  • SEARCH: Finds one text string within another and returns the starting position (case-insensitive, supports wildcards).
  • CEILING: Rounds a number up to the nearest multiple of significance.
  • FLOOR: Rounds a number down to the nearest multiple of significance.
  • RANDBETWEEN: Returns a random integer between two specified numbers.

Frequently asked questions

  • What does #VALUE! error mean in Excel? #VALUE! error occurs when Excel encounters the wrong type of data in a formula. This typically happens when you try to perform math on text, use incompatible data types, or have array dimension mismatches. It's Excel's way of saying 'I can't process this type of data.'
  • How do I fix #VALUE! error in Excel? To fix #VALUE! error: 1) Check if numbers are stored as text and convert with VALUE(); 2) Remove extra spaces with TRIM(); 3) Remove hidden characters with CLEAN(); 4) Ensure date values are actual dates, not text; 5) Verify array formulas have matching dimensions.
  • Why am I getting #VALUE! when adding numbers? You're likely getting #VALUE! because one or more cells contain numbers stored as text. This often happens with imported data. Look for a green triangle in the cell corner, or check with ISNUMBER(). Convert text to numbers by multiplying by 1, using VALUE(), or using Paste Special > Multiply.
  • How do I find which cell is causing #VALUE! error? To find the problematic cell: 1) Use Evaluate Formula (Formulas tab) to step through the calculation; 2) Break the formula into parts using helper columns; 3) Use ISNUMBER() or ISTEXT() to check each referenced cell; 4) Look for cells with green triangles indicating number-as-text.
  • What causes #VALUE! in date calculations? #VALUE! in date calculations usually means dates are stored as text, not actual Excel dates. Fix by: using DATEVALUE() to convert text dates, ensuring consistent date formats, or using Text to Columns with date format. Check with ISNUMBER() - real dates return TRUE.