This is the easiest of all. When you divide something with 0, you see this error. For example, a cell with the formula =23/0 would return in this error.
The most common reason why you see this error is because you misspelled a formula or named range or have a syntax error. For example, if you write =summa(a1:a10) in a cell, it would return #NAME? error. Common syntax errors that cause this are missing a ( after a function or forgetting to enclose a string in double quotes.
This is one of the frequent errors you see while using lookup formulas (which we will cover shortly). The N/A error is shown when some data is missing or inappropriate arguments are passed to the lookup functions (vlookup, hlookup etc.) or if the list is not sorted and you are trying to lookup using sort option.
This is rare error. When you use incorrect range operators often you get this error. For example, the formula =SUM(D30:D32 C31:C33) returns a #NULL! error because there is no seperator between range 1 and range2. Excel thinks that you are trying to use the intersecting operator but the two ranges do not intersect.
The space is the Intersect Operator and should be used correctly like:
=A1:F1 B1:B10
Excel will return the cell value that intersects A1:F1 and B1:B10.
In this case, B2.
However, if you used
=A1:F1 B2:B10
Excel would display the #NULL! error as it is NOT possible for a row 1 range
to intersect a column range that starts at row 2.
This is the error that you see when your formula returns a value bigger than what excel can represent. For example, the formula =4389^7E+37 returns a #NUM! error.
This error also occurs if you supply an invalid number to a function argument. For example, using a negative number when a positive is needed or using a $, % symbol with the number.
This is one of the most common error messages you see when you make changes with a worksheet full of formulas. You get #REF! error when one of the formula parameters is pointing to an invalid cell or range. This can happen because you deleted the cells. For example, try to write a sum forumla like =SUM(A1:A10, B1:B10, C1:C10) and then delete the column C. Immediately the sum formula returns #REF! error. This is an error that must ALWAYS be fixed, not just captured by looking for errors.
Possibly the most frequent error type.
Occurs when the wrong type of argument or operand is used.
For example. the formula =SUM("ab","cd") returns #VALUE! error.
Similarly, if you have the formula
=A1*A2
and either cell has text and NOT numbers, the #VALUE! error would be displayed.
You see a cell full of # symbols when the contents cannot fit in the cell. For example, a long number like 2339432094394 entered in a small cell will show ####s. Also, you see the ###### when you format negative numbers as dates.