#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
-
try another solution
Fix bugs for some features
- Which mode are you using?
- Average
- in a row
- COUNTIF, COUNTIF
- date value
- the
- find, find database
- I
- index, match
- search, search B
- I
- SUMIF, SUMIFS
- sum of products
- time value
- transfer
- VLOOKUP
- * none of the above
For more information visitExact value! AVERAGE or SUM function error
For more information visitExact value! Error in CONCATENATE function
For more information visitExact value! COUNTIF/COUNTIFS function error
For more information visitExact value! Error in DATEVALUE function
For more information visitExact value! DAYS function error
For more information visitExact value! Error in FIND/FINDB and SEARCH/SEARCHB functions
For more information visitExact value! IF operation error
For more information visitExact value! Error in INDEX and MATCH functions
For more information visitExact value! Error in FIND/FINDB and SEARCH/SEARCHB functions
For more information visitExact value! AVERAGE or SUM function error
For more information visitExact value! SUMIF/SUMIFS function error
For more information visitExact value! Error in SUMPRODUCT function
For more information visitExact value! Error in TIMEVALUE function
For more information visitExact value! TRANSPOSE function error
For more information visitExact 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 mistyped the subtraction type. There are two methods here:
Subtract from the second cell reference
Enter two values in two separate cells. In the third cell, remove the cell reference from the second cell. 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 the budget amount and cell E6 has the actual amount as a negative number. F6 has a formula=sum(D6,E6).
If you're using Windows, you might get #VALUE! Mistakes can happen even when performing the most basic type of subtraction. 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! Report an error and proceed to the next step. If you don't get the error, 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.
-
-
existsFormtab, clickother settings.
-
I am lookinglist separator.If the list separator is set to a minus sign, change it to something else. For example, a comma is a common list separator. 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 #VALUE! Error, double click to edit.
-
If there are any commas where the subtraction should have a minus sign, change them to a minus sign.
-
Press Enter.
-
Repeat this process for the other cells with errors.
Subtract from the second cell reference
Enter two dates in two separate cells. In the third cell, remove the cell reference from the second cell. 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 it's left aligned, that means the date isn't really a date. is the text. Excel does not recognize text as dates. Here are some solutions that can 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 battery has this problem, proceed to the next step. If you don't see one or more spaces, go to the next section to check your computer's date settings.
-
Select the column containing the dates by clicking on the column header.
-
clickdata>text in a column.
-
clickNEXTtwice.
-
On the wizard's 3rd step, ucolumn data format, Clickdatum.
-
Select a date format, then clickFinish it.
-
Repeat this process for the other columns to make sure they don't 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 the actual date from the date stored as text. Assuming your computer's date system is mm/dd/yyyy and your text dates are31.12.2017In cell A1:
-
Create a formula like this:=Date(right(A1,4),middle(A1,4,2),left(A1,2))
-
The result will be31.12.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 be31.12.2017This will be an actual date, not a text date.
+1 on Mac).notes:The formula above is useddatum,rectify,the middle, MeleftMode. Note that it is written assuming that a date literal has two day characters, two month characters, and four year characters. You may need to adjust the formula to fit your date.
Space and text issues
Often #VALUE! It happens because your formula refers to other cells that contain spaces, and it's even more complicated: hidden spaces. These spaces can form a cellLookpermission, but in fact it isYesEmpty.
1. Select the indicated cell
Find the cells that refer to your formula and select them. In many cases, it is good practice to remove spaces for an entire column, since you can replace more than one space at a time. In this example, click onButSelect the entire column.
2. Find and replace
existsDomtab, clickfind and select>replace.
3. Replace spaces with spaces
from the insidewhat to look forfield, type a blank. Then insideuse. . . replacebox, erasing anything that might be there.
4. Replace or Replace All
If you are sure that all spaces in the column should be removed, clickreplace everything.If you want to go through one by one and replace spaces with spaces, you can clickfind the next onefirst and then clickreplaceWhen you're sure you don't need the space. When you're done, #VALUE! Errors can be corrected. 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 from the columns, go toDom>sorting and filtering>blankets.
6. Install the filter
Click the filter arrow
, then deselectSelect all.then selectemptycontrol box.
7. Select any untitled check box
Select any checkbox that has nothing next to it, like this one.
8. Select an empty cell and delete it
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...This way, all cells are visible.
10. Results
If the gaps are to blame for your #VALUE! error Then 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 referenced by your formula. 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 that numbers are 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! errors. But sometimes it's hard to see which cells have these problems. Solution: useISTEXT functionCheck the cells. Note that ISTEXT does not resolve the error, it only finds the cell that may have caused the error.
Example #VALUE!
Here's an example of a formula that contains #VALUE!. error. This is probably due to the E2 cell. After the "00" there is 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 functionis added to column F. All cells are OK except the one with the value TRUE. 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 also 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 might need it tooconverts 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 a function. Functions usually ignore text values and evaluate everything as numbers, eliminating the #VALUE! error. For example, instead of=A2+B2+C2, hint=zbroj(A2:C2).or, instead of=A2*B2, hint=proizvod(A2,B2).
try another solution
wrong choice
First select the cell with #VALUE!! error.
Click Types > Calculation Type
clickofficially>Scoring type>assessmentExcel will go through each part of the formula one at a time. In this case the guy=E2+E3+E4+E5Breaks due to hidden space in cell E2. Look at cell E2 so you don't see a blank. However, you can see it here. appears as"".
Sometimes you just want to replace the #VALUE! The error refers to your own text, zero or empty cells, etc. In this case you can addIFERROR functionyour formula. IFERROR will check for errors and, if there are any, replace them 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 (error()).
to warn:IFERROR will hide all errors, not just #VALUE! error. Hiding mistakes is not recommended because mistakes usually indicate that something needs to be fixed, not hidden. We do not recommend using this feature unless you are absolutely sure that your formulas work as you want them to.
Cells with #VALUE!
Here's an example of a formula that contains #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 substitute your own text, for example:"completely wrong".
Unfortunately, as you can see, IFERROR doesn't actually resolve the error, it just hides it. So make sure that hiding a mistake is better than correcting it.
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 only the values. To paste as value only, I can clickDom>Paste>special paste>values.This removes all types and joins, so any #VALUE will be removed as well! error.
If you are not sure what to do right now, you can findExcel 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 the formula