Microsoft Excel has so many keyboard shortcuts that it can sometimes be difficult to remember the most useful. One of the most overlooked is F9, which gives you more control over formula calculation in your worksheets.

Recalculating Formulas in Manual Calculation Mode

By default, Excel automatically calculates formulas as soon as changes are made that affect the results. However, you might choose to turn off automatic calculations by selecting “Manual” in the Calculation Options drop-down menu, which is located in the Formulas tab on the ribbon.

The Manual calculation mode is beneficial if you have a large dataset containing lots of formulas, as it prevents Excel from constantly performing large numbers of calculations at the same time. As a result, you’re less likely to face lapses in your spreadsheet’s performance.

Related

3 Ways to Stop Volatile Functions Recalculating in Microsoft Excel

Fix the values generated by volatile functions.

In this mode, cells that have not recalculated to align with changes in the data are reformatted with strikethrough (also known as a stale value), to remind you that the information in those cells is out of sync.

At this point, you can use one of several F9-related keyboard shortcuts to trigger recalculations:

Debugging Complex Formulas

There are various ways to review and debug formulas in Excel.

For example, you can press Ctrl+` (the grave key) to display all the formulas used in each cell. While this is useful if you’re able to quickly scan a formula and manually spot any errors, it doesn’t directly identify any issues. Also, replacing all calculated values with formulas can make a spreadsheet cluttered and difficult to read.

You can also use Excel’s Evaluate Formula tool (Alt > M > V), which—as its name suggests—breaks down a formula into separate components so that you can examine them one by one. However, this tool only lets you evaluate formulas from left to right, and you can’t make edits directly within the Evaluate Formula dialog box. What’s more, this tool can flag up false errors, thus creating more problems than it solves.

Related

How to Evaluate Formulas Step-by-Step in Microsoft Excel

You don’t have to struggle to figure out how a formula works.

However, a lesser-known method for reviewing formulas is the F9 key, which overcomes the above issues by letting you choose which formula to evaluate, allowing you to manually evaluate each part of a formula in any order, and indicating which part is leading to the error.

In this example, imagine you’ve created a dataset in cells A1 to F11, and you want to use the lookup table in cells H1 to I4 to extract key information. You’ve used the INDEX-MATCH-MATCH combination to generate the result in cell I4, but at the moment, it’s returning the #N/A error:

=INDEX(B2:F11,MATCH(I2,A2:F11,0),MATCH(I3,B1:F1,0))

Related

How to Use INDEX and MATCH in Microsoft Excel

When VLOOKUP doesn’t cut it, you have another option for lookups in your spreadsheet.

To debug this formula, select cell I4, and press F2 to enter Edit mode. Now, you can select any part of the formula—by either using your mouse or pressing Shift+Arrow keys—to evaluate it.

First, select the final argument in the formula—MATCH(I3,B1:F1,0).

Then, press F9 to reveal what that part of the formula will return when you press Enter. You would expect this to return 2, because Cost is the second measure in row 1.

Since this part of the formula returned what you expected, you know that this is not what’s causing the problem.

However, you don’t want this value to be hard-coded into your formula, as you want the lookup to change depending on the measure selection in cell I3. So, press Ctrl+Z to revert the formula to its original form. Pressing Esc also cancels the F9 conversion, though you’ll need to press F2 again to return to Edit mode.

Related

Why You Should Avoid Hard-Coding Values in Microsoft Excel Formulas

Referencing cells or named ranges is the way forward.

Next, select the penultimate argument—MATCH(I2,A2:F11,0)—and press F9 to evaluate it. This should return 6, because 2020 is the sixth year in column A.

However, it returns the #N/A error, so you know that this part of the formula is causing the issue. This means you can focus your attention on the first MATCH argument, and work out why it’s returning an error.

To do this, first, press Ctrl+Z to revert the formula to its original form. After doing this, you’ll notice that the lookup array references cells A2 to F11, where, in fact, it should only reference cells A2 to A11. So, amend the formula accordingly.

Then, select that part of the formula again, and press F9 to verify that it returns the result you expect.

Once you’ve confirmed that it does, press Ctrl+Z to return the formula to its original form, and press Enter to see the new result:

=INDEX(B2:F11,MATCH(I2,A2:A11,0),MATCH(I3,B1:F1,0))

Minimizing the Excel Window

Pressing Ctrl+F9 minimizes the Excel window, which means that the workbook is temporarily hidden without being closed completely.

This is a handy shortcut if you have sensitive information in your spreadsheet, and you want to quickly hide it from the view of other people. It’s also a useful way of navigating to your desktop using only your keyboard if Excel is the only program you have open.

To restore the Excel window after minimizing it, hold Alt, and then tap Tab until the Excel window is selected. Then, release the keys to continue working on your spreadsheet.

F9 isn’t the only Microsoft Excel keyboard shortcut that can save you time. For example, F1 displays the Excel Help task pane, Ctrl+F1 shows or hides the ribbon, Alt+F1 creates a chart of the selected data, and Alt+Shift+F1 inserts a new worksheet. On the other hand, F4 in Excel lets you toggle between reference types, repeat the last action, repeat a find query, and close the active Excel workbook or window.

Related

Microsoft Excel Keyboard Shortcuts: Printable Cheat Sheet

Excel’s keyboard shortcuts are a real time-saver.