Read time 6 minutes
In Excel 2016, the #VALUE! error appears when you enter a value in the wrong way in a cell.
Excel offers a wide range of functions, but when a function receives an unexpected value, it displays the #VALUE! Error. Basic addition and subtraction operations using the (+) or (-) operator will trigger a #VALUE! error if any of the values are not of the expected type. However, it’s worth noting that the behavior of the #VALUE! error can be somewhat tricky for certain functions, as they may automatically ignore invalid data.
Let us consider some of the most common examples of #VALUE! error along with the solutions to resolve.
- Unexpected text value
While performing a regular addition, if one of the cells contains a text instead of a numeric value, Excel will return a #VALUE! error.Consider the example below, where Cell C2 contains a text value ‘Null’ while Cell B1 contains a numeric value ‘258’. When formulated “= C1 + B1”, D1 returns the #VALUE! error.
A common solution to this issue is to correct the invalid values. However, when dealing with extensive spreadsheets, manually correcting each data point can be time-consuming and labor-intensive. In such cases, one approach is to employ the SUM function to eliminate the #VALUE! errors.However, the SUM function ignores the invalid value as shown below:
- Irrelevant Space character(s)
At times, users may delete values by pressing the spacebar key, either intentionally or inadvertently. This action makes the cell appear empty, but in reality, it still contains a space character.Consider the example below, where C2 cell is left blank, and cell B2 contains a numeric value (522). When formulated “=C2/B2”, D2 returns a #DIV/0! error, which indicates division by zero.
Now, if the same cell C2 contains errant space characters, D2 will throw a #VALUE! error.
Users can look out for errant space characters by pressing the F2 key within the cell to note the blinking cursor, as shown in the image below.
If the cursor seems to be a couple of millimeters away from the adjacent border of the cell, press the Delete key.
However, while working on large spreadsheets, you can use the ISBLANK function to determine whether a cell is blank or not. The function will return TRUE if the cell is blank or FALSE if it’s not.
- Function argument is not valid
Invalid inputs as function arguments can also result in a #VALUE! Error. For example, consider the NETWORKDAYS function, which is employed to calculate the total number of workdays between two dates. If the inputs provided to this function are invalid or incompatible, it can trigger a #VALUE! Error.dates. If one of the function arguments is not the expected type, Excel will throw a #VALUE! Error.
In the example below, cell C2 contains the start date (6/2/2017), and cell C3 contains an invalid value “August.” As a consequent, cell C5 returns #VALUE! Error.
Enter the correct value as a date to get the desired result.
For Excel functions that take multiple ranges as arguments, those ranges must have the same size and shape. Formulas that fail to meet this requirement raise the #VALUE error.
An array argument with incompatible dimensions results in a #VALUE error with the dynamic array FILTER function. For eg:
=FILTER(A3:B20, A3:A22=”Apple”)
After the range references are altered, the error must be resolved:
=FILTER(A3:B20, A3:A20=”Apple”)
Excel typically stores dates as numeric values. There may be instances, however, where your worksheet stores dates in the form of text strings instead. This will result in Excel returning the #VALUE! If you try to compute these dates, you will receive an error because text values cannot be added, subtracted, or otherwise computed.
To resolve this issue, you must convert text-formatted dates into valid Excel dates.
#VALUE errors could also be caused by syntax errors in formulas. You can resolve such issues by using Excel’s Formula Auditing tools.
Identify the cell that has the #VALUE error.
To evaluate a formula or check for errors, click on Evaluate Formula or Error Checking in the Formula Auditing group.
Excel will step through each section of the formula one by one, showing the result at each stage. Excel highlights the specific part of a formula that contains a syntax error. After detecting the syntax error, fix it and re-evaluate the formula to ensure it works now.
For eg., look at the formula below:
=SORT(CHOOSECOLS(A3:B20, 3))
With the last argument switched to 2, the desired result is returned – the Total column sorted from smallest to largest:
You can use Excel 2007 to 365’s IFERROR function or its IF ISERROR combination to eliminate the #VALUE error.
Let’s say you need to find the difference between B3 and C3 by using the DATEDIF formula:
=DATEDIF(B3, C3, “d”)
If one or both the dates are invalid, it will show a #VALUE error. To resolve it, make your core formula in the IFERROR function as:
=IFERROR(DATEDIF(B3, C3, “d”), “Invalid date!”)
Excel will explicitly point out the IFERROR function if it does not recognize the referenced cell value as a date.
When dealing with extensive spreadsheets, you may encounter Excel corruption problems in addition to issues like the #VALUE! Error. While there are manual methods available to recover corrupted Excel 2016 files, it is often more efficient to utilize a reliable third-party tool to address Excel error codes. Manual Excel recovery methods can be time-consuming and complex.
Kernel for Excel Repair stands as a robust solution for rescuing your corrupted Excel files in the wake of Excel errors. Equipped with advanced algorithms, it offers a comprehensive remedy for all your Excel-related challenges. Additionally, you can access a free trial version of the software to experience its functionality. Let’s delve into how this tool operates.
- Launch the tool and click on the Browse button to navigate to the folder containing the corrupt Excel file.
- Click Repair to repair the corrupt Excel file or to fix the error codes.
- Wait while the process gets complete.
- Click on the Save button to save the recovered file.
- Click Select Path to choose a destination folder and then click on the Save button.
- The Excel file gets saved at the specified location.
Conclusion
While you can resolve a #Value error through straightforward manual methods, certain Excel errors prove to be elusive and challenging to rectify. Furthermore, although there exist multiple approaches to recover corrupted Excel files, employing the Kernel for Excel Repair tool is undoubtedly the optimal and highly endorsed method for addressing errors and restoring compromised Excel files.