During the creation and development of models or templates made with Excel spreadsheet we may introduce some data or formula in a wrong way, so it impossible to perform the calculation, in order to identify where the error is, Excel application is able to detect the cell and classify the type of error produced by facilitating early detection and resolution of errors.
Excel has 4 methods or techniques for the identification of errors:
Symbol top left of the cell
Value in cell
Failed to execute a macro
The first 3 methods of identifying errors has reference when entering any data into a formula which is unable to calculate the result, either because there is no function or because the data that we introduce to the function is incorrect:
Dialog box - Usually when we make a mistake in the introduction of some data in a formula dialog box appears, which makes us a proposal to correct the error.
Symbol top left of the cell - This symbol appears when Excel identifies a possible error as an inconsistent formula, a value in text format when the next cell is a numeric value, etc.... When you click to a red symbol automatically display the box and indicate where possible mistake and how to proceed to correct the error.
Value in cell - May mistake by introduce the required values of a formula or function to be impossible to calculate, Excel in these cases may show some of the following values in the cell:
The knowledge of each of these errors will allow us to identify its origin and fix it.
# NAME? - This error occurs when Excel does not recognize the text of the formula introduced in the cell, either because it is not well written the formula or does not exist.
Example - if we calculate the power of 3.4 raised to15 we introduce the following formula or function in the cell "= POWER (3.4, 15)" in the case that we should write "= POTENTIAL (3.4, 15 ) "Excel would show the error # NAME? because it has no registered POTEN function.
# VALUE! - This type of error occurs when it detects that Excel is calculating with different data types together, as for example numeric data, text, or date and time.
Example - The power of 4 raised to a letter or text "= POWER (4, A)" will result in the error # VALUE!, because it is not possible or makes sense to carry out the calculation.
# NUM! - This error occurs when Excel detects when a formula or function requires a numeric data and has introduced a non-numeric data as a letter or a date. It may also be the result of the calculation is as big or small that Excel can not display it.
Example - Calculation of the power of 1000 raised to 103 results in error # NUM! given that Excel can not show such a high value of this calculation.
# DIV/0! - This error occurs when Excel detects that it has made a calculation of one number divided by 0 or a cell that contains no value.
Example - Calculation of a cell containing the formula "= 5/0" will result in the error # DIV/0!
# REF! - This error occurs when Excel detects the use of a function or a calculation with invalid cell references.
Example - This error usually occurs when deleting columns or rows that contain data related to formulas, the disappearance of such data can not calculate formulas and the error # REF!
# NULL! - This error occurs when we want connect through some calculation or function data ranges or cells that do not intersect.
Example - The sum of two separate data ranges, as shown in the image results in the # NULL! error.
# N/A - This error is generated in the Excel spreadsheet when using search functions or data matching which does not exist in the specified search range.
Example - use the VLOOKUP function to find a value that does not exist in the specified search range.
##### - This error is displayed on Excel when:
The value entered or calculated in cell is larger than the width of the column
It has been calculated or entered a negative value of a date.
Example - multiply a date by the value of -2.