In Excel, you can audit your formula by analyzing each part of your formula with the shortcut F9
When using the F9 shortcut?
Let's say you have a complex formula with a lot of functions VLOOKUP, INDEX, MATCH.
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 add a break line.
How to audit a formula with 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? 🤔
Don't press Enter but to leave edit mode, use
- the Escape key
- or Ctrl+Z
Have a look at this video to see more examples to audit a formula with f9
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).