43. Excel Tips - Function debugging - using F9

Nick's picture

This is a follow-on from the first tip on debugging a large Excel function call

This Excel tip is really one of the most useful ones I can think of. I use it all the time, so make sure you understand it

Suppose you have a large function call, and you want to understand what is going on.

There's a neat way to reduce the function to smaller pieces to make it easier to understand.

Step1: Lets take a function call from my Buy, do Up and Sell Calculator.

This is the function call... it's massive... what on earth is going on ?: debug-large-function-excel


First, select a complete statement in the formula bar... in this case, select: ROW()-5 Maximum_length_of_project



Step2: Now, press F9 - What this does it to evaluate what you have selected. Step3: Repeat for each element of the formula that you want to evaluate. Now you can see what the result of the function is made up from.



Step3: You can keep on reducing the function call until its down to bare bones debug-large-function-excel

NOTE: When you have finished investigating... Don't forget to press ESCAPE, and NOT ENTER !

Training Video - Function Debugging