If you’ve landed on this page, we assume you’ve come across the same issue. Don’t worry; you haven’t messed up your calculations. This article will guide you in learning your way around the #VALUE! error in Excel. So keep reading this article to learn how you can fix this issue and avoid it the next time.
How do You Get the #VALUE! Error?
How to Fix the #VALUE! Error in Excel?
There are general to specific solutions for the #VALUE! error. If you suspect a certain cause for the #VALUE! error, you can look go ahead and try specific solutions for it. If not, you may try all relevant fixes.
Hyphen as a List SeparatorValue Contains TextDate Stored as TextSpace or Hidden Characters in Cell
Change List Separator
You may run into this issue while performing subtraction on Excel in Windows. You may have set the list separator as a hyphen (-). Excel will recognize the symbol as a separator and not an operator. You can check this theory out by performing a basic subtraction in Excel.You will have to change the symbol for the list separator on your Windows for Excel to recognize the hyphen (-) as an operator for subtraction and not a separator. This should fix the fix #VALUE! error in Excel. Here are the steps you can take to change the list separator from the Control panel:
Use Ampersand
Even if you have formatted the contents of your cell as ‘Number’, you cannot operate them if they include texts. Operations like subtraction, addition, multiplication, and division are limited only to numbers. If your intention is to combine the values of the selected cells, you can use the Ampersand operator. The Ampersand will combine the data in the set cells into a single cell. Here are the steps to execute the Ampersand operator to combine texts:The formula should look something like this to combine your strings:=A2&B2Besides Ampersand, you can also use the CONCAT, and CONCATENATE functions to combine data in Excel.
Fix Date Stored as Text
Although Excel allows you to perform calculations on dates, it may be recognizing your data as text. As already established, you are not allowed to perform calculations on text. If your date is aligned to the left, Excel is viewing your date as text. You can try to change the format to Date from the Text to Column tool from the Data ribbon on Excel. Follow these steps to change the format for the selected cell in your workbook:On your workbook, select the cell or the row you want to convert. Remember, you can convert multiple cells in a single row but not columns.
Change Date Formats
Excel may also view your date as text if the date you’ve entered does not match your computer’s date format. For example, if your computer settings has the date format as DD/MM/YYYY and you entered the date as YYYY/MM/DD, Excel will recognize it as text.You can change the date format for your Excel to match the date settings of your computer. Follow these steps to change the date format in Excel:
Remove spaces
Excel registers the space as a value. If the cells you want to manipulate include space, Excel will display the #VALUE! error. This also includes the hidden spaces that make the cell look blank. For data manipulation, you’ll need to get rid of spaces and hidden characters. You can use the Find & Replace to locate and remove the cells in your cells. Follow these steps to use the Find & Replace tool to remove spaces in your workbook.
Use Sort & Filter
It is not only ‘space’ that can cause your cells to appear blank. These hidden characters will cause issues while performing your calculations. You can use the Sort & Filter tool to locate and remove such characters from your column. Through this tool, you can select the hidden characters and spaces and delete them. Here are the steps to turn on the filter for your workbook in Excel:
