How to correct a #VALUE! error

The #VALUE! error appears when Excel can’t understand an argument in your formula. For example, the third argument for VLOOKUP is the column index number argument (col index num). This argument tells VLOOKUP which column of data to return and display. The correct example below shows a formula in cell I3 with the argument specified. The incorrect example shows that the formula is missing the argument, and therefore Excel displays the error.


A good example of a VLOOKUP formula: =VLOOKUP(J2,E2:G4,2,FALSE)


An incorrect example of a VLOOKUP formula: =VLOOKUP(J2,E2:G4,FALSE)

Here are other reasons why the #VALUE error can occur with VLOOKUP. But if you aren't using VLOOKUP, check out the rest of this article for more things to try.

Fix the error for a specific function

Other solutions to try

You can try to locate the source of the error by selecting the cell with the error and clicking Formulas > Evaluate Formula. An interactive dialog will appear like this one:

Use the Evaluate Formula tool to see what part of a formula is causing an error

Click the Evaluate button, and Excel will step through the parts of the formula individually. After each part is evaluated, it shows the evaluation to give you an idea of what's getting calculated. In this case the formula =A2+B2+C2 breaks because of a hidden space in cell A2. You can't see it in cell A2. However, the wizard does see it, and it shows that the value is =" "+B2+C2. The " " indicates there's a blank space in cell A2.

Sometimes you just want to replace the #VALUE error with something else like your own text, a zero or a blank cell. In this case you can add the IFERROR() function to your formula. IFERROR() will check to see if there’s an error, and if so, replace it with another value of your choice. If there isn’t an error, your original formula will be calculated. IFERROR will only work in Excel 2007 and later. For earlier versions you can use IF(ISERROR()).

Warning: IFERROR will suppress all errors, not just the #VALUE! error. It’s not advisable to use IFERROR until you are absolutely certain your formula works the way that you want. Otherwise, you may not see valuable error messages that indicate a problem.

Here’s an example of a formula that has a #VALUE! error due to a hidden space in cell D2:

Example of a #VALUE! error caused by a leading space in cell D2 - Formula in Cell E2 is =C2-D2

And here’s the same example with IFERROR applied to replace #VALUE! with zero:

Use IFERROR() to suppress all errors - Formula in cell E2 is =IFERROR(C2-D2,0)

You could also use =IFERROR(C2-D2,””) to display nothing instead of 0, or even substitute your own text, like: =IFERROR(C2-D2,”Discount Error”).

Unfortunately, you can see that IFERROR doesn’t actually resolve the error, it simply hides it. So be certain that hiding the error is better than fixing it.

Formulas with math operations like +, -, *, ^ and / may not be able to calculate cells that contain text or spaces. In this case, try using a function instead. Functions will often ignore text values and formulate everything as numbers, eliminating the #VALUE! error.

Replace mathematical operators with functions

Sometimes cells look blank, when in fact they may have hidden spaces. Double-click a cell that your formula is referencing, and check for spaces. In the following illustration, there are extra spaces to the left of the cursor in cell A2.

VALUE Error caused by leading spaces in cell A2

Try deleting the spaces, or select the cell and press Delete to see if the error goes away. If you have many rows to check, you can use the ISBLANK() function in an empty column to see if cells are truly blank or not. In the following illustration, cell A2 has a hidden space that you can’t see, and the ISBLANK function in E2 returns FALSE. Whereas A3 is truly blank and the ISBLANK function returns TRUE.

Use ISBLANK to identify potential errors - Formual in cell E2 is =ISBLANK(A2)

Sometimes when you import data from external sources, it can come into Excel with non-printing characters like ^ or '. Unfortunately, these characters can cause problems in formulas and it can be hard to delete them. However, you can use the CLEAN function to strip the characters out, and convert the values from text to numbers. You can then copy the CLEAN function range and paste just the cell values back over the original range. Here’s how: Select the CLEAN function range, and then press CTRL+C. Select the original cells and click Home > Paste > Paste Special > Values.

Use CLEAN to remove non-printing characters - formual in cell E2 is =CLEAN(A2)

Sometimes leading or trailing spaces can cause problems. Excel will generally try to remove these spaces in simple values. For example, it will remove spaces if you type “ 123”, but it might not be able to do the same with date values like “ 1/1/16”. In this case, you can use the TRIM function to remove those leading and trailing spaces, and then reference the converted TRIM value in your formula.

In this example, leading spaces in the date in A2 cause a #VALUE! error with =B2-A2. But by using =TRIM(A2), we can then use =B2-D2 to resolve the error.

USE TRIM() to remove leading or trailing spaces - Formula in cell D2 is =TRIM(A2)

Right-click a cell that the formula is referencing, and then click Format Cells. Make sure the format is not Text. Sometimes this is not practical for more than one cell. If you have more than one to check, insert a new column and use the ISTEXT() function to see if the cells are formatted as text. In the example below “173 0” isn’t a valid number, so Excel will see it as text. This can often be the result of a typo. Note that ISTEXT won’t resolve the error, it will just tell you if text could be causing the issue.

Use ISTEXT() to identify potential errors caused by non-numeric values - Formula in E3 is =ISTEXT(C3)

Your data connection may have become unavailable at some point. To fix this, restore the data connection, or consider importing the data if possible. If you don't have access to the connection, ask the creator of the workbook to make a new file for you. The new file ideally would only have values, and no connections. They can do this by copying all the cells, and pasting only as values. To paste as only values, they can click Home > Paste > Paste Special > Values. This eliminates all formulas and connections, and therefore would also remove any #VALUE errors.

If you’re not sure what to do at this point, you can search for similar questions in the Excel Community Forum, or post one of your own.

Link to the Excel Community Forum

Post a question in the Excel community forum

See Also

Overview of formulas in Excel

How to avoid broken formulas

Share Facebook Facebook Twitter Twitter Email Email

Was this information helpful?

Great! Any other feedback?

How can we improve it?

Thank you for your feedback!