Know The 7 Types Of Error Messages In Excel Formulas And How To Solve Them

Table of Contents[Show]
Know The 7 Types / Types Of Error Messages In Excel Formulas And How To Solve Them


Have you ever gotten a ##### error when using excel formulas or formatting cells? When using Microsoft Excel there are times when we find an error message such as #NULL!, #DIV/0!, #VALUE!, #REF!, #NAME?, #NUM!, #NA.

What is the meaning of the error generated by the Excel formula?

If we are just learning Microsoft Office Excel, of course we will be confused by this kind of error message.

The error message is a type of message that provides clues to the cause of a formula error and helps correct errors in the formula.

Knowing and understanding the meaning of each error message that appears in this Excel formula will certainly give you an idea of ​​how to eliminate or how to solve the error in Excel.

7 TYPES OF ERROR MESSAGES IN EXCEL FORMULES

Before explaining some types of error messages in Excel, it's a good idea to make some of the following experimental data on a Microsoft Excel sheet and write down some excel formulas as shown below:

Example of Error Messages in Excel Formulas

 

1. The #NULL!

An error message #NULL! will appear if the range of cells (Cell Range) that we enter into the Excel formula does not coincide or does not refer to the correct cell range. This is usually caused by adding spaces or not having a correct argument separator.

In the example Excel formulas =SUM(C2:C6 D2:D6) will generate an error message #NULL! because there should be an argument separator ":" or ",".

A message #NULL! will also appear, for example we type the excel formula =SUM(C2 C6). The space between C2 and C6 causes Excel not to recognize this cell range because we wrote the wrong range of cells that we should have written C2:C6.

2. Error # DIV / 0!

Error message #DIV/0! means that in the formula there is a number divided by 0. This error message appears because we divide a number value by 0 (zero) . When we were in school, of course we were taught that if we divide a number by 0 (zero) then the result is infinite or unlimited. Because of this infinity, Excel raises the error #DIV/0! value.

In the example above we divide the number of cells C2 and D2 or 60 + 90 by 0, giving rise to the type error #DIV/0!.

3. #VALUE!

Error type #VALUE! means that the data type that we enter into the formula does not match the type of data requested or expected by Excel functions or formulas. It could also be due to a mixture of data types that we enter into the excel formula.

For example, if we want to add numbers, the data type that we enter into the formula should be a number. If the text data type we enter is like the example above ( =B2+C2) where B2 and C2 contain text, the result is an error #VALUE!.

4. #REF!

The error message #REF! means that the excel formula we entered refers to an incorrect or invalid cell reference . This can happen if the cell refers to a reference that is deleted, we move or because the cell that we referenced is in another workbook that is not open or unavailable.

In the example above we intend to add up the value of C6 with the value of cell A1 on Sheet 1 in the workbook with the file name Book5.xlsx. Because we haven't opened the Book5.xlsx file or don't exist, Excel displays an error message #REF!.

5. #NAME?

The error message #NAME? means that the Excel formula does not recognize a defined name, table name or function name. This can happen because of a typo or the names really don't exist in the excel file.

In the example, we haven't created defined name DATA so Excel doesn't recognize this name. This causes Excel to display an error message #NAME!.

Another example is sometimes we enter the wrong function in an excel formula. For example, we want to calculate age using the DATEDIF formula but we incorrectly write the formula to DATEIF, this will also cause an error message #NAME? because Excel doesn't recognize the DATEIF function.

6. #NUM!

An error message #NUM! means that the excel formula has returned an invalid numeric value. This invalid numeric value is usually because the value is too large or too small to exceed the excel specification capability .

In the example the value of 10 ^ 1000 (10 to the power of 1000) is very large beyond what Excel can handle so that Excel returns the value to an error message #NUM!.

7. Error # N / A

An error message #N/A means that the excel formula has returned a value that does not exist or that the excel formula cannot find the reference value .

This error or error message usually appears when we use Lookup functions such as LOOKUP , VLOOKUP , HLOOKUP , etc.

In the example above we intend to find the value 1 for "Name 6" using the VLOOKUP function. Because the value we are looking for is FALSE, which means that the value must match exactly, the Excel formula generates an error message value #N/A.

2 OTHER ERROR MESSAGES FROM MICROSOFT EXCEL

Apart from the 7 (seven) types of error messages that I have described above, there are still 2 other error messages that are often displayed in Excel. The error messages are ######## and #GETTING_DATA.

Actually these 2 messages are not error messages or errors because this message does not appear because there is something wrong or incorrect with the value we entered in an excel formula.

1. ########

The message ######## will appear when the cell or column that appears the message is too narrow so that it cannot accommodate the digits in the cell.

Apart from being a column that is narrower than the numeric digits, this message also appears when the column or cell displays a negative date and time format.

2. #GETTING_DATA

If you encounter a message, #GETTING_DATA don't be in a hurry to get confused. This message appears if Excel is doing calculations or calculating a large and complex Excel formula.

This can happen if the excel formula on these cells has branched references and is quite complicated. So that Excel needs additional time to do the calculations.

By knowing the types or types of messages, of course we will find it easier to correct the errors we find when writing an Excel formula.

There are times when the error message in the excel formula above is the result of a formula that is deliberate. If so, how do you get rid of it?

For this kind of problem you can solve it by using the IFERROR function . In addition, you can also take advantage of the ISERROR function and its friends.

As an overview, the IFERROR function is used to replace the excel error messages that have been mentioned above with other text, such as blank text. While the ISERROR function is used to find out whether an exel formula produces an ERROR value or not, so the ISERROR function and its friends produce a BOOLEAN (True / False) value.

I think this is enough what I can explain about the types of error messages in Microsoft Excel . Next time we connect again. If it's worth sharing, don't hesitate so that other Excel users can also take advantage of it.

Leave a Comment