What Does F9 Do in Excel?

When using Excel, F9 is a function key that performs specific actions depending on the context in which it is used.

This tutorial shows seven uses of F9 in Excel.

Different Uses of F9 in Excel

Below I have a table that shows the different ways you can use the F9 key in Excel (with a detailed example of each use case covered later in the tutorial).

Key CombinationWhat it Does?
F9Recalculates all the formulas in all the sheets in the active workbook
SHIFT + F9Recalculates all the formulas in all the active sheet only
CONTROL + ALT + F9Calculate all worksheets in all open workbooks, even if they have not changed since the last calculation.
CONTROL + ALT + SHIFT + F9Calculate all worksheets in all open workbooks, even if they have not changed since the last calculation (including cells that have been marked not to be recalculated).
F9 (when used in formulas)Gives you the result of the part of the formulas selected in the cell or formula bar
F9 (when used in VBA)Sets the breakpoint in the VBA code
CONTROL + F9Minimizes the Excel window

Now let’s look at each of these shortcuts and how F9 is used in Excel in each of these cases.

Use #1: Recalculate Changed Formulas in All Open Workbooks

Sometimes, when working with large and complex workbooks that take a long time to recalculate, you may want to turn off Excel’s default automatic calculation mode and activate the manual calculation mode.

This makes sure that whenever you make any changes in the worksheet, the recalculation does not happen (which prevents unnecessary calculation delays).

Instead, you can use the F9 key to manually recalculate the formulas in the sheet whenever you want.

The F9 key recalculates formulas that have changed since the previous calculation and formulas dependent on them in all open workbooks.

Let me show you how this works with an example.

Consider the following dataset with formulas to calculate the percent variance in column D.

formula in Excel that we want to recalculate manually

We want to turn on Excel’s manual calculation mode, make changes to the data and then be able to use F9 to manually instruct Excel to recalculate the formulas.

Step #1: Turn on Excel’s Manual Calculation Mode

On the Formulas tab, on the Calculation group, open the Calculation Options drop-down and choose Manual.

change the calculation option to manual

Step #2: Make Changes to the Data

Make changes to the Current Year (USD) values as follows:

  • North region – 550,000.
  • South region – 500,000.
  • East region – 660,000.
  • West region – 400,000.
data set with formulas

Notice that although the values in column C have changed, the percent variance values in column D have not changed because column D’s formulas did not automatically recalculate.

Step #3: Initiate Recalculation by Pressing F9

Select any cell in the worksheet and press F9.

The formulas in column D are recalculated, and the percent variance figures are updated.

F9 has been used to recalculate the formulas

Note: By pressing F9, all the formulas in all open workbooks that have changed since the last calculation are recalculated. Additionally, all formulas dependent on the modified formulas are recalculated.

If the workbook contains many formulas, pressing F9 may take a long time to recalculate all the formulas.

In some cases, Excel may appear frozen or not responding, but it recalculates the formulas in the background.

Also read: Show Formulas (Instead of Formula Result) in Excel (Shortcut)

Use #2: Auditing Formulas

When editing a formula in a cell, we can press F9 to evaluate the selected part of the formula.

Pressing F9 allows us to see the result of a specific part of the formula without calculating the entire formula.

Here’s a dataset with a formula with the XMATCH function inside the CHOOSE function in column B.

al ong formula that needs to be audited

We want to evaluate the XMATCH function inside the CHOOSE function and only see the result of that part.

We proceed as follows:

  1. In the formula bar, select the part of the formula using the XMATCH function.
select the part of the formula you want to audit and then press F9 key
  1. Press F9.

The result of the specific part of the formula we selected is shown in the Formula bar.

result of that part of the formula that was selected

Note: If you have a complex formula with many nested functions or references, pressing F9 may not help you understand how the formula works. In this case, we recommend breaking the formula down into smaller parts or using the formula auditing tools in Excel.

Also read: Calculation Modes in Excel (Manual, Partial, and Automatic)

Use #3: Press Shift + F9 to Recalculate the Active Worksheet

We use the keyboard shortcut Shift + F9 to recalculate formulas in the active worksheet that have been modified since the last calculation and formulas dependent on them. 

Calculating only the active worksheet is helpful when we have a large workbook with multiple worksheets, and we only need to recalculate a particular worksheet we are working on. 

Here are the steps to use the Shift + F9 shortcut to calculate only the active worksheet:

  1. Open the Excel worksheet that you want to calculate.
  2. Ensure the worksheet is active by clicking on any cell.
  3. Press the Shift + F9 keys on your keyboard.

Excel will recalculate all the formulas in the worksheet and update the results.

Note: Sometimes, the shortcut Ctrl + F9 may not work in Excel because another program has reserved it. For example, Snagit uses Ctrl + F9 as the default hotkey for video capture. To fix this conflict, go to the other program and change its preferences. 

Use #4: Press Ctrl + Alt + F9 to Recalculate Formulas in All Open Workbooks

We use the shortcut Ctrl + Alt + F9 to force a recalculation of all formulas in all open workbooks, irrespective of whether they have changed since the last recalculation.

Applying the shortcut means that Excel will go through all the formulas in the open workbooks and update their values accordingly based on the latest data in the cells they refer to. 

Use #5: Press Ctrl + Shift + Alt + F9 to Force Recalculation of All Formulas

We use the shortcut Ctrl + Shift + Alt + F9 to inspect dependent formulas, then recalculate all formulas in all open workbooks despite whether they have changed since the last recalculation.

This keyboard shortcut is known as “force calculation” or “force recalculation.”

Use #6: Press Ctrl + F9 to Minimize the Excel Window

We can use the shortcut Ctrl + F9 to minimize the Excel window, which is reduced to an icon on the taskbar without closing the application.

Minimizing windows allows us to open multiple windows or applications and easily switch between them.

Also read: Full Screen in Excel (Shortcut)

Use #7: Set a Breakpoint in VBA Code

When debugging code in Excel VBA, we can use F9 to set a breakpoint to pause the code at a particular point and observe the state of the variables and other information.

Here’s how we do it:

  1. Press Alt + F11 to open the Visual Basic Editor.
  2. Select the macro we want to debug.
  3. Place the cursor on the line we want to execute and press F9. This process sets a breakpoint on that line of code, as seen in the example below:
F9 to set the break point in VBA
  1. Press F5 to run the code. When the macro reaches the line with the breakpoint, it will pause and highlight the line.
the code would run only till the break point

We can press F8 to execute the line and observe any errors that might be occurring or F5 to continue running the macro to the next breakpoint or end of the macro.

This tutorial showed seven applications of F9 in Excel.

Some functions apply the F9 key alone, while others use the F9 with other keyboard keys. We hope you found the tutorial helpful.

Other Excel articles you may also like:

I am a huge fan of Microsoft Excel and love sharing my knowledge through articles and tutorials. I work as a business analyst and use Microsoft Excel extensively in my daily tasks. My aim is to help you unleash the full potential of Excel and become a data-slaying wizard yourself.

Leave a Comment