How to fix Value errors in Excel

Working with Excel sometimes you will encounter #VALUE! but you guys don't know how to fix this. There are many causes of #VALUE! Error, below the article will give some of the most common Value errors in Excel and how to fix those errors, please refer.

How to fix Value errors in Excel Picture 1

Error #VALUE with spaces

Many times, your formula involves cells that contain hidden spaces, which appear to be blank, but are not white in reality, so #VALUE!

How to fix Value errors in Excel Picture 2

To fix this problem, you can remove these hidden spaces as follows:

Step 1 : Select the referenced cells, then press Ctrl + H to open the replacement dialog box.

How to fix Value errors in Excel Picture 3

Step 2: In the Find and Replace dialog box, enter a space in the Find what box and in Replace with you do not enter anything. Then click Replace All to remove all spaces.

How to fix Value errors in Excel Picture 4

Many times, there are hidden characters other than spaces that make the field look blank but actually it is not empty.To remove these characters, you can continue with the next steps.

Step 3: On the area you have selected you select Data -> Filter to open the filter.

How to fix Value errors in Excel Picture 5

Step 4 : Select the filter icon on the column header and uncheck the Select All box , then check in the Blanks checkbox or the checkbox has no content.

How to fix Value errors in Excel Picture 6

Step 5 : The filter results appear, select the blank cells and press the Delete key . So you deleted all the hidden characters in the cells.

How to fix Value errors in Excel Picture 7

Step 6 : Clear the filter by clicking the filter icon on the title box and selecting Clear Filter From .

How to fix Value errors in Excel Picture 8

So you will get the following results:

How to fix Value errors in Excel Picture 9

Error #VALUE! when there is text or special characters

If a cell contains text or special characters, an #VALUE! Error will be generated. but it's hard to know which cell contains text or special characters if the data list is very long.

How to fix Value errors in Excel Picture 10

To search for cells containing text or special characters you can fix the #VALUE! then you use the ISTEXT function to check the cells.

Step 1 : Create 1 more column next to the data column you need to check, in the first cell of the column you enter the formula = ISTEXT (C6)

How to fix Value errors in Excel Picture 11

Step 2 : Copy the formula down all remaining cells, ISTEXT () will return TRUE if the cell contains text or special characters, return FALSE if the cell does not contain text or characters especially.

How to fix Value errors in Excel Picture 12

Step 3 : Next, you select the cells containing TRUE and delete the text or special characters, the function will no longer #VALUE!

How to fix Value errors in Excel Picture 13

You also need to delete the column you created in step 1, and the result will be no #VALUE! Error.

How to fix Value errors in Excel Picture 14

Value error occurs when using the numeric operators (+, *)

For example, you use the + operator to add the sales of two days without a sales day. You will get the #VALUE error.

How to fix Value errors in Excel Picture 15

To fix this, replace the + operator with the SUM function formula, the SUM function will ignore the string and return the result.

How to fix Value errors in Excel Picture 16

Similar to the * operator, if you have a #VALUE error, you can replace it with the PRODUCT function to multiply in Excel.

Value error occurs when using the Average, Sum function

If the average or sum contains the #Value! then the result of Average, Sum will also return the #VALUE error.

How to fix Value errors in Excel Picture 17

To fix this, you can use an array formula in combination with two groups of functions: If + Isseror or Iferror .

1. SUM function

Method 1

{= SUM (IFERROR (C6: C8; ""))}

Because this is an array formula, once you have entered the SUM function, press Ctrl + Shift + Enter to display the curly braces.

How to fix Value errors in Excel Picture 18

Method 2

{= SUM (IF (ISERROR (C6: C8); ""; C6: C8))}

The ISERROR function will determine if any value in the range C6: C8 is an error value, it will return a blank result, so the values ​​for the final calculation SUM will remove all error values.

How to fix Value errors in Excel Picture 19

1. AVERAGE function

Similar to the SUM function, the AVERAGE function you also use the array formula IF and ISERROR.

Method 1

{= AVERAGE (IFERROR (C6: C8; ""))}

How to fix Value errors in Excel Picture 20

Method 2

{= AVERAGE (IF (ISERROR (C6: C8); ""; C6: C8))}

How to fix Value errors in Excel Picture 21

Similar to some other functions (IF, CONCATENATE .) you get #VALUE! When referring to error values, you can also handle errors by using the IF, ISERROR and ISERR or IFERROR functions in the formula.

Above the article has taught you some ways to fix Value errors in Excel, hope you will be able to fix Value errors when you encounter this error. Good luck!