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 Combination | What it Does? |
|---|---|
| F9 | Recalculates all the formulas in all the sheets in the active workbook |
| SHIFT + F9 | Recalculates all the formulas in all the active sheet only |
| CONTROL + ALT + F9 | Calculate all worksheets in all open workbooks, even if they have not changed since the last calculation. |
| CONTROL + ALT + SHIFT + F9 | Calculate 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 + F9 | Minimizes 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.
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.
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.
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.
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.
We want to evaluate the XMATCH function inside the CHOOSE function and only see the result of that part.
We proceed as follows:
- In the formula bar, select the part of the formula using the XMATCH function.
- Press F9.
The result of the specific part of the formula we selected is shown in the Formula bar.
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:
- Open the Excel worksheet that you want to calculate.
- Ensure the worksheet is active by clicking on any cell.
- 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:
- Press Alt + F11 to open the Visual Basic Editor.
- Select the macro we want to debug.
- 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:
- Press F5 to run the code. When the macro reaches the line with the breakpoint, it will pause and highlight the line.
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:
