How do you fix the value you entered is not valid in Excel?
|
The #VALUE! error appears when a value is not the expected type. This can occur when cells are left blank, when a function that is expecting a number is given a text value, and when dates are evaluated as text by Excel. Fixing a #VALUE! error is usually just a matter of entering the right kind of value. Show
The #VALUE error is a bit tricky because some functions automatically ignore invalid data. For example, the SUM function just ignores text values, but regular addition or subtraction with the plus (+) or minus (-) operator will return a #VALUE! error if any values are text. The examples below show formulas that return the #VALUE error, along with options to resolve. Example #1 - unexpected text valueIn the example below, cell C3 contains the text "NA", and F2 returns the #VALUE! error: =C3+C4 // returns #VALUE!  One option to fix is to enter the missing value in C3. The formula in F3 then works correctly: Another option in this case is to switch to the SUM function. The SUM function automatically ignores text values: =SUM(C3,C4) // returns 4.5 Example #2 - errant space character(s)Sometimes a cell with one or more errant space characters will throw a #VALUE! error, as seen in the screen below: Notice C3 looks completely empty. However, if C3 is selected, it is possible to see the cursor sits just a bit to the right of a single space: Excel returns the #VALUE! error because a space character is text, so it is actually just another case of Example #1 above. To fix this error, make sure the cell is empty by selecting the cell, and pressing the Delete key. Note: if you have trouble determing whether a cell is truly empty or not, use the ISBLANK function or LEN function to test. Example #3 - function argument not expected typeThe #VALUE! error can also arise when function arguments are not expected types. In the example below, the NETWORKDAYS function is set up to calculate the number of workdays between two dates. In cell C3, "apple" is not a valid date, so the NETWORKDAYS function can't compute working days and returns the #VALUE! error: Below, when proper date is entered in C3, the formula works as expected: Example #4 - dates stored as textSometimes a worksheet will contain dates that are invalid because they are stored as text. In the example below, the EDATE function is used to calculate an expiration date three months after a purchase date. The formula in C3 returns the #VALUE! error because the date in B3 is stored as text (i.e. not properly recognized as a date): When the date in B3 is fixed, the error is resolved: If you have to fix many dates stored as text, this page provides some options for fixing. How do you make a value valid in Excel?Add data validation to a cell or a range. Select one or more cells to validate.. On the Data tab, in the Data Tools group, click Data Validation.. On the Settings tab, in the Allow box, select List.. In the Source box, type your list values, separated by commas. ... . Make sure that the In-cell dropdown check box is selected.. What to do when Excel keeps Says reference not valid?One of the greatest reasons why data source reference isn't valid Excel error appears is the presence of “['or']”. In that case, you simply need to rename the file name and remove the brackets. It might be possible that you have not saved the pivot table on your local drive.
How do you fix a user has restricted values that can be entered into this cell?Go to the Data Ribbon Menu and select Data > Data Validation. This will bring up the Data Validation Settings Menu. Click Clear All to remove the data restrictions in this range of cells. Then click OK to apply the new settings.
|
