Formula Errors in Excel

Top 8 Most Common Excel Formula Errors

Below is the list of some most common errors that can be found in excel formula:

  1. #NAME? Error: This Excel error usually occurs because of the non-existed function we are using.
  2. #DIV/0! Error: This Excel error because if you try to divide the number by zero or vice-versa.
  3. #REF! Error: This error arises due to a reference missing.
  4. #NULL! Error: This error comes due to unnecessary spaces inside the function.
  5. #N/A Error: This comes because the function cannot find the required data. Maybe due to the wrong reference is given.
  6. ###### Error: This is not a true Excel formula error but because of a formatting issue. Probably the value in the cell is more than the column width.
  7. #VALUE! Error: This is one of the common Excel formula error we get to see in excel. This occurs due to the wrong data type of the parameter given to the function.
  8. #NUM! Error: This Excel formula error because of the number we have supplied to the formula is not proper.

Ok, now I will discuss one by one in detail.

#1 #NAME? Error

#NAME? an excel formula error is when the excel cannot find the supplied function or the parameters we supplied are not matching with the standards of the function.

You can download this Excel Errors (Excel Template) here – Excel Errors (Excel Template)

Just because of the display value of #NAME? it does not mean excel asking your name; rather, it is due to the wrong data type to the parameter.

Name Error 1

If you notice in the formula bar instead of the SUM formula in excel, we have misspelled the formula as su. The result of this function is #NAME?.

How to Fix the #Name? ERROR Issue?

In order to fix the excel formula error, look for the formula bar and check if the formula that you have entered is valid or not. If the formula spelling is not a valid one, change that to the correct wording.

Name Error 1-1

#2 #DIV/0! Error

#DIV/0! Is due to the wrong calculation method, or one of the operators is missing. This error we are in calculations. For example: If you have a Budgeted number in the cell A1 and the Actual number in the cell B1, to calculate the efficiency, we need to divide the B1 cell by A1 cell. If any of the cells are empty or zero, we get this error.

DIV Error

How to Fix the #DIV/o! ERROR Issue?

To fix this excel formula error, we need to use one formula to calculate the efficiency level. Use the IFERROR function in excelIFERROR Function In ExcelWhen an error occurs in any formula or argument, the IFERROR excel function is used to display the result. This function can be used in conjunction with other functions to alert the user of any errors in the formula.read more and do the calculation inside the function.

DIV Error in Excel 1

#3 #REF! Error

#REF! the error is due to supply the reference is missing all of a sudden. Look at the below example in the cell B2; I have applied the formula as A2 + C2.

REF Error 3

Now I am going to delete the column C2 and see the result in the cell B2.

REF Error 3-1

Now I will demonstrate one more example. Look at the below image I have applied the VLOOKUP formula to fetch the data from the main data.

REF Error 3-2

In the VLOOKUP range, I have mentioned the data range as A:B, but in the column index number, I have mentioned the number as 3 instead of 2. The formula encountered an error because there is no column range 3 in the data range.

How to Fix the #REF! ERROR Issue?

Before you add or delete anything to the existing data set, make sure all the formulas are pasted as values. If the deleting cell is referred to any of the cells, then we will encounter this error.

Just in case if you have deleted any rows or columns or cells by accident, always undo your action.

#4 #NULL! Error

#NULL! Error is due to the wrong supply of the value to the required parameters, for example, after the wrong supply of range operator, wrong mention of parameter separator.

Look at the below image I have applied the SUM formula to calculate the sum of the values in cell A2 and B2. The mistake I did here is after the first argument, I should give comma (,) to separate the two arguments; instead, I have given space.

Null Error 4

How to Fix the #NULL! ERROR Issue?

In these cases, we need to mention the exact argument separators. In the above image, I should use the comma (,) after the first argument.

Null Error 4-1

In the case of a range, we need to use Colon (:)

Null Error 4-2

#5 #VALUE! Error

#VALUE! The error occurs if the formula is not able to find the specified result. This is due to non-numerical values or wrong data types to the argument.

Look at the image below; I have calculated the commission amount based on the sales value and commissions.

VALUE Error 5

If you notice the cell D6 and D8, we got an error as #VALUE!. The simple reason we got an error as #VALUE! Is because there is no commission percentage in the cells C6 and C8.

We cannot multiply the text value with numerical values.

How to Fix the #VALUE! ERROR Issue?

In these cases, just replace all the text values with zero until you get further information.

#6 ###### Error

###### to be very frank; it is not an error; rather, it is just a formatting issue. Look at the below image in the cell A1 that has entered the date values.

#### Error 6

This is due to the length issue of the characters. The values in the cell are more than the column width. In simple terms, “Column width is not wide enough.”

How to Fix the ###### ERROR Issue?

Double click on the column to adjust the column widthThe Column To Adjust The Column WidthA user can set the width of a column in an excel worksheet between 0 and 255, where one character width equals one unit. The column width for a new excel sheet is 8.43 characters, which is equal to 64 pixels.read more to get the full values to visible.

#### Error 6-1

#7 #N/A! Error

#N/A! is due to the formula is not able to find the value in the data. This usually occurs in the VLOOKUP formula (I know you have already encountered this error).

NA Error in excel 7

We go some errors as #N/A in the cells E6, E8, and E9. If you look at the ranges for these cells, it does not include the values against those ids.

NA Error in excel 7-1

ID 156 is not there in the range A6 to B13; that is why we got an error. Similarly, for the remaining two cells, we have the same issue.

How to Fix the #N/A! ERROR Issue?

When we are referencing a table range, we need to make this as an absolute referenceAbsolute ReferenceAbsolute reference in excel is a type of cell reference in which the cells being referred to do not change, as they did in relative reference. By pressing f4, we can create a formula for absolute referencing.read more.

NA Error in excel 7-2

Press the F4 key to make it an absolute reference.

#8 #NUM! Error

#NUM! is due to the formatting of the numerical values. If the numerical values are not formatted, we will get these errors.

How to Fix the #NUM! ERROR Issue?

We just need to fix the formatting issue of the specific numbers.

Things to Remember

Recommended Articles

This has been a guide to Errors in Excel. Here we discuss the top 8 types of formula errors in excel and how to fix these errors along with excel example and downloadable excel templates. You may also look at these useful functions in excel –

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>

Reader Interactions

Comments

  1. Weldu Gebru says

    I have got much Excel knowledge from your site. I am very thankful. God bless you.

    • Dheeraj Vaidya says

      Thanks for your kind words!

  2. Donna Murimwa says

    thanx a lot…article helped me to bits.more than what a video would have done.