Formula Errors in Excel

Updated on December 27, 2023
Article byBabita Sehdev
Edited byAshish Kumar Srivastav
Reviewed byDheeraj Vaidya, CFA, FRM

Top 8 Most Common Excel Formula Errors

Below is the list of some most common errors that we can find in the Excel formula:

  1. #NAME? Error: This Excel error usually occurs because of the non-existent function.
  2. #DIV/0! Error: This Excel error because if we 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: The function cannot find the required data. Maybe the wrong reference is given.
  6. ###### Error: This is not a true Excel formula error, but it occurs 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 errors we see in Excel. It occurs due to the wrong data type of the parameter given to the function.
  8. #NUM! Error: This Excel formula error because the number we have supplied to the formula is not proper.

Now, we will discuss them one by one in detail.

#1 #NAME? Error

The #NAME? Excel formula error is when Excel cannot find the supplied function or the parameters we gave do not match 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 is asking your name. Rather, it is due to the wrong data type for the parameter.

Name Error 1

If we notice in the formula bar, instead of the SUM formula in excelSUM Formula In ExcelThe SUM function in excel adds the numerical values in a range of cells. Being categorized under the Math and Trigonometry function, it is entered by typing “=SUM” followed by the values to be summed. The values supplied to the function can be numbers, cell references or ranges.read more, we have misspelled the formula as su. So the result of this function is #NAME?.

–>> If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

How to Fix the #Name? ERROR Issue?

To fix the Excel formula error, we must look for the formula bar and check if the formula we have inserted is valid. If the formula spelling is inaccurate, we must change that to the correct wording.

Name Error 1-1

#2 #DIV/0! Error

The #DIV/0! Error is due to the wrong calculation method, or one of the operators is missing. This error occurs in calculations. For example, if we have a “Budgeted” number in cell A1 and the “Actual” number in cell B1, we must divide the B1 cell by the A1 cell to calculate the efficiency. If any cells are empty or zero, we may get this error.

DIV Error

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

To fix this Excel formula error, we must use one formula to calculate the efficiency level. Therefore, we can use the IFERROR function in excelIFERROR Function In ExcelThe IFERROR function in Excel checks a formula (or a cell) for errors and returns a specified value in place of the error.read more and calculate inside the function.

DIV Error in Excel 1

#3 #REF! Error

The #REF! Error is due to the supply of the reference missing suddenly. Look at the below example in cell B2. We have applied the formula “A2 + C2.”

REF Error 3

Now, we will delete column C2 and see the result in cell B2.

REF Error 3-1

Now, we will demonstrate one more example. Look at the below image. We have applied the VLOOKUP formula to fetch the dataVLOOKUP Formula To Fetch The DataThe VLOOKUP excel function searches for a particular value and returns a corresponding match based on a unique identifier. A unique identifier is uniquely associated with all the records of the database. For instance, employee ID, student roll number, customer contact number, seller email address, etc., are unique identifiers. read more from the primary data.

REF Error 3-2

In the VLOOKUP range, we have mentioned the data range as A:B. But in the column index number, we have mentioned the number as 3 instead of 2. So, as a result, the formula encountered an error because there is no column range 3 in the data range.

How to Fix the #REF! ERROR Issue?

Before adding or deleting anything to the existing data set, we must ensure all the formulas are pasted as values. If the deleting cell is referred to any of the cells, we may encounter this error.

We must always undo the action if we accidentally delete any rows, columns, or cells.

#4 #NULL! Error

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

Look at the below image. We have applied the SUM formula to calculate the sum of the values in cells A2 and B2. The mistake we made here is after the first argument. We should give a comma (,) to separate the two arguments. Instead, we have provided space.

Null Error 4

How to Fix the #NULL! ERROR Issue?

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

Null Error 4-1

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

Null Error 4-2

#5 #VALUE! Error

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

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

VALUE Error 5

If we notice the cells D6 and D8, we get an error as #VALUE!. The reason we got #VALUE! Error 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, we must replace all the text values with zero until we get further information.

#6 ###### Error

The ###### error, is not an error. Rather, it is just a formatting issue. For example, look at the below image in cell A1 that has entered the date values.

#### Error 6

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

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

We must 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 visible.

#### Error 6-1

#7 #N/A! Error

The #N/A! error is because the formula cannot find the value in the data. It usually occurs in the VLOOKUP formulaVLOOKUP FormulaThe VLOOKUP excel function searches for a particular value and returns a corresponding match based on a unique identifier. A unique identifier is uniquely associated with all the records of the database. For instance, employee ID, student roll number, customer contact number, seller email address, etc., are unique identifiers. read more (We know you have already encountered this error).

NA Error in excel 7

We got some errors as #N/A in the cells E6, E8, and E9. If we 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 in the range from A6 to B13; we got an error. Similarly, for the remaining two cells, we have the same issue.

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

We need to make this 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 when referencing a table range.

NA Error in excel 7-2

We must press the F4 key to make it an absolute reference.

#8 #NUM! Error

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

How to Fix the #NUM! ERROR Issue?

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

Things to Remember

This article is a guide to Errors in Excel. We discuss the top 8 types of formula errors in Excel and how to fix these errors, along with Excel examples and downloadable Excel templates. You may also look at these useful functions in Excel: –

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.