Excel: Use F9 in the Formula Bar to Test a Formula

This page is an advertiser-supported excerpt of the book, Power Excel 2010-2013 from MrExcel - 567 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.

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.

  1. 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.

  2. In Edit mode, the formula references are color coded.
    1. To selectively calculate just a portion of the formula, use the mouse to highlight a portion of the formula.

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

  4. Press F9 to calculate the highlighted portion.
    1. 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.

  5. Press F9 to calculate the entire formula.