When you think your Excel is not calculating… Stay Calm and Press F9.
I have to admit that I did not know about “Manual Calculation” mode in Excel long long time ago. I still remember how stupid I was when I complained to the interviewer about the “bug” in her PC that made Excel do not calculate. Yes, I was put on an Excel test and for whatever reason the Excel was set to “Manual calculation”. What’s the point of the test??? @_@ I failed the interview of course.
Now I know and I would pay attention to the status bar. Indeed, the information on status bar is sometimes really helpful!
I guess the main reason for users to turn Excel to manual calculation is the low efficiency of the spreadsheet they are working work. Why the spreadsheet is of low efficiency? This is a big topic which is beyond the scope of this post. Nevertheless, I would recommend the Decision Models site by Charles Williams if you wish to understand more about how Excel calculates.
Let’s come back to what F9 does…
The following is extracted from Office Support directly:
- F9 – Calculates all worksheets in all open workbooks.
- SHIFT+F9 calculates the active worksheet.
- CTRL+ALT+F9 calculates all worksheets in all open workbooks, regardless of whether they have changed since the last calculation.
- CTRL+ALT+SHIFT+F9 rechecks dependent formulas, and then calculates all cells in all open workbooks, including cells not marked as needing to be calculated.
- CTRL+F9 minimizes a workbook window to an icon.
One important use of F9 however is not mentioned:
- In formula bar, F9 returns the result of a formula (or part of a formula depends on your selection) instanly.
Let’s take a look one by one:
F9 – Calculates all worksheets in all open workbooks
This should be good enough to give you updated results of your formulas.
However if it takes too long to update AND you just want to see the updated results of your current worksheet, you may consider using
SHIFT+F9 to calculate the active worksheet.
CTRL+ALT+F9 calculates all worksheets in all open workbooks, regardless of whether they have changed since the last calculation.
CTRL+ALT+SHIFT+F9 rechecks dependent formulas, and then calculates all cells in all open workbooks, including cells not marked as needing to be calculated.
For the two points above, I do not have an example to illustrate. I think you will need it to evaluate the efficiency of your workbook. Please feel free to share your views in comment.
CTRL+F9 minimizes a workbook window to an icon
This one is quite clear I believe. It minimize the active workbook. Just to remind, to restore its size, press Ctrl+F5; to maximize it, press Ctrl+F10;
Well, here come the important use of F9:
In formula bar, F9 returns the result of a formula (or part of a formula depends on your selection)
Using together with F2, it converts a formula into a value quickly. i.e select the cell with the formula, Press F2 followed by F9.
If you are dealing with more than one cell, Copy and Paste Value would absolutely be more efficient.
To evaluate part of the formula, the first thing is to select the part you want to evaluate. Normally this can be done by mouse drag. But there is a far more efficient way to do so if the part you want to exam is actually an argument of a function. See screenshot below:
This technique is a must-know if you need to write nested functions, or array formula.
You may find another example of using F9 in formula bar to combine contents from different cells into a single cell HERE.
To make it short,
- you don’t need to worry about F9 if your Excel is set to calculate automatically;
- in case your spreadsheet is set to “Manual calculation”, remember to press F9 for updated results; or just SHIFT+F9 if you want to see updated results on active worksheet only;
- when using in formula bar, F9 evaluates (part of) your formula instantly.
Note:
The shortcut combinations stated above have been tested with Excel 2010. If you find it doesn’t work in other version of Excel, please let us know by leaving your comment.
Also feel free to share with us your tips in using F9.
For other Function keys:
wmfexcel 