Hello Excellers, time for some more formula fun. If you’re an Excel user, you know that formulas are the key working smart with Excel. But what happens when you’re not sure if your formula is working right? Fortunately, Excel has a handy way to test your formulas without having to enter them into a spreadsheet. Just hit the Excel F9 key and Excel will show you the result of your formula. So in this blog post I show show you how to use F9 in Excel. Stay tuned! So, let’s look at checking our formulas to ensure they are working as expected.
If you work on a large spreadsheet solution, it is really handy to be able to break down formulas into bite size chunks. This is especially useful if formulas are complex. This helps to pinpoint any errors that you may have. As always let’s use an example to work through. The screen grab shows an extract of transactions from January to March 2016. The data contains a calculated field to check if a target has been met. The calculated column of formula is in Column E.
First, I always spreadsheet to see where the formulas are, especially if I have inherited a workbook. So, step one for me is to find the Excel formulas.
Find Excel Formulas Fast
To quickly see where the formulas are on your worksheet
- Hit F5 | Goto Special
- Select Formulas
We can see there is one calculated column in the worksheet- Column E. So, this is the area of the Excel worksheet to debug.
I find it easiest to debug a formula right there in the formula bar. However, if the formula is long then you can expand the bar by dragging the bottom of the formula bar down to expand it. Just like below.
The example formula is small so it is easy to activate edit mode by selecting the formula bar Now, you could manually select the differents parts of the formula, but I find it easier to use the tool tip which is more precise. You can see in the image below, I have selected the logical test part of the formula.
Using F9 To Check Excel Formulas
Once selected, if I hit F9 then I can see right in the formula bar the calculation that Excel has carried out and the result of that part of the formula, which in this case is FALSE as the value of D2 is less not less than 1000, therefore the result is FALSE and NO is displayed.
Once you are done with the debugging, just hit ESCAPE to exit the formula editing without making any changes, of course if you need to make changes you know exactly where to change.
If you are ever stuck with an Excel formula and need to debug it, the F9 key can be a lifesaver. By pressing this key, you will see the results of each step of the calculation process in your formula. This can help you to quickly identify and fix any errors in your equation. So, that’s how to simply step through and debig your formulas right in the formula bar.