Formulas in Excel can range from the simple to the complex. When entering formulas, there are a number of mistakes that you can make, like leaving out a parenthesis, or referencing the wrong cells. If you have a long formula with several cell references and it just doesn’t seem to work, pinpointing the error can be tricky.

A good first step in fixing formulas is learning to understand Excel’s error messages. When you see a number sign (#) followed by some text, rather than the expected result, your formula contains some kind of error.

·              The #NAME? message means that you entered something in your formula that Excel interprets as an incorrect cell reference, range, or function name

·              The #REF! message indicates that you might have relocated or deleted a cell that is referenced in the formula

·              The #VALUE! message tells you that you are probably using text in a formula when another argument (probably a number) is required

·              The #DIV/0! error occurs when you divide a number by zero, or divide by a reference to an empty cell ( division by zero is mathematically undefined)

·               The #NUM! error can occur when you try to pass an incorrect argument, like text, to a function that is expecting a numerical value

·              ####### means that a number is too wide to be displayed in the cell

When you see an error message, make sure that the cell with the message is the active cell, and carefully examine the cell contents in the formula bar.

If you cannot find the error based on Excel’s error message, try to trace the precedents or dependents of the formula in question by using the auditing toolbar. When you locate these cells, examine them for typing mistakes or incorrect cell references. Use the kind of error message you are getting as a clue for what you are looking for (i.e. division by zero, or text being used in an equation). Examine the contents of every cell involved in the formula to make sure that the data types (i.e. number, text) are appropriate.

Remember, you can still have an error in your formula even if you do not get an error message. The formula may produce a numerical result as expected, but the result is incorrect. If this happens, examine the mathematics of your formula. Did you place the parentheses in the right places? Are you using the right functions? Are you using the right mathematical operators?

Finally, try to avoid errors by planning out long and complex formulas before you enter them.