How to correct a #VALUE! error

The most common reason for the #VALUE! error is that a formula is expecting numbers in a cell, but instead it finds spaces, text, or other characters.

Top solution - Try using functions instead of operators

Formulas with math operators 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
Use functions instead of operators

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

Link to the Excel Community Forum

Note: Click here if you need help on the #VALUE! error with a specific function, like IF or VLOOKUP.

If you want to move forward, then the following checklist provides troubleshooting steps to help you figure out what may have gone wrong in your formulas.

If your formula is throwing a #VALUE! error, but you don't know where it is, you can use the Evaluate Formula Wizard. Select the cell with the error and goto Formulas > Evaluate Formula, and an interactive dialog will appear:

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

Each time you click the Evaluate button, Excel will step through the formula and calculate each part individually, then display the results. This won't fix your formula, but it can help you figure out where it's breaking, so you can go fix it. In this case the formula =A2+B2+C2 breaks because of a leading space in cell A2, but you can't see it. The Evaluate Formula Wizard does though and it shows that the value is =" "+B2+C2, where the " " indicates a blank space.

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

VALUE Error caused by leading spaces in cell A2
Check for hidden spaces inside cells

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)
ISBLANK returns FALSE when there are hidden spaces in cells

Tip: Use Auto Filter to mass delete blank cells. You can click Data > Filter and Filter for Blanks on the column with blanks (Auto Filter will see cells with leading spaces as blanks as well). Next, select the entire column and press CTRL+G (Goto) > Special > Visible Cells only, then press DELETE to clear everything.

Use AutoFilter to display only cells with Blanks
Use AutoFilter to delete blank cells
Use Goto > Special > Visible Cells only to hide non-blank rows so they can be deleted
Use Filter on a column heading to check for any blank cells in that column

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 (or Alt > E > S > V for keyboard shortcuts for the PC - For MAC you can use Image of the MAC Command button icon +OPTION + V > V > Enter).

Use CLEAN to remove non-printing characters - formual in cell E2 is =CLEAN(A2)
The CLEAN function removes non-printing characters inside cells

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 calculation, or use the Copy > Paste Special > Values method to replace the original values.

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)
The TRIM function removes extra spaces inside cells

Right-click a cell that the formula is referencing, and then click Format Cells (or use CTRL+1). Make sure the format is not Text. Sometimes this is not practical for more than one cell, so 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)
The ISTEXT function determines if a formula references cells containing text

To fix this, restore the data connection, or consider importing the data if possible. This can happen a lot with distributed workbooks, where you might send a workbook to someone who doesn't have the data connection. In cases like this it can be a good idea to create a distribution copy where you copy an entire worksheet(s) and Paste > Special >Values, which will eliminate formulas and links.

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 versions. For earlier versions you can use IF(ISERROR()).

Warning: IFERROR is a blanket error handler, meaning that it will suppress all errors, not just the #VALUE! error. It’s not advisable to use IFERROR until you are absolutely certain that your formula performs the way that you want. Otherwise, you won’t see potentially valuable error messages that might indicate you have a problem.

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

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

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)
The IFERROR function enables you to replace the #VALUE! error with a value of your choice

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.

For more information on the #VALUE! error appearing in a specific function, see the topics below:

Top of the page

Do you have a specific function question?

Post a question in the Excel community forum

Help us improve Excel

Do you have suggestions about how we can improve the next version of Excel? If so, please check out the topics at Excel User Voice

See Also

Overview of formulas in Excel

How to avoid broken formulas

Use error checking to detect errors in formulas

Keyboard shortcuts in Excel 2016 for Windows

Keyboard shortcuts in Excel 2016 for Mac

Excel functions (alphabetical)

Excel functions (by category)

Was this information helpful?

Great! Any other feedback?

How can we improve it?

Thank you for your feedback!

×