Use F9 in the Formula Bar to Test a Formula

January 19, 2022 - by Bill Jelen

Problem: I have a complex formula that does not appear to be providing the correct result. The formula has multiple terms, and I am not sure which part is not working correctly.

A complex formula is shown in the formula bar. — Figure 280. Troubleshoot this formula.

Strategy: You can use F9 to test a formula. Here’s how:

1. Select cell C25 and press F2 to put the cell in Edit mode. In this mode, each cell reference in the formula is color coded. The $C$9 text in the formula is blue, and the outline around C9 is blue.

By pressing F2 to edit the formula, the cells referenced in the formula light up with colored outlines. The cell reference matches the color of the outline. — Figure 281. In Edit mode, the formula references are color coded.

2. To selectively calculate just a portion of the formula, use the mouse to highlight a portion of the formula.

While in edit mode, carefully highlight one part of the formula in the cell. — Figure 282. Select part of the formula.

3. Press the F9 key. The highlighted portion of the formula will be replaced with the current result of the formula.

Press F9 and the highlighted part of the formula will evaluate. In this case, it changes to 81. — Figure 283. Press F9 to calculate the highlighted portion.

4. Press the Esc key to return to the original formula.

Additional Details: If you press F9 without selecting anything, it will calculate the entire formula and replace it in the result.

If you fail to pre-select part of the formula and press F9 while in edit mode, the formula is replaced with the result. — Figure 284. Press F9 to calculate the entire formula.

This article is an excerpt from Power Excel With MrExcel

Title photo by Ben Mullins on Unsplash