- Select the cell
- Press F2 and go into editing mode

- Select part of the formula
- Press F9 to the see the result of that much part

**Happy Debugging!**

Educate Yourself in Excel

*By* rushabhs
*in* Best Practices, Formulas
No Comments

Once you start getting used to advanced feature and functions of Excel, writing long and complex formulas becomes simpler and simpler day by day.

You would also start coming up with new kind of logics in your mind each time you write a formula! Don’t stop this!

But the moment you get an error, you think Damn what do I do now?!?!?!

Now its time to troubleshoot, which is even more a task than writing the formula itself.

To troubleshoot the formula part by part is much more simple then to troubleshoot an entire formula in a single shot.

This can be achieved by following the below: –

- Select the cell
- Press F2 and go into editing mode

- Select part of the formula
- Press F9 to the see the result of that much part

This way, test each part of the formula. Now you know where you are exactly going wrong.

*By* rushabhs
*in* Formulas
No Comments

**Formula Errors**

Entering a formula and receiving an error in return isn’t uncommon. One possibility is that the formula you entered is the cause of the error. Another possibility is that the formula refers to a cell that has an error value. The latter scenario is known as the ripple effect — a single error value can make its way to lots of other cells that contain formulas that depend on the cell. The tools in the Formulas➜Formula Auditing group can help you trace the source of formula errors. Table below lists the types of error values that may appear in a cell that has a formula.

**Error Value Explanation**

- #DIV/0! The formula is trying to divide by 0 (zero), an operation that’s not allowed on this planet. This error also occurs when the formula attempts to divide by a cell that is empty.
- #N/A The formula is referring (directly or indirectly) to a cell that uses the NA worksheet function to signal the fact that data isn’t available. A LOOKUP function that can’t locate a value also returns #N/A.
- #NAME? The formula uses a name that Excel doesn’t recognize. This can happen if you delete a name that’s used in the formula or if you have unmatched quotes when using text. A formula will also display this error if it uses a function defined in an add-in and that add-in isn’t installed.
- #NULL! The formula uses an intersection of two ranges that don’t intersect. (This concept is described in the section “Intersecting names,” earlier in the chapter.
- #NUM! There is a problem with a function argument; for example, the SQRT function is attempting to calculate the square root of a negative number. This error also appears if a calculated value is too large or too small. Excel doesn’t support nonzero values less than 1E–307 or greater than 1E+308 in absolute value.
- #REF! The formula refers to a cell that isn’t valid. This can happen if that cell has been deleted from the worksheet.
- #VALUE! The formula includes an argument or operand of the wrong type. An operand is a value or cell reference that a formula uses to calculate a result. This error also occurs if your formula uses a custom VBA worksheet function that contains an error.
- ##### A cell displays a series of hash marks under two conditions: The column isn’t wide enough to display the result, or the formula returns a negative date or time value.