Formula Errors in Excel

MS Excel provides complete support to its users for an excellent computing experience. Therefore, it also has a well-crafted error handling method in place. This tutorial will be covering frequently occurring formula errors which you may encounter while using MS Excel.

##### ERROR

When you encounter a situation where your cell display #### signs instead of the actual values, it means your column width is not appropriate and you need to expand. The error is shown in the figure below.

Now, to resolve this issue, click on the right border of the column A header as shown in the above figure, and drag it towards right increase the column width. You can also expand it by double clicking the border.

#NAME? ERROR

This error occurs when an incorrect formula or function is called in MS Excel formula. The #NAME? Error refers to any misspelled function or a wrong function. As shown in the figure below where the desired formula was SUM however, by mistake, there is an underscore next to the range. So the result occurred as a #NAME? error.

The solution is quite simple. Correct the mistake and the function will work as shown in the figure below.

#VALUE! ERROR

This error occurs when a data type mismatches. As shown in the figure below, where the formula refers to adding cell A1 A2 and A3, however, since the value in A2 is a non-number value therefore, the result occurs as an error “#VALUE!”.

To resolve this issue, you can perform either of the following

1. Change the value of cell A2 to a number. Or

2. Use a SUM function so that it can ignore cells that contain text.

#DIV/0! ERROR

MS Excel displays #DIV/0! error when a formula tries to divide a number by 0 or an empty cell.

Change the value of cell A2 to a value that is not equal to 0 or you can also use function like IF to prevent the error from being displayed.

#REF! ERROR

The #REF! error occurs in MS Excel when a formula refers to a cell that is not valid. This normally occurs when a cell or a column is deleted after implementing the formula. In the following example, you will observe that the #REF! error will occur when column B is deleted. The step by step tutorial is as follows:

1. Enter formula A1 / A2 in cell A3. This references the both the cells in cell A3

2. Delete Row 2 by right clicking on the Row 2 header and click Delete.

3. Select cell B1. The reference to cell B1 is not valid anymore as shown in the figure below.

To fix this error, you can either delete +#REF! in the formula of cell A2 or you can undo your action by clicking Undo in the Quick Access Toolbar (or press CTRL + Z) or you may assign any other reference.