In Excel, you can audit your formula by analyzing each part of your formula with the shortcut F9.
- This technique is crucial for debugging a formula error.
- It allows you to scan any part of your formula and analyze the result.
- This article details the technique and the mistakes to avoid.
When using the F9 shortcut?
Let's say you have a complex formula with many functions VLOOKUP, INDEX, MATCH. However, the result returned is incorrect, and you must find the reason.
Tips: Press Alt + Enter to add a break line to display your formula on multiple rows.
How do you audit a formula with the F9 key?
- Select a 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 you press Enter at this step, the result will overwrite this part of your formula
- You will lose the formula you selected.
So, what do I do? 🤔
Don't press Enter but to leave edit mode, use
- the Escape key
- or Ctrl+Z
Tutorial video
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 test result will be displayed in your formula (instead of the formula selected) 😉👍
UPDATE Excel 365
A recent Excel 365 update integrates this feature directly when you select a part of your formula.
- The result of the selection appears directly in a tooltip
- The size of the font is very small 🧐
- This is why F9 is still useful
