Exceljet

Quick, clean, and to the point

This video comes from our online video training for Excel.

How to check and debug a formula with F9

Tags 
Summary 
In this brief video, we look at how to debug a formula using the F9 key. If you work with formulas a lot, this is one of the most useful skills you can learn.
Video Transcript 

One thing you'll frequently do in Excel is check or debug formulas.

In this video, we'll look at how to use the F9 key to quickly break a formula down into pieces that you can understand.

Here we have a simple list of names. In addition to names, we have a column for birthday, a column for age, and a column for legal status.

We've covered this before, but whenever you inherit a new worksheet, make sure you understand first where the formulas are. You can can do this quickly by using goto special > formulas.

You can see that both Age and Status are calculated values.

Now let's use the F9 key to see what the Status formula is doing.

In general, it's easier to debug a formula in the formula bar. And you may want to expand the formula bar to give yourself more room to work.
 
Once you're in edit mode, you can use the F9 key to check the calculated value of any part of the formula.

Although you can parts of the formula manually, the function tooltip makes it much easier to select things precisely.

To display the tooltip, click directly on or inside any function. For example, when we click anywhere inside the IF function, we see its three arguments listed in the tooltip, and we can simply click to select each argument in the formula.

=IF(E5>20,$H$6,$H$5)

Once an argument is selected, press the F9 key to evaluate. In this case, H5 evaluates to "Minor", H6 evaluates to "Adult", and the logical test evaluates to FALSE, since Michael is only 12 years old.

Press the escape key to exit the formula editor without making changes.

Pay attention to your selection before you press F9. If the cursor is outside the last set of parentheses, you'll see only the final result of the formula when you use F9. Again, press Escape to exit without making changes.

Now let's look at the formula that calculates Age.

=INT(YEARFRAC(D5,TODAY(),1))

You can see that the formula uses two functions: YEARFRAC and INT.

Working from the inside out, we see the start date and end date both evaluate to date serial numbers. Next, the YEARFRAC function returns years as a fractional value. And finally, the INT function gives us just the integer value, which corresponds to a person's age as of today.

Use the F9 key whenever you need to understand or debug a formula. It's a great way to quickly understand how a formula works, and it will save you the trouble of breaking the formula down into separate formulas on the worksheet.

Author 
Dave Bruns

Related shortcuts

Your YouTube videos ROCK!! Thanks for producing them with such quality and brevity. -Mike
Excel foundational video course
Excel Pivot Table video training course
Excel conditional formatting video course
Excel formulas and functions video training course
Excel Shortcuts Video Course