How to Fix Value Error in Excel Quickly, 100% Effectively

The value error in Excel is a very common error that surely those who often use functions to calculate have encountered once. Many people who encounter this error for the first time are confused as to what this error is and how to fix it. In the article below, Tipsmake will guide you how to fix this error in the most detailed and simple way.

I. What is #value error in excel?

The value error in Excel is an error that easily occurs if you enter the wrong formula or the value in the cell you refer to has a problem.

The cause of this error is not fixed and it is difficult to verify the cause. Basically, the common causes of #value errors in Excel are:

1. Contains special characters in value cells
2. There is a space between the values
3. ….

For each of the above reasons, you will have different solutions. Below, Ben Computer will provide specific instructions on how to fix each cause.

II. Instructions on how to fix value errors in Excel simply

1. How to fix errors caused by special characters

How to Fix Value Error in Excel Quickly, 100% Effectively Picture 1

If there are any special characters in the referenced cell, it will cause the calculation in Excel to fail and the Value error message. To fix, you need to remove those characters. If the strange character is easy to recognize and the number of value cells is small, it can be checked manually. However, if the number of value cells is too much and it is difficult to find strange characters, you can do it in the following way:

Step 1: Create an extra column next to the column with the data to be checked. Enter the ISTEXT function formula (function to check strange characters in cells) into the cell in the newly created column corresponding to the first cell of the column that needs to be checked.

For example, the cell that needs to be checked is B2, enter =ISTEXT(B2)

How to Fix Value Error in Excel Quickly, 100% Effectively Picture 2

Step 2: In other cells in the column, copy and fill in the same formula as the first cell.

Then, you will see the results returned in the True and False boxes.

1. True: cell contains strange characters
2. False: cell does not contain strange characters

How to Fix Value Error in Excel Quickly, 100% Effectively Picture 3

Step 3: Then, please check and delete the strange characters in the True box until all the cells return False.

How to Fix Value Error in Excel Quickly, 100% Effectively Picture 4

Step 4: Delete the extra column at first, the result will not report Value anymore.

How to Fix Value Error in Excel Quickly, 100% Effectively Picture 5

2. How to fix value error in Excel due to space between values

Gaps between values ​​are the most difficult to identify among the causes. Because with the naked eye, it will be difficult for people to recognize where the excess space is. To delete unnecessary spaces, you can do it in two ways:

2.1. Use the search and replace tool to remove spaces

How to Fix Value Error in Excel Quickly, 100% Effectively Picture 6

Step 1: Select the cells to be referenced => press Ctrl + H

Step 2: After the Find and replace window appears, open the replace tab, in the find what box, press a space once. As for the replace with section, leave it as is => select Replace all.

How to Fix Value Error in Excel Quickly, 100% Effectively Picture 7

2.2. Use a filter to filter out the gaps

Step 1: Select the cells you want to filter to remove the space => On the menu bar, select Data => select Filter.

How to Fix Value Error in Excel Quickly, 100% Effectively Picture 8

Step 2: In the first cell of the column where the filter has been set, select expand the filter => select only the Blank box => select Ok.

How to Fix Value Error in Excel Quickly, 100% Effectively Picture 9

Step 3: After filtering, the screen will display cells containing spaces => select all those cells and press the Delete button on the keyboard.

How to Fix Value Error in Excel Quickly, 100% Effectively Picture 10

Step 4: After deleting all the empty cells, you choose to expand the filter again => select Clear Filter From… => ​​Ok. 

How to Fix Value Error in Excel Quickly, 100% Effectively Picture 11

Step 5: Turn off the filter, the screen will display as below, the result is calculated correctly and there is no error.

How to Fix Value Error in Excel Quickly, 100% Effectively Picture 12

Above is a guide on how to fix Value errors in Excel extremely simply and quickly. Hope you are succesful.

Samuel Daniel

Update 04 December 2021