MS Excel: Formula Error and What It refers To

When you get an error in your formula cell, you’ll need to determine what caused the error and fix it.
Common Formula Errors You’ll See in EXCEL
What Appears in the Cell and What happened? :

#DIV/0!
Appears when the formula calls for division by a cell that either contains the value 0 or, as is more often the case, is empty. Division by zero is a no-no.

#NAME?
Appears when the formula refers to a range name that doesn't exist in the worksheet. This error value appears when you type the wrong range name or fail to enclose in quotation marks some text used in the formula, causing Excel to think that the text refers to a range name.

#NULL!
Appears most often when you insert a space (where you should have used a comma) to separate cell references used as arguments for functions.

#NUM!
Appears when Excel encounters a problem with a number in the formula, such as the wrong type of argument in an Excel function or a calculation that produces a number too large or too small to be represented in the worksheet.

#REF!
Appears when Excel encounters an invalid cell reference, such as when you delete a cell referred to in a formula or paste cells over the cells referred to in a formula.

#VALUE!
Appears when you use the wrong type of argument or operator in a function, or when you call for a mathematical operation that refers to cells that contain text entries.

MS Excel:Remembering Cell Names

When you're doing a formula and can't remember the range names that you need, you can:

Press [F3] - (a list of range name appear)

DOUBLE CLICK the range name that you want to include in formula.

MS Excel: Quick Click Movement

If you Want to find out where a column ends or where the next blank cell is in the column.

Select a cell:
Double-click the bottom edge of the selected cell,
(Take cursor to botton edge and when it becomes + sign made of four arrows)
and you're taken to the last filled cell below the selected cell in the current column.
This trick works for rows nd column and in all four directions.
So, for example, if you want to find the last filled cell or the next empty cell in the current row:

Select a cell:
Double-click the right edge of the cell.

MS Excel:Range Names Used In Formulas

Named ranges let you calculate the total for all cells in a range with a formula such as

SUM(Value) instead of SUM(E2:E20)
For example, enter the name Quantity for Column C and Price for column D and value in column E.(highlight the column from C2:C20 and then type the name in the Range name text area at the left of the fx symbol and hit Enter).
Then enter the formula =Quantity*Price in column E cells.
Now, as you fill in numbers in the Quantities and Price columns, Excel automatically calculates Values in the third column.

MS Excel: Avoid error displays in formulas

Sometimes a formula may return an error message
but you may want to avoid the messages.
You can do so by using an IF() function to check for an error.
The formula below displays a blank if the division results in an error.

=IF(ISERROR(A1/B1),"",A1/B1)