SubscribeFormula 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
- Select the cell with formula.
- Now click on the formula bar
- Just select the parts the formula and press F9 (for eg: the first average() formula)
- This will evaluate only the selected part and replaces it with the result. Like this:
- Using this technique you can narrow down the errors to particular range or values causing it.
- 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. 
Trackbacks & Pingbacks
- Pingback by Excel Formula Errors - Understand and Debug Them | Excel Howtos | Pointy Haired Dilbert - Chandoo.org on April 20, 2009 @ 12:48 pm
- Pingback by Become an Excel Guru in 31 Days | Pointy Haired Dilbert: Learn Excel Online - Chandoo.org on March 11, 2010 @ 9:53 am
Comments
RSS feed for comments on this post. TrackBack URI
Leave a comment
If you have a question, please ask in the forums
At Pointy Haired Dilbert, I have one goal, "to make you awesome in excel and charting". PHD is started in 2007 and today has 300+ articles and tutorials on using excel, making better charts. 
Another way to do this would be to invoke the formula helper function. Denoted by the f(x) I believe. This will also evaluate everything, but admittedly pushing the keys is a bit more fancy
This is a great Excel tip on debugging. Thanks!
Best excel tip ever Chandoo!
Excellent. Now debugging a worksheet is much easier.
AS we say in Ireland, “that’s deadly!” That’s a very high compliment, meaning that it’s absolutely brilliant. I’ve been using Excel for ?? years and never knew that existed. Thanks again.
You can also use the formula auditing toolbar, there’s a button labelled “Evaluate Formula” which you can use to step through a given formula one calculation step at a time.
@Peden: yeah formula helper toolbar has handy tools for debugging formulas and tracing dependents.
@Learn Excel: thank you so much…
@FC: I am so happy you liked it
@Oliver: learning this trick saved me so much time in both debugging and avoiding future errors. It also helps me to know that right data is coming through lookups and references in the formula.
@TheQ: That is really a wonderful comment.
Cool Trick!
One needs to press Esc after seeing the result. Else the actual formula gets replaced by the evaluated result.
@JP: yeah formula auditing toolbar is another helpful tool.
@Vishal : you are right, if you don’t press ESC after this, excel replaces the formula with its results.
Dear Chando,
Its really great it saves my time lot. Thanks for the info:)
@Mohan: You are welcome.
Hi Chandoo,
I use PivotTables everday. I want to use division and multiplication in Calculated items in PivotTables. Is it possible? If yes, how to do it.
@Sumeet : I suggest to insert the col in your database with required formula and then make pivot table.
Alternative:
Check this path : raise pivot table=>click the data field in layout ==>go to option==>select available option from show data as
@Chandoo : Pls inform how to create the customise equation in above path
@Ketan: thanks for sharing your tips with Sumeet. I use pivot tables very rarely, one reason why we dont have many posts on them here. I will learn them in the next few days and share my thoughts here.
@Sumeet: Meanwhile if you come across any other good ways to do this let us all know.