Software errors are almost unavoidable. MS Excel is not an exception either since one is likely to encounter some errors during day to day use of this spreadsheet program. When they occur, errors must be debugged or corrected effectively. Some of the few general reasons that lead to errors include wrong syntax, incompatible inputs as well as complex mathematical operations that turn impossible during execution. Some of the most common errors in MS Excel include:
#N/A:
To put it simply, N/A stands for Not Available. When a formula returns the value of such an error, then this means that a credible result could not be got. Failure to include all parameters of the formula could lead to this error. The error may also be a result of wrong parameters passed to a formula.
Resolving #N/A: to resolve this error please:
1. Check that all cells being referenced have valid input. If the cells themselves have a #N/A error, then the formula or function may also return the same.
2. Check the formula and verify all parameters are passed accordingly. I.e. Ensure that no parameters are missing.
3. Check for incompatible data types between cells which are being operated upon.
#Div/0! :
This is one of the most common errors that occur especially when performing division operations. To be specific, this error will appear in instances where one tries to perform division by zero values between two or more cells. This error also results when you attempt to execute division with a blank cell. Other causes of the error include wrong use and passing of parameters to functions. A good example is passing a zero value as the second argument in the mod () function.
Resolving #Div/0! Error:
1. Check for zero values in input cells of the function or formula.
2. Ensure there are no blanks in cells being operated. Blanks are treated as zeros by default in Excel.
3. Check for formula or function parameters that may lead to division by zero.
4. Use logical operators such as IF (), to control or catch exceptions for instances where division by zero error may occur e.g. in, =IF (Num2 = 0, "", Num1/ Num2), the function returns a string instead of the #Div/0! Error when Num2 is zero and continues execution.
#NULL!
This is another type of error that is unforgiving to most Excel users. The error arises when one uses an intersection operator on non-common cells. For example, in "=SUM (G1:H2 I3:J4) ", G1: H2 and I3:J4 lack common cells. This will result in a null error.
Resolving #NULL!
1. Check the accuracy of references for cells making the range.
2. Check if the ranges that are to be intersected are in the correct position as expected.
#NAME?
This error prevails in instances where a name used in a formula is not recognized and especially if the name is not defined. Other reasons that source this error include misspelled range names, incorrect function names, the omission of the colon in a range reference, Failure to enclose string values in quotations as well as the use of functions whose add-ins have been removed.
Resolving #NAME? Error:
1. Ensure the accuracy of range names.
2. Check to see that the name of the function is correctly spelled.
3. Search for string values without quotations.
4. Check for colons in references for the range involved.
#REF!
This error results in case there is an invalid cell reference. The invalid cell reference may have been caused by deleted or moved cells. Other reasons include cutting and pasting of formulas without editing their reference hence leading to inconsistency in the new locations.
Resolving #REF! Error:
1. Check for accidentally deleted cells.
2. Check the accuracy of cell references used.
3. Undo recent moves accordingly if the error was a result of a mistake.
#VALUE!
The value is an error of its kind that is thrown by Excel when the argument passed to a function is incompatible. This is especially common if you give a function more data type values that don't match. E.g. passing a string parameter in instances where the function expects a numerical value may lead to this error.
Resolving #VALUE! Error:
1. Check for the compatibility of data types in functions.
2. Check the parameters passed to the function.
#NUM!
Wrong use of numbers in functions may actually lead to this error. Most mathematical functions throw this error if fed with the wrong numerical input.
Resolving #NUM! Error:
1. Check for correctness of function parameters/inputs.
2. Eliminate any ambiguous iterations.