#VALUE! error in Excel: find the bad cell and fix it

#VALUE!

Why it happens

Microsoft describes the error as 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." Its own page, How to correct a #VALUE! error, adds that the error is very general and the exact cause can be hard to find. The causes it documents fall into a few groups.

Arithmetic on text or spaces. Operators such as + and * might not be able to calculate cells that contain text or spaces. The page says #VALUE! often occurs because the formula refers to cells containing spaces, or hidden spaces, which make a cell look blank when it is not. Hidden characters other than spaces, such as a single apostrophe, can do the same. A special character inside a number (Microsoft's example shows one as a small box after "00") turns the cell into text too.

Numbers stored as text. A number aligned to the left with a green triangle in the corner is stored as text. Microsoft's guide to converting numbers stored as text says such values can cause unexpected results.

Dates stored as text. When you subtract one date from another, both must be real dates. Microsoft's test: widen the column; a date aligned to the right is a date, one aligned to the left is text, and Excel will not recognise text as a date. Two documented reasons are a leading space before the date, and a date typed in a different order from your computer's date system (for example dd/mm/yy on a computer set to mm/dd/yyyy). The DATEVALUE article states the same for that function: a mismatch between the format of date_text and the system's date and time settings gives #VALUE!.

Ranges of different sizes. Some functions require their ranges to line up. In SUMIFS, criteria_range and sum_range must refer to the same number of rows and columns (SUMIF/SUMIFS article); in SUMPRODUCT, all array arguments must have the same dimension (SUMPRODUCT article).

Errors passed along. If AVERAGE or SUM refers to cells that already contain #VALUE!, the result is #VALUE! as well (AVERAGE or SUM article). Fix the first error in the chain and the others follow.

Windows list separator. If even =B1-A1 with 2 in A1 and 4 in B1 returns #VALUE! in a new workbook on Windows, Microsoft points to the List separator in the Region settings being set to the minus sign.

How to fix it

1. Find the reference that breaks the formula. Select the cell with #VALUE!, then select Formulas > Evaluate Formula > Evaluate. Excel steps through the parts of the formula individually. In Microsoft's example, =E2+E3+E4+E5 fails because of a hidden space in E2 that you cannot see in the cell but that appears in the Evaluate Formula box as " ".

2. Flag text cells with ISTEXT. In a spare column, test each cell the formula uses. ISTEXT returns TRUE when the value refers to text (IS functions):

   =ISTEXT(E2)
   

   Fill it down. Any TRUE marks a cell that holds text where you expect a number. ISTEXT does not fix anything; it only finds the suspects. Its companion =ISNUMBER(E2) returns TRUE only for a number, and =ISBLANK(E2) returns TRUE only for an empty cell, so a cell that merely looks blank returns FALSE.

3. Remove spaces from the referenced cells. Select the cells, or the whole column, then Home > Find & Select > Replace. Type a single space in Find what, leave Replace with empty, and choose Replace All if every space in that range should go, or Find Next and Replace to go one at a time.

4. Clear hidden characters from cells that look blank. Turn on the filter with Home > Sort & Filter > Filter. Open the filter arrow, clear Select All, tick Blanks and any check box with nothing next to it. Select the cells Excel returns, press Delete, then choose Clear filter from... on the same arrow.

5. Fix special characters. Delete the cell's contents and retype the number, or use CLEAN or REPLACE to strip the characters. After using a function, copy the result and use Home > Paste > Paste Special > Values so the cleaned value replaces the original.

6. Convert numbers stored as text. Select the cells, select the error indicator in the top-left corner (or press Alt+Shift+F10) and choose Convert to Number. If no indicator appears, turn on File > Options > Formulas > Enable background error checking. The alternative is a helper column with VALUE, then Paste Special > Values back over the original:

   =VALUE(E23)
   

7. Convert dates stored as text. If the dates have leading spaces, select the column, then Data > Text to Columns, select Next twice, choose Date under Column data format, pick the date format and select Finish. If the date is typed in a different order from your system, build a real date with DATE. Microsoft's example assumes a computer set to mm/dd/yyyy and the text 31/12/2017 in A1, with two characters for the day, two for the month and four for the year:

   =DATE(RIGHT(A1,4),MID(A1,4,2),LEFT(A1,2))
   

   Adjust the RIGHT, MID and LEFT positions if your text dates are laid out differently.

8. Use a function instead of an operator. Microsoft notes that functions often ignore text values. Replace =A2+B2+C2 with:

   =SUM(A2:C2)
   

   and =A2*B2 with =PRODUCT(A2,B2). Microsoft says this can eliminate the #VALUE! error, but the text stays in the cells, so fix the data as well if other formulas use it.

9. Check data connections. If the formula depends on a data connection that has become unavailable, restore the connection or import the data. Pasting the cells as values removes formulas and connections, and with them the #VALUE! errors.

Check that it worked

• The result cell shows a number instead of #VALUE!. Run Evaluate Formula again if not; it will show the next reference that fails.
• Your ISTEXT column shows FALSE for every cell that should be a number. Delete the helper column afterwards.
• The green triangle has gone from converted numbers, and converted dates are aligned to the right when the column is widened.
• Any SUM or AVERAGE that depended on the cell has recalculated without an error.

#VALUE! in SUMIFS and SUMPRODUCT: ranges that do not match

When the data is clean and the error persists, check the range sizes. Microsoft's SUMIFS example sums C2:C10 against criteria in A2:A12 and B2:B12, which returns #VALUE!:

=SUMIFS(C2:C10,A2:A12,A14,B2:B12,B14)

Make sum_range cover the same rows:

=SUMIFS(C2:C12,A2:A12,A14,B2:B12,B14)

Plain SUMIF can use ranges of different sizes, according to the same article. SUMPRODUCT cannot: =SUMPRODUCT(D2:D13,E2:E10) fails because the ranges have different row counts, and =SUMPRODUCT(D2:D13,E2:E13) fixes it. SUMPRODUCT also returns #VALUE! when a referenced cell contains text or is formatted as Text, so steps 2 to 6 above apply to it too.

Two more causes are specific to these functions. SUMIF, SUMIFS, COUNTIF and COUNTIFS that refer to a range in a closed workbook return #VALUE!; open that workbook and press F9 to refresh. And a criteria string longer than 255 characters makes SUMIF and SUMIFS return incorrect results; Microsoft suggests splitting it with &, as in =SUMIF(B2:B12,"long string"&"another long string").

VLOOKUP has its own #VALUE! cases, a column number that is text or less than 1 and a lookup value over 255 characters, covered in VLOOKUP not working. The hidden spaces and numbers-as-text behind #VALUE! are also what make a lookup miss a value you can see, explained in XLOOKUP returns #N/A when the value exists. A dynamic array formula that cannot place its results returns a different error; see #SPILL! error in Excel.

Hiding the error instead of fixing it

You can wrap the formula in IFERROR to show your own text, a zero or nothing instead of the error. For example, to show two dashes:

=IFERROR(E2+E3+E4+E5,"--")

Microsoft warns that IFERROR hides all errors, not just #VALUE!, and that it does not resolve the error. Use it only when the formula is already correct and an error is an expected, harmless case.

In each documented cause, the formula is held up by what it is fed: text, spaces, text dates or ranges of the wrong size. Start from Evaluate Formula, follow the reference it stops on, and fix that cell's content or the range it belongs to. Once the inputs are real numbers and real dates in ranges of matching size, the formula calculates, apart from any range you had to resize.