Error Messages in Microsoft Excel:
Microsoft Excel is considered to be the most effective spreadsheet software for organizing, analyzing, and visualizing data. It allows users to track data, manage budgets, create charts, and evaluate trends. In MS, we use various formulas and functions, but sometimes errors can occur, and these errors will be visible in the worksheet.
In Microsoft Excel, error values occur when a formula or data doesn’t work as expected. Here are some common MS Excel error values and their meanings. If you encounter any of these errors, understanding the cause will help you troubleshoot and fix them quickly:
-
#DIV/0!
This error occurs when a formula attempts to divide a number by zero or an empty cell.
For Example: if you attempt to calculate 10/0 in MS Excel, it can’t perform this calculation and returns the #DIV/0! error.
Another example: If you try to calculate A1 / B1 (where A1 cell has a value of 10 and B1 is blank), then this formula will return a #DIV/0! error.
-
#N/A
This error occurs when a formula tries to bring a value but cannot find or the value is unavailable.
Suppose you attempt to use, for example, the “VLOOKUP” or “MATCH” functions in MS Excel. If the value is not found, it will result in an #N/A error.
-
#Value!
This error occurs when an incorrect argument is entered into our formula or function. For example, if we enter
“10 in Cell A1” and “an apostrophe or space in Cell B1”, we might encounter this error. Let’s enter the following formula in Cell C1.
=10 * ‘ —— >>>>>> the function will return #VALUE!.
-
#REF!
“This error occurs when a cell reference is not valid. For example, if you have deleted a row or column that is part of a function or formula.
For instance, if you type any number or 10 in Cell A1 and 5 in Cell B1, and then multiply the values in A1 and B1 in Cell C1, the value should be 50. Now, if you delete column B, you will get the #REF! error.”
-
#NAME?
This error occurs when MS Excel encounters a spelling mistake in a function or formula. For example: =AVERAE(B9:B12). Here, the alphabet “G” is missing, causing MS Excel to return the value #NAME?
-
#NUM!
This error occurs in MS Excel when an invalid argument is used in a function or formula. The error may occur when a number is too small or too large. For example, in the square root formula =SQRT(-3), the argument -3 will return #NUM!.
-
#NULL!
The #NULL! error occurs in MS Excel when an invalid range argument is used. For instance, if a comma or colon is forgotten in the argument, such as =SUM(J7 J12), MS Excel cannot calculate the sum for the range of values, resulting in the #NULL! error.
-
#SPILL!
This error occurs when a formula that returns multiple values (an array) can’t fit them into the required range. It’s specific to dynamic arrays in the latest versions of Microsoft Excel and happens if the target cells for the array are already occupied by other data or if there’s a block preventing the spill.
-
#CALC!
The #CALC! error in MS Excel happens when a formula or function encounters a calculation issue due to invalid data or an unsupported operation. It’s common with dynamic array functions and some newer MS Excel functions.