#VALUE is an Excel expression,"There is a problem with the way you entered the formula. Or, there is a problem with the cell you are referring to."This error is so common that it is difficult to find its exact cause. The information on this page shows solutions to common problems and errors.
Use the drop down menu below or go to one of the other areas:
-
subtraction problem
-
Space and text issues
-
Other solutions you should try
Fix bugs for specific features
- Which mode are you using?
- Average
- I'm connecting
- count, count
- date value
- the
- find, find database
- I
- index, match
- search, search for HB
- I
- sum function, sum function
- they disappeared
- time value
- transfer
- search table
- * none of the above
More information:Exact value! Error in AVERAGE or SUM function
More information:Exact value! Error in CONCATENATE function
More information:Exact value! Error in COUNTIF/COUNTIFS function
More information:Exact value! Error in DATEVALUE function
More information:Exact value! Error in DAYS function
More information:Exact value! Error in FIND/FINDB and SEARCH/SEARCHB functions
More information:Exact value! IF operation error
More information:Exact value! Error in INDEX and MATCH functions
More information:Exact value! Error in FIND/FINDB and SEARCH/SEARCHB functions
More information:Exact value! Error in AVERAGE or SUM function
More information:Exact value! Error in SUMIF/SUMIFS functions
More information:Exact value! Error in SUMPRODUCT function
More information:Exact value! Error in TIMEVALUE function
More information:Exact value! Error in TRANSPOSE function
More information:Exact value! Error in VLOOKUP function
Don't see your feature on this list? Try the other solutions listed below.
subtraction problem
If you are new to Excel, you may have entered the subtraction type incorrectly. There are two ways to do this:
Remove one cell reference from another
Enter two values in two separate cells. In the third cell, subtract one cell reference from the other. In this example, cell D2 has the budget amount and cell E2 has the actual amount. F2 has a type=D2-E2.
Alternatively, use SUM for positive and negative numbers
Enter a positive value in one cell and a negative value in another. In the third cell, use the SUM function to add the two cells. In this example, cell D6 has a budgeted amount and cell E6 has a negative actual amount. F6 has a formula=SUM(D6,E6).
If you're using Windows, you may have received #VALUE! Even the most basic type of removal can go wrong. The following may solve your problem:
-
Do a quick test first. In the new workbook, type 2 in cell A1. Type 4 in cell B1. Then enter this formula in C1=B1-A1. If you get #VALUE! Error, go to next step. If you don't get the error message, try the other solutions on this page.
-
In Windows, open the Regional Control Panel.
-
Windows 10: Clickprinciple, hintterrainand then clickterrainChecklist.
-
Windows 8: On the Start screen, typeterrain, Clickseriesand then clickterrain.
-
Windows 7: Clickprinciplethen typeterrainand then clickregion and language.
-
-
higherFormtab, clickother settings.
-
I am lookinglist separator. If the list separator is set to a minus sign, change it to another sign. For example, commas are common list separators. The question mark is also common. However, some other list separator may be more appropriate for your locale.
-
clickWell.
-
Open your workbook. If a cell contains a #VALUE! error, double-click to edit it.
-
If there is a comma in the subtraction where a minus sign should be, change it to a minus sign.
-
Press Enter.
-
Repeat this process for the other cells with errors.
Remove one cell reference from another
Enter two dates in two separate cells. In the third cell, subtract one cell reference from the other. In this example, cell D10 has a start date and cell E10 has an end date. F10 has a formula=E10-D10.
Alternatively, use the DATEDIF function
Enter two dates in two separate cells. In the third cell, use the DATEDIF function to find the date difference. For more information about the DATEDIF function, seeCalculate the difference between two dates.
Expand the date column. If your date is lined up correctly, then it's a date. But if left aligned, that means the date isn't really a date. This is the text. Excel does not recognize text as dates. Here are some solutions that can help solve this problem.
Check the leading blanks
-
Double-click the date used in the subtraction type.
-
Place the cursor at the beginning and see if one or more parts can be selected. This is what the selected space at the beginning of the cell looks like:
If your phone is experiencing this problem, proceed to the next step. If you don't see one or more spaces, skip to the next section on checking your computer's date settings.
-
Select the column containing the dates by clicking on the column header.
-
clickdata>text in a column.
-
clickNEXTtwice.
-
Under step 3 of 3 in the guide,column data format, Clickdatum.
-
Select a date format, then clickFinish it.
-
Repeat this process for the other columns, making sure they do not contain spaces before the date.
Check the date settings on your computer
Excel uses your computer's date system. If the cell date is not entered using the same date system, Excel will not recognize it as a real date.
For example, suppose your computer displays dates as mm/dd/yyyy. If you type such a date into a cell, Excel recognizes it as a date and you can use it in a subtraction formula. However, if you type a date such as dd/mm/yy, Excel will not recognize it as a date. Instead, it treats it as text.
There are two solutions to this problem: You can change the date system used by your computer to match the date system you want to import into Excel. Alternatively, you can create a new column in Excel and use the DATE function to create actual dates from dates stored as text. Assuming your computer's date system is mm/dd/yyyy and your text dates areDecember 31, 2017In cell A1:
-
Create a formula like this:=Date(right(A1,4),middle(A1,4,2),left(A1,2))
-
The result will beDecember 31, 2017.
-
If you want the format to appear as dd/mm/yy, press CTRL+1 (or
+1 on Mac).
-
Select a different locale using the dd/mm/yy format, for exampleEnglish (UK). After applying the formatting, the result will beDecember 31, 2017This will be an actual date, not a text date.
notes:The above formula is written asdatum,rectify,the middle, MeleftMode. Note that it is written assuming that a literal date has two characters for the day, two for the month, and four for the year. You may need to adjust the formula to fit your dates.
Space and text issues
Usually #VALUE! This is because your formula refers to other cells that contain spaces, and it's even more complicated: hidden spaces. These spaces can make up a cellLookpermission, but in fact it isYesEmpty.
1. Select the indicated cell
Find the cells that the formula refers to and select them. In many cases, it is good practice to remove spaces for an entire column, as you can replace many spaces at once. In this example, click onButSelect the entire column.
2. Find and replace
higherDomtab, clickfind and select>replace.
3. Replace spaces with nothing
from the insidewhat to look forfield, type a blank. Then insideuse. . . replacebox, delete anything that might be there.
4. Replace or replace everything
If you are sure that all spaces in the column should be removed, clickreplace everything. If you want to go through and replace spaces with spaces, you can clickfind the next onefirst and then clickreplaceWhen you are sure that the space is not needed. After completion, the error #VALUE! can be solved. If not, go to the next step.
5. Activate the filter
Sometimes there are hidden characters other than spaces that can make up a cellwill appearempty when notrealEmpty. A single apostrophe within a cell does the trick. To remove these characters in a column, enable the filter via:Dom>sorting and filtering>blankets.
6. Adjust the filter
Click the filter arrow, then deselectSelect all. Then chooseemptycontrol box.
7. Select any untitled check box
Select a box with nothing next to it, like this one.
8. Select an empty cell and delete
When Excel returns empty cells, select them. Then press the delete key. This will delete all hidden characters in the cell.
9. Clean the filter
Click the filter arrowand then clickclean the filter...so that all cells are visible.
10. Results
If the gaps are to blame for your #VALUE! error, hopefully your error has been replaced by the result of the formula, as in our example. If not, repeat this process for the other cells that the formula refers to. Alternatively, try the other solutions on this page.
notes:In this example, notice that cell E4 has a green triangle and the number is left aligned. This means the number is stored as text. This can cause more problems later. If you see this problem, we suggest youconverts a number stored as text to a number.
Text or special characters inside cells can cause #VALUE! error. But sometimes it's hard to see which cells have these problems. Solution: useISTEXT functionCheck the cells. Note that ISTEXT does not handle errors, it only finds the cell that may have caused the error.
Take #VALUE for example!
Here's an example of a formula with #VALUE! error. This is probably due to the E2 cell. "00" is followed by a special character, shown as a small box. Or as shown below, you can use the ISTEXT function on a separate column to check the text.
Same example, using ISTEXT
hereISTEXT functionadded to column F. All cells are OK except the one with TRUE value. This means that cell E2 has text. To fix this, you can delete the contents of the cell and retype the value 1865.00. or you can usecleaning functionto delete characters or usefunction replacementReplace special characters with other values.
After using CLEAN or REPLACE , you must copy the results and then use themHome > Paste > Paste Special > Prices. you may also needconverts a number stored as text to a number.
Formulas with math operations like + and * may not evaluate cells that contain text or spaces. In this case, try using the function. Functions generally ignore text values and evaluate everything as numbers, eliminating the #VALUE! error. For example, instead of=A2+B2+C2, hint=SUM(A2:C2). or, instead of=A2*B2, hint=proizvod(A2,B2).
Other solutions you should try
wrong choice
First select the cell with #VALUE! error.
Click Types > Calculation Type
clickofficially>Scoring type>assessment. Excel will proceed through each part of the formula separately. In this case the guy=E2+E3+E4+E5Breaks due to hidden space in cell E2. You cannot see this interval by looking at cell E2. However, you can see it here. appears as"".
Sometimes you just want to replace the #VALUE! Other content, such as your own text, null or empty cells have errors. In this case you can adderror modeyour formula. IFERROR will check for errors and if there are any, replace it with another value of your choice. Your original formula will be calculated if there are no errors. IFERROR is only available in Excel 2007 and later. For older versions you can useIF(ISError()).
to warn:IFERROR will hide all errors, not just #VALUE! error. Hiding mistakes is not recommended because mistakes are usually a sign that something needs to be fixed, not hidden. We do not recommend using this function unless you are absolutely sure that your type works as you want.
A cell with #VALUE!
Here's an example of a formula with #VALUE! Error due to hidden space in cell E2.
IFERROR hidden errors
Here is the same formula with IFERROR added to the formula. You can read the formula like this:"Calculate the formula, but if there is an error, replace it with two dashes."Note that you can also use””It shows nothing but two dashes. Or you can replace the text with your own, for example:"completely wrong".
Unfortunately, as you can see, IFERROR doesn't actually resolve the error, it just hides it. Of course, hiding mistakes is better than correcting them.
Your data connection may be unavailable at times. To resolve this issue, reset your data connection or consider importing data if possible. If you do not have access to the link, ask the workbook creator to create a new file for you. Ideally, the new file will have just the values, no merges. I can do this by copying all the cells and pasting them as values only. To paste as value only, I can clickDom>Paste>special paste>values. This eliminates all types and joins, so the #VALUE! error.
If you are not sure what to do now, you can search for similar questions atExcel Community Forums, or post one of your own.
Ask a question in the Excel Community Forum
See also
Overview of formulas in Excel
How to avoid breaking formulas