Calculating Excel

F9 shortcuts and tips

When you set calculation to manual in Excel you have turned off calculation. You can press F9 at any time to update the calculations. F9 doesn’t turn on calculation, it just does a recalculation.

There are a couple of other uses of the F9 key you might find useful.

Shift + F9   Recalculates the formulas in the current sheet only. Useful if the full file takes a long time to calculate.

Ctrl + Alt + F9    Forces a recalculation of the open workbooks. Use this if there is any doubt that a file has been recalculated.

Ctrl +Shift + Alt + F9    Rechecks dependent formulas and then forces a recalculation.

Note an Excel file can have calculation set to manual. When you open that file it will turn off calculation in Excel for all files. Check the bottom left of the screen. If it has the word Calculate (see image below) it means calculation has been set to manual (turned off).

 

To turn calculation on use the Calculation Options in the Formulas Ribbon tab

 

Using F9 in the Formula bar

When editing a formula you can highlight part of a formula in the formula bar and press F9. This will calculate just that part of the formula and display the result in the formula bar. Make sure you press Esc after you do this otherwise the result will replace the formula you highlighted. You can use Undo to fix this if you forget.

The part of the formula you highlight must be able to be calculated on its own ie it could be entered as a formula by itself.

This technique is useful when debugging formula. You can test each part of the formula to find the part returning an error or to test the results.

 

Please note: I reserve the right to delete comments that are offensive or off-topic.