Inspect a Formula with F9

In Excel, you can scan part of your formula with the shortcut F9

When use the F9 shortcut?

Let's say you have a complex formula with a lots of VLOOKUP, INDEX, MATCH, references.

But the result returned is not correct and you need to find the reason.

Tips: To display your formula on multiple rows, press Alt + Enter to move to the next line.

How to use the F9 key?

Select part of your formula with the mouse

And then press F9

As you can see, the result is displayed in place of your selection 🙂😎😍

Avoid this mistake

But be aware of this! If at this step, you press Enter, the result will overwrite this part of your formula and you will lose the formula selected.

So, what do I do?

Instead of pressing Enter, leave edit mode using the Escape key or Ctrl+Z. In this way you will return to your initial formula.

Tutorial video

Have a look at this video for more details

Test a condition with F9

Now, if you want to visualize the result of a test, you have to select the full test (the 2 parts of the test and the comparison sign).

Press F9, and the result of the test is displayed in your formula (instead of the formula selected).

Parsing your formula

The great Excel developer team, Roberto Mensa, Krisztina Szabó and Gábor Madács, have created a very nice tool to inspect and parse your formulas. This makes it easier to visualize the different parts of your formula.

This is a free tool that you can download it at this address.

Permanent link to this article:

Leave a Reply

Your email address will not be published.