Quick Links

• Recalculating Formulas in Manual Calculation Mode

• Debugging Complex Formulas

• Minimizing the Excel Window

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.

PostsIn 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:

Keyboard Shortcut	What It Does	What It Affects
F9	Recalculates all formulas that are new, volatile, or dependent on values that have changed since the last recalculation	All worksheets in all open workbooks
Shift F9	Recalculates all formulas that are new, volatile, or dependent on values that have changed since the last recalculation	The active worksheet only
Ctrl Alt F9	Recalculates all formulas, regardless of whether they're new, volatile, or the values have changed	All worksheets in all open workbooks
Ctrl Alt Shift F9	Checks dependent formulas, and then recalculates all formulas, regardless of whether they're new, volatile, or the values have changed	All worksheets in all open workbooks

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.

PostsHowever, 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:

<code>=INDEX(B2:F11,MATCH(I2,A2:F11,0),MATCH(I3,B1:F1,0))</code>

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.

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

Posts 1 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:

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

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

The above is the detailed content of Don't Ignore the Power of F9 in Microsoft Excel. For more information, please follow other related articles on the PHP Chinese website!