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.