Troubleshooting Formulas

0 Flares Filament.io 0 Flares ×

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 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:

Use the Insert Function button for help

To here:

Explanations in the Function Arguments dialog box

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:

Using the Syntax tool

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:

Selecting an argument in the syntax tool

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:

Selecting part of a formula

Then press the F9 key to evaluate just that part:

F9 for manual calculation

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:

Evaluate Formula in Excel

To use it simply select the cell containing your formula and click the Evaluate Formula button which opens the dialog box:

Stepping into a formula

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.

Photo of Mynda Treacy

About the author: Mynda Treacy is co-founder of My Online Training Hub, author of their comprehensive Excel Formulas library, and popular Excel Blog.

Watch the Video

Leave a Reply

Your email address will not be published. Required fields are marked *