From the dreaded # DIV/0! to the frustrating # VALUE! annoying and unexpected error alarms, this article will help you understand and avoid these frustrating Excel warnings.
There are several different error messages that can appear in Microsoft Excel when there is an issue with formulas or functions.
Here is a list of some of the most common error messages:
# CALC!
There is a problem with the calculation of a formula, such as a circular reference. A circular reference occurs when a formula refers to itself, either directly or indirectly, causing the formula to try to recalculate itself indefinitely.
To fix the # CALC! error, you will need to identify the circular reference and modify the formula to remove it.
# DIV/0!
Formula is attempting to divide a number by zero.
Occurs if a formula includes a division operation and one of the operands is zero.
A common cause of this error is a formula that divides a cell reference by another cell reference, and one of the cell references is empty or contains the value zero.
Rectify the formula or the cell reference.
# N/A
The formula cannot find the required information.
Usually displayed when a formula is using a data source that is not available or is taking longer than expected to retrieve the data. For example, using the VLOOKUP function to search for a value that is not in the lookup table.
To fix, check the table or range being searched by the formula to ensure that it contains the value being searched for. The formula can be modified to include an IFERROR function to handle the error. The IFERROR function will return a specified value if an error occurs in the formula, such as # N/A.
Unlike the # N/A! error, # N/A cannot be handled by the IFERROR function and must be resolved by correcting the cause of the error. Displayed if the data source is not responding or if there is a network issue preventing Excel from accessing the data.
Download our Excel Errors Cheat Sheet
# N/A! Returns the N/A error value.
Similar to the # N/A error, and occurs when a formula cannot find the required information.
The main difference between the # N/A and # N/A! errors is that the # N/A error can be handled by the IFERROR function, which allows you to specify a value to return if an error occurs in the formula.
To fix the # N/A error, you can use the IFERROR function to return a specified value if the error occurs.
# NAME? The formula contains a cell reference or defined name that Excel doesn't recognise. This can occur, for example when a reference is misspelled or using a reference that is not defined in the current workbook. Check for a misspelled a reference or a reference that used is not defined.
# NULL!
Attempts to reference an intersection of two ranges that do not intersect. This can happen if when the intersection operator (a space) in a formula is used to specify the intersection of two ranges, but the ranges do not intersect. Check the cell references being used in the formula to ensure that the ranges intersect.
# NUM! This error occurs when a calculation can't be performed.
For example, trying to calculate the square root of a negative number. Can exist if a number is too big or small for Excel or using an invalid input to a function argument.
Generally, fixing the # NUM! error is a matter of adjusting inputs as required to make a calculation possible again.
# REF Refers to a cell that is not valid, either because it has been deleted or because it never existed. Appears if a cell or range of cells is deleted that are being referred to by a formula, or if a formula is pasted into a new location and the cell references have changed.
Check the cell references being used in the formula are valid.
# SPILL! A formula generates a result that is too large to display in the cell. This can happen if a formula is using an array formula or a function, such as SORT or UNIQUE, that generates a large number of results. Check the formula to ensure that it is generating a result that is not too large to fit in the destination cell or range.
# VALUE! A formula includes an incorrect data type, such as text in that is expecting a number. This error will appear entering an incorrect value into a formula, or if a formula is expecting a certain type of data (such as a number or text) and is given a different type of data.
Examine the formula and the cell references it uses to determine what is causing the error. In some cases The formula might need to be adjusted or data checked in in the cells to ensure that it is entered correctly.
Related:
See our related article about using Excel’s IFERROR() function to catch and handle errors in a spreadsheet.
All our Excel articles are here
Comments