Formula Debugging in Excel – Tip [spreadcheats]

Can you imagine building a complex worksheet without formulas? I can’t. While no one can dispute the usability of formulas, we all know how painful it is when an excel formula returns a mysterious error and we don’t know what is causing it.

When I learned IF() formula for the first time, I have spent a whole Sunday morning debugging a stupid error in a grade calculation formula.

So as part of our spreadcheats series, we will learn a handy trick you can use to debug formulas and fix the errors quickly.

Assuming we have a moderately lengthy formula like this
=IF(AVERAGE(B2:B6)<=AVERAGE(C2:C6),MAX(B2:B6),MAX(C2:C6))
and we want to know where the error is occurring

1. Select the cell with formula.
2. Now click on the formula bar
3. Just select the parts the formula and press F9 (for eg: the first average() formula)
4. This will evaluate only the selected part and replaces it with the result. Like this:

   

5. Using this technique you can narrow down the errors to particular range or values causing it.
6. Now that you know where the error is occurring you can wrap that part of formula with an ISERROR() formula to avoid unpleasant surprises.

What is your favorite way of handling errors?

PS: If you have mailed me or commented here and waiting for a response, please give me some more time. I am having trouble getting internet connection in Chennai and visiting browsing center to respond to mails is not a pleasant experience either. I really appreciate your patience. Meanwhile if you know any free wi-fi hot spots in Chennai do let me know through comments. 🙂