How to fix #VALUE! error (2023)

• 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

If you are new to Excel, you may have entered the subtraction type incorrectly. There are two ways to do this:

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:

1. 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.

2. 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.

3. higherFormtab, clickother settings.

4. 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.

5. clickWell.

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

7. If there is a comma in the subtraction where a minus sign should be, change it to a minus sign.

8. Press Enter.

9. Repeat this process for the other cells with errors.

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

1. Double-click the date used in the subtraction type.

2. 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.

3. Select the column containing the dates by clicking on the column header.

4. clickdata>text in a column.

5. clickNEXTtwice.

6. Under step 3 of 3 in the guide,column data format, Clickdatum.

7. Select a date format, then clickFinish it.

8. 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:

1. Create a formula like this:=Date(right(A1,4),middle(A1,4,2),left(A1,2))

2. The result will beDecember 31, 2017.

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

4. 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.

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.

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.

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.

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.

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.

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