Sign in with Microsoft
Sign in or create an account.
Hello,
Select a different account.
You have multiple accounts
Choose the account you want to sign in with.
How to correct a #VALUE! error

#VALUE is 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." The error is very general, and it can be hard to find the exact cause of it. The information on this page shows common problems and solutions for the error.

Use the drop-down below or jump to one of the other areas:

Fix the error for a specific function

Problems with subtraction

If you're new to Excel, you might be typing a formula for subtraction incorrectly. Here are two ways to do it:

Subtract a cell reference from another

Cell D2 with $2,000.00, Cell E2 with $1,500.00, Cell F2 with formula: =D2-E2 and result of $500.00

Type two values in two separate cells. In a third cell, subtract one cell reference from the other. In this example, cell D2 has the budgeted amount, and cell E2 has the actual amount. F2 has the formula =D2-E2.

Or, use SUM with positive and negative numbers

Cell D6 with $2,000.00, Cell E6 with $1,500.00, Cell F6 with formula: =SUM(D6,E6) and result of $500.00

Type a positive value in one cell, and a negative value in another. In a third cell, use the SUM function to add the two cells together. In this example, cell D6 has the budgeted amount, and cell E6 has the actual amount as a negative number. F6 has the formula =SUM(D6,E6).

If you're using Windows, you might get the #VALUE! error when doing even the most basic subtraction formula. The following might solve your problem:

  1. First do a quick test. In a new workbook, type a 2 in cell A1. Type a 4 in cell B1. Then in C1 type this formula =B1-A1. If you get the #VALUE! error, go to the next step. If you don't get the error, try other solutions on this page.

  2. In Windows, open your Region control panel.

    • Windows 10: Select Start, type Region, and then select the Region control panel.

    • Windows 8: At the Start screen, type Region, select Settings, and then select Region.

    • Windows 7: Select Start, type Region, and then select Region and language.

  3. On the Formats tab, select Additional settings.

  4. Look for the List separator. If the List separator is set to the minus sign, change it to something else. For example, a comma is a common list separator. The semicolon is also common. However, another list separator might be more appropriate for your particular region.

  5. Select OK.

  6. Open your workbook. If a cell contains a #VALUE! error, double-click to edit it.

  7. If there are commas where there should be minus signs for subtraction, change them to minus signs.

  8. Press ENTER.

  9. Repeat this process for other cells that have the error.

Subtract a cell reference from another

Cell D10 with 1/1/2016, Cell E10 with 4/24/2016, Cell F10 with formula: =E10-D10 and result of 114

Type two dates in two separate cells. In a third cell, subtract one cell reference from the other. In this example, cell D10 has the start date, and cell E10 has the End date. F10 has the formula =E10-D10.

Or, use the DATEDIF function

Cell D15 with 1/1/2016, Cell E15 with 4/24/2016, Cell F15 with formula: =DATEDIF(D15,E15,"d") and result of 114

Type two dates in two separate cells. In a third cell, use the DATEDIF function to find the difference in dates. For more information on the DATEDIF function, see Calculate the difference between two dates.

Make your date column wider. If your date is aligned to the right, then it's a date. But if it's aligned to the left, this means the date isn't really a date. It's text. And Excel won't recognize text as a date. Here are some solutions that can help this problem.

Check for leading spaces

  1. Double-click a date that is being used in a subtraction formula.

  2. Put your cursor at the beginning and see if you can select one or more spaces. Here's what a selected space looks like at the beginning of a cell: Cell with space selected before 1/1/2016

    If your cell has this problem, proceed to the next step. If you don't see one or more spaces, go to the next section on checking your computer's date settings.

  3. Select the column that contains the date by selecting its column header.

  4. Select Data > Text to Columns.

  5. Select Next twice.

  6. On Step 3 of 3 of the wizard, under Column data format, select Date.

  7. Choose a date format, and then select Finish.

  8. Repeat this process for other columns to ensure they don't contain leading spaces before dates.

Check your computer's date settings

Excel uses your computer's date system. If a cell's date isn't entered using the same date system, Excel won't recognize it as a true date.

For example, let's say that your computer displays dates as mm/dd/yyyy. If you typed a date like that in a cell, Excel would recognize it as a date and you'd be able to use it in a subtraction formula. However, if you typed a date like dd/mm/yy, Excel wouldn't recognize that as a date. Instead, it would treat it as text.

There are two solutions to this problem: You could change the date system that your computer uses to match the date system you want to type in Excel. Or, in Excel you could create a new column and use the DATE function to create a true date based on the date stored as text. Here's how to do that assuming your computers date system is mm/dd/yyy and your text date is 31/12/2017 in cell A1:

  1. Create a formula like this: =DATE(RIGHT(A1,4),MID(A1,4,2),LEFT(A1,2))

  2. The result would be 12/31/2017.

  3. If you want the format to appear like dd/mm/yy, press CTRL+1 (or + 1 on the Mac).

  4. Choose a different locale that uses the dd/mm/yy format, for example, English (United Kingdom). When you're done applying the format, the result is 31/12/2017 and it is a true date, not a text date.

Note: The formula above is written with the DATE, RIGHT, MID, and LEFT functions. Notice that it is written with an assumption that the text date has two characters for days, two characters for months, and four characters for year. You might need to customize the formula to suit your date.

Problems with spaces and text

Often #VALUE! occurs because your formula refers to other cells that contain spaces, or even trickier: hidden spaces. These spaces can make a cell look blank, when in fact they are not blank.

1. Select referenced cells

Column selected

Find cells that your formula is referencing and select them. In many cases removing spaces for an entire column is a good practice because you can replace more than one space at a time. In this example, selecting the E selects the entire column.

2. Find and replace

Home tab > Find & Select > Replace

On the Home tab, select Find & Select > Replace.

3. Replace spaces with nothing

Find what box containing a space, Replace with containing nothing

In the Find what box, type a single space. Then, in the Replace with box, delete anything that might be there.

4. Replace or Replace all

Replace all button

If you are confident that all spaces in the column should be removed, select Replace All. If you'd like to step through and replace spaces with nothing on an individual basis, you can select Find next first, and then select Replace when you're confident the space isn't needed. When you're done, the #VALUE! error might be resolved. If not, go to the next step.

5. Turn on the filter

Home > Sort & Filter > Filter

Sometimes hidden characters other than spaces can make a cell appear blank, when it's not really blank. Single apostrophes within a cell can do this. To get rid of these characters in a column, turn on the filter by going to Home > Sort & Filter > Filter.

6. Set the filter

Filter menu with the Select all checkbox unselected, (Blanks) checkbox selected

Click the filter arrow Filter arrow, and then deselect Select all. Then, select the Blanks checkbox.

7. Select any unnamed checkboxes

An unnamed checkbox selected

Select any check boxes that don't have anything next to them, like this one.

8. Select blank cells, and delete

Filtered blank cells selected

When Excel brings back the blank cells, select them. Then press the Delete key. This clears any hidden characters in the cells.

9. Clear the filter

Filter menu, Clear filter from ...

Select the filter arrow Filter arrow, and then select Clear filter from... so that all cells are visible.

10. Result

#VALUE! error gone, and replaced with the formula result. Green triange in cell E4

If spaces were the culprit of your #VALUE! error then hopefully your error has been replaced by the formula result, as shown here in our example. If not, repeat this process for other cells that your formula refers to. Or, try other solutions on this page.

Note: In this example, notice that cell E4 has a green triangle and the number is aligned to the left. This means the number is stored as text. This could cause more problems later. If you see this problem, we recommend converting numbers stored as text to numbers.

Text or special characters within a cell can cause the #VALUE! error. But sometimes it's hard to see which cells have these problems. Solution: Use the ISTEXT function to inspect cells. Note that ISTEXT doesn't resolve the error, it simply finds cells that might be causing the error.

Example with #VALUE!

H4 with =E2+E3+E4+E5 and result of #VALUE!

Here’s an example of a formula that has a #VALUE! error. This is likely due to cell E2. A special character appears as a small box after "00." Or as the next picture shows, you could use the ISTEXT function in a separate column to check for text.

Same example, with ISTEXT

Cell F2 with =ISTEXT(E2) and result of TRUE

Here the ISTEXT function was added in column F. All cells are fine except the one with the value of TRUE. This means cell E2 has text. To resolve this, you could delete the cell's contents and retype the value of 1865.00. Or you could also use the CLEAN function to clean out characters, or use the REPLACE function to replace special characters with other values.

After using CLEAN or REPLACE, you'll want to copy the result, and use Home > Paste > Paste Special > Values. You might also have to convert numbers stored as text to numbers.

Formulas with math operations like + and * might not be able to calculate cells that contain text or spaces. In this case, try using a function instead. Functions often ignore text values and calculate everything as numbers, eliminating the #VALUE! error. For example, instead of =A2+B2+C2, type =SUM(A2:C2). Or, instead of =A2*B2, type =PRODUCT(A2,B2).

Other solutions to try

Select the error

Cell H4 with formula =E2+E3+E4+E5 and result of #VALUE!

First select the cell with the #VALUE! error.

Click Formulas > Evaluate Formula

Evaluate formula dialog with " "+E3+E4+E5

Select Formulas > Evaluate Formula > Evaluate. Excel steps through the parts of the formula individually. In this case the formula =E2+E3+E4+E5 breaks because of a hidden space in cell E2. You can't see the space by looking at cell E2. However, you can see it here. It shows as " ".

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 checks to see if there’s an error, and if so, replaces it with another value of your choice. If there isn’t an error, your original formula is calculated. IFERROR only works in Excel 2007 and later. For earlier versions you can use IF(ISERROR()).

Warning: IFERROR hides all errors, not just the #VALUE! error. Hiding errors isn't recommended because an error is often a sign that something needs to be fixed, not hidden. We don't recommend using this function unless you're absolutely certain your formula works the way that you want.

Cell with #VALUE!

Cell H4 with =E2+E3+E4+E5 and result of #VALUE!

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

Error hidden by IFERROR

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

And here’s the same formula with IFERROR added to the formula. You can read the formula as: "Calculate the formula, but if there's any kind of error, replace it with two dashes." Note that you could also use "" to display nothing instead of two dashes. Or you could substitute your own text, such as: "Total 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.

Your data connection could 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 have only 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 select 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

Need more help?

Want more options?

Explore subscription benefits, browse training courses, learn how to secure your device, and more.

Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge.

Was this information helpful?

What affected your experience?
By pressing submit, your feedback will be used to improve Microsoft products and services. Your IT admin will be able to collect this data. Privacy Statement.

Thank you for your feedback!

×