Written by Mynda Treacy of My Online Training Hub.
Writing formulas can have you tearing your hair out, especially when you start nesting them. Here are 4 tips to help you troubleshoot formulas when things go wrong.
1. Function Wizard
Some think the Function Wizard is for beginners but seriously, who can remember what the cryptic syntax is for every function.
So, when you get stuck, simply click on the Function Wizard button to the left of the formula bar to jump in, even when you’re in the middle of writing a formula.
It’ll take you from here:
To here:
Now you have some helpful information about the function and meaning of each argument.
2. Syntax tool
The syntax tool pops up as soon as you start to enter your formula.
Move It
You can move it out of the way by hovering your mouse on the very edge of the tool tip until you get the 4 headed arrow and then drag it into place:
Navigate Your Formula
You can also use the syntax tool to jump to a section of your formula.
Hover your mouse over the argument you want to select and when it turns blue with an underline click on it to go to that section of your formula:
See how the value_if_true argument in the formula above is highlighted to show it is selected.
3. F9 is Your Friend – Evaluate Part of a Formula
When working with complex nested formulas you’ll often run into problems and it can be difficult to narrow down the cause.
The F9 key allows you to evaluate parts of your formula in isolation.
For example if I wanted to evaluate the logical_test, which is the AND function nested in my formula, I can select it using the syntax tool link:
Then press the F9 key to evaluate just that part:
And we can see it evaluates to FALSE.
You can continue selecting different parts of the formula to evaluate them one by one.
Pressing CTRL+Z will reverse the effect of the last F9 pressed. Or if you have already entered the formula pressing the Escape key will exit out of edit mode and put the formula back as it was.
4. Evaluate Formula Tool
The evaluate formula tool is similar to the F9 key however you cannot choose which part of the formula you want to evaluate, it will evaluate in order, which is great if you’re trying to understand how a formula works.
You’ll find it on the Formulas tab in the Formula Auditing Group:
To use it simply select the cell containing your formula and click the Evaluate Formula button which opens the dialog box:
This will evaluate the formula in order. Each click of the button evaluates the next section of the formula and so on.
You can also Step In and Step Out of different sections of the formula for more information on the source of different components.
About the author: Mynda Treacy is co-founder of My Online Training Hub, author of their comprehensive Excel Formulas library, and popular Excel Blog.
Leave a Reply