If you had to pick a distinctive characteristic of the experienced financial modeller and spreadsheet user, it would be their unconscious use of keyboard shortcuts. I’m sure, like me, you’ve been captivated by the blurred fingers of an expert modeller making Excel dance with obscure combinations of Shift and Ctrl. Or even better, overheard a conversation between two modellers: “I just F5-s’d and they’re inconsistent”, “OK, well just Ctrl+Shift+Plus and Alt+H+Double-V a row of hardcodes”.

So why are keyboard shortcuts so important? For one thing, they make you faster at telling Excel what to do, leaving you to concentrate on the commercial problem and the elegant solution. But the greater advantage is that Excel’s keyboard shortcuts are designed to encourage you to use modelling best practices.

As I’ve said before, if you think like Excel, your models will be designed better and your formulas will be simple. But go up against Excel in a fist fight and you’ll be left in a hellish pool of formulas we refer to as the Axis of Spreadsheet Evil (eg OFFSET, INDIRECT, VLOOKUP).

The two most-used Excel shortcuts

I asked my team for their favourite and most-used keyboard shortcuts and there were two clear winners: F5 [the ‘Go to’ key] and F2 [which toggles between Edit mode and Enter mode].

“Excel’s keyboard shortcuts are designed to encourage you to use modelling best practices.”

Use Ctrl+[ to go to precedents

Double-clicking formulas to find the precedents is a bad idea because it doesn’t always work: the cursor often misses, and it requires the mouse. To avoid the mouse and get much more control, use Ctrl+[ to go to precedents.

If there are a few precedents on one worksheet they’ll all be selected, but if the first precedent is offsheet then you’ll be taken to it, which is why we recommend that the offsheet reference is always at the start of any formula. This shortcut is always partnered with F5+Enter to return to the original cell. Elegant.

F5 is your ‘Go to’ button

We can’t rush past F5, because it’s very special. F5 > Alt+S (Special) opens up an amazing world of options that are all a single keyboard press away. See Figure 1. My favourite is Current Array because there’s no other way to know if you have one array formula, or lots of little ones put together.

Figure 1. Click image to enlarge.

Formula editing with F2

F2 puts the financial modeller into “edit mode”. This is where you can select a part of the formula and hit F9 to see what it resolves to (don’t forget to Esc out once you’ve done that). Then use F4 to toggle those $ absolute references on and off.\

Excel shortcuts to move and select cells

Another often-used group of shortcuts are for moving around and selecting cells.

Ctrl+Arrow – to move along the rows of inputs or formula.

Ctrl+Shift+Arrow – to select the rows and edit/copy.

Ctrl+Home and Ctrl+End – these take you to the first active cell and the last active cell respectively. (My team uses Ctrl+Home to reset each page before we release it.)

Alt+Page Up and Alt+Page Down – these shortcuts move you to the far left and far right cells on the worksheet respectively.

Ctrl+Page Up and Ctrl+Page Down – these shortcuts move you around the tabs.

Shift+Space – to select rows.

Ctrl+Space – to select columns.

Ctrl+Minus(-) – once you select a column or row, Ctrl+Minus(-) deletes it.

Ctrl+Shift+Plus(+) – once you have selected a column or row, Ctrl+Shift+Plus(+) inserts a row or column to above or left of the selection.

Magic graphs with Alt-F1

Select some data, hit Alt-F1, and hey presto… a graph! Soon to be replaced by the AI-driven data explore functionality, this is an oldie but a goodie. Not the right graph? Go into Change Chart Type and right-click the chart you prefer and select Set as Default Chart. Sorted. See Figure 2.

Figure 2. Click image to enlarge.

Named Ranges and F3

I have previously sung the praises of named ranges, but to make them truly sing you have to use their F3-orientated keyboard shortcuts.

Ctrl+F3 – opens the named range dialog box.

Ctrl+Shift+F3 – creates named ranges from labels.

Excel shortcut check for formula consistency

Best practice says one formula per row or column. But if you need to check that, use:

Ctrl+\ – to check consistency horizontally for the selection.

Ctrl+Shift+\ – to check consistency vertically for the selection.

Ctrl+~ – this shortcut makes all your formulas visible (and is mostly used to prank colleagues). See Figure 3.

Figure 3. Click image to enlarge.

Do shortcuts reveal your personality type?

It’s possible that this variety of favourite shortcuts mean that the question could be used as an ultra-revealing personality test for financial modellers, but what’s more likely is that the favourites are varied because of the wide range of work in our profession.

Nil-By-Mouse**

Unfortunately, there are a few things you need a mouse for, such as double clicking the Trace Dependents arrow showing a reference to another worksheet to get the Go To box up, but thankfully they are few and far between. Hardcore modellers have mostly written themselves a macro to avoid these mouse-attacks anyway.

(** A joke first made by inaugural ModelOff financial modelling world champion Alex Gordon.)

Top tip

When you’re buying a laptop, try to get a device that features standalone Pg Up, Pg Dn, Home and End buttons on its keyboard, rather than have them merged with the arrow keys. Or get an external keyboard.

Need an Excel shortcut cheat sheet?

There are a few good cheat sheets around. If you want PwC’s just contact me on social media. [Twitter @ianrbennett or message me on LinkedIn.]

Your questions answered

Have a financial modelling or spreadsheet question you’d like Ian Bennett to tackle in an upcoming issue of Acuity? Send it to [email protected] with ‘Ask Ian’ in the subject line.

Read more: