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