Tempting as it is to entitle this blog “All twelve function keys in Excel – ranked!”, I’m going to rein in my enthusiasm to share with you a more sober reflection on the utility (or not) of the 12 keys at the top of your keyboard. I’m restricting my comments to how they work in Excel – and, where relevant, their role in financial modelling.
Before you start, it’s worth identifying whether you have a Function Lock (“FnLock”) available to you. On my ThinkPad keyboard, holding down “Fn” and “Esc” will apply it: a little light appears on the Esc key to indicate that things have been switched on.
If you don’t have a function lock available to you, then you will need to hold down the “Fn” key every time you wish to apply the functions described below. Otherwise, you will find that you are turning airplane mode on or reducing the volume or increasing screen brightness.
Here are the function keys that have a role in financial modelling (with some comment on their utility).
F1
Offers help. I’ve yet to find anyone who likes it; I’ve met plenty of people that hit it by accident and then curse.
Sometimes the frustration is so intense, a user will remove the key with a screwdriver.
F2
Edit a cell. This one is useful – in particular if you change your settings to remove “edit directly in cell” so that your eye is drawn, every time, to the same place: the formula bar.
Shift + F2 to add a comment.
F3
Ctrl + F3 will bring up the Excel name manager. Of occasional use.
F4
An essential piece of kit to help you in anchoring cell references:
- Double anchored / absolute reference (1x)
- Row anchored (2x)
- Column anchored (3x)
- No anchors / relative reference (0 or 4x)
As a bonus, hitting F4 will also repeat a previous action. I find this of limited use, but plenty of people like it.
F5
Brings up the “Go To” menu. This is helpful: type a cell reference and hit OK to go there.
For model review, the “Special” sub menu is essential: try out constants, formulas and row differences in financial models. Being able to tab through the selected cells is helpful – as is being able to give them all, in one stroke, a new background colour for ease of identification.
F8
Alt + F8 will bring up a list of macros. This is occasionally helpful.
More helpful is using F8 to enter “break mode” when reviewing macro coding in the visual basis editor.
F9
Will recalculate all stale cells in all open workbooks. A stale cell is one whose results will change as a result of hitting F9.
Try Shift + F9 to recalculate stale cells in a single worksheet; try Ctrl + Alt + F9 to recalculate all cells, stale or not, in all open workbooks.
Ctrl + Alt + Shift + F9 also rebuilds the dependencies in the model’s links.
F10
Shift + F10 is a useful shortcut for a right click (without using the mouse).
F11
A strong contender for the most useful. Highlight data and use F11 to produce a quick chart in a separate worksheet.
Alt + F11 is a useful shortcut for opening up the visual basic environment.
F12
A quick way of bringing up the “Save As” menu.
I know of no use for F6 nor F7. If I’ve missed something, then please comment below to let me know.
Finally, I’m often asked: “Why F1F9?” The answer is in the blog:
- F1 – Help
- F9 – Calculate
Here at F1F9, we help calculate…
