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. 🙂
35 Responses to “Formula Debugging in Excel – Tip [spreadcheats]”
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.
[...] Formula Debugging using F9 Key [...]
[...] Day 17: Quick tip to Debug Complex Excel Formulas [...]
[...] Use F9 Key to Debug Portions of Formulas [...]
[...] Using F9 key to evaluate portions of formulas [...]
How did I not know about this?!?!? Wow. What a time saver!
how to fix one call in formula while other can b change
A large multi-page spreadsheet has stopped calculating. The formulas have NOT been changed to their values. This is a year's work. Let's say cells A1 through Z1 on page 2 copy cells A22 through Z22 on the previous page. I enter the + key and targets the corrsponding cell on the previous sheet. Though the formula is there, the result is zero. It's happening out of the blue throughought the workbook. Copying a neighboring cell, copies the formua correctly & targets the correct cells, but the result remains the SAME as in the cell I copied the formula FROM. The workbook is about 70% formulas with the rest being hard data. Evaluate the formula & it tells you to lock the cell to prevent inadvertently changing it. I've tried to do that, but it does not help. I'M GOING CRAZY!
Good tip, thanks!
Thanks for your tip - but I was still puzzled when part of my formula was highlighted; I checked the cells it pointed to (as I did before looking for help), but didn't see the problem for a while. Here's what the problem was: I migrated my files from XP, where I saved Works 2002 spreadsheets as Excel. All the formulas were working there. But when I moved them to V7 with Excel instead of Works, one of the rules about formulas was different. Works counted any non-numeric input in a cell used in a formula (example: =c95+q95 where c95 has the string "Hello" in it) as 0 (thus: =c95+q95 yields q95); but Excel counts it as an error. So I suddenly had lots and lots of errors where there was nothing wrong before. The fix was easy: delete or move the bits of text I'd put in. If anyone else happens to be moving spreadsheets from Works to Excel, here's why the formulas suddenly complain "#VALUE"
I'd also like to ask a question. Is there a way to include BOTH calculated numeric values AND text in the result of a formula? That is, can I say something like =IF(A72<90,A73" Don't worry",A74" Be happy!") I really wish I could mix text and calculated numbers in one of my spread sheets. For now I have 2 different cells; one with the numbers and the adjoining one with the text. It's an extra 31 cells with a formula in each to be maintained from month to month....
Annabelle,
You'd use the Ampersand, like this:
=IF(A72<90,A73 & " Don't worry",A74 & " Be happy!")
[...] we use the F9 key to successively evaluate parts of the function it looks like [...]
Great tip, thanks! This will save me a lot of time & effort. I have a follow-up question.
If I am debugging a very long formula, I may want to debug several segments in the formula (say, 10 segments in all). After I select the 1st segment, press F9 & display the value, how do I change back to formula mode WITHOUT EXITING the cell, so that I can select the next segment & examine its value again?
At the moment, I have to press ESC to get out of the cell, then go back to the cell, select the next segment, press F9 again, etc.
Was this question answered, by any chance? I'm having the same issue..
Once I've pressed F9, this:
=IF(AVERAGE(B2:B6)<=AVERAGE(C2:C6),MAX(B2:B6),MAX(C2:C6))
changes to this:
=IF(4.2<=AVERAGE(C2:C6),MAX(B2:B6),MAX(C2:C6))
@Chandoo: Can we revert back while still within the formula? i.e. without exiting the cell and come back just to start all over with the debugging?
thanks!
@The S.Wan
Once you press F9 and it show 4.2
simply revert to the previous formula by pressing Ctrl+Z
As you have found pressing Esc reverts but also takes you out of Edit mode
[…] Select the TRANSPOSE(B2:B19) portion and press F9. (related: debugging formulas using F9 key) […]
[…] Display portion of the formula (F9): Lastly, this little trick will make your life easier when you are deciphering someone […]
how to convert a figure in word with the help of excel formula
like:- 15 - fifteen
1000 - one thousand
@Jitu
https://support.microsoft.com/en-us/kb/213360
What about if you're using Mac? What key will I press instead of F9? Thanks!
Great Help. Got the answer to concatenate and then found a link through this. Thanks a lot!!
When I save my excel sheet and again opening it the formulas linking sheets of same file disappear and shows value only.