Investment Banking Excel Shortcuts and Colour Coding: What to Learn First

A senior banker opening your model first checks whether they can read it: which numbers are assumptions, which are calculations, where each line comes from. Colour coding lets them see that at a glance, and investment banking Excel shortcuts let you build it at speed. You need both for timed modelling tests and again in your first week on the desk.

The colour code: blue, black, green

The textbook modelling convention uses three font colours:

Your team may vary it, for example with red for links to a separate file. Learn the house style in your first week, then apply it without exceptions. An ICAEW introduction to financial modelling makes the same point: the choice of colours is a matter of team preference, but each colour has to carry a consistent meaning across the model.

Why banks insist on it

The analyst builds a model, the associate checks it and the VP pressure-tests the assumptions. Colour lets each of them find the inputs in seconds. If a projection row turns blue halfway across, somebody typed over a formula, and the reviewer will ask why.

Colour has one blind spot: a number typed inside a formula. In a modelling test, that kind of hard-code is easy for a grader to spot and hard for you to defend.

Colour a sheet in seconds, then audit it

F5 opens Go To, and Alt+S takes you to Go To Special. Choose Constants, untick everything except Numbers, and press Enter: Excel selects every typed number on the sheet. Set them to blue (Alt, H, F, C opens the font colour menu). Repeat with Formulas for black, then switch the cross-sheet links to green.

The same routine doubles as an audit. Run it across someone else's forecast columns, and any cell it selects there is a hard-code that needs a reason.

Investment banking Excel shortcuts worth learning first

Start with the two dozen below. They are for Excel on Windows; Mac Excel uses different keys for several.

Moving and selecting

• Ctrl+Arrow jumps to the edge of the current block of data; add Shift to select everything along the way.
• Ctrl+Page Down and Ctrl+Page Up move to the next and previous sheet.
• Ctrl+End jumps to the bottom-right corner of the used range, a quick check for stray entries far below your model.
• Shift+Space selects the row and Ctrl+Space selects the column.

Building formulas

• F2 edits the active cell and outlines each cell the formula refers to in its own colour.
• F4, with a reference selected inside a formula, cycles through $A$1, A$1, $A1 and A1. Outside a formula, it repeats your last action.
• Ctrl+R fills right from the first cell of the selection, and Ctrl+D fills down. Select a formula plus the cells to its right, press Ctrl+R, and it covers the whole projection period.
• Ctrl+Enter puts the same entry into every selected cell, and Alt+= inserts a SUM.

Formatting

• Ctrl+1 opens Format Cells, where custom number formats live.
• Ctrl+Shift+! applies a number format with two decimals and a thousands separator; Ctrl+Shift+% applies a percentage.
• Alt, H, 0 adds a decimal place and Alt, H, 9 removes one.
• Alt, H, H opens the fill colour menu.

Auditing

• Ctrl+[ jumps to the cells the active formula refers to, even on another sheet. F5 then Enter takes you back.
• Alt, M, P draws trace-precedent arrows, Alt, M, D traces dependents, and Alt, M, A, A clears them.
• Ctrl plus the grave accent key, left of 1, switches the sheet between values and formulas, which makes an inconsistent row stand out.

Once these are second nature, Microsoft's full list of Excel keyboard shortcuts for Windows has the rest.

Checking a model: what to say out loud

If an interviewer asks how you would check a model a colleague built, this answer shows you know the routine:

"First I'd confirm the colour coding is consistent, so I can find every input. Then I'd run Go To Special for constants across the forecast to catch hard-codes, trace the key outputs back to their drivers, and check that the balance sheet balances and the cash flow statement ties to the change in cash."

Set up PowerPoint's Quick Access Toolbar for pitch books

Pitch book formatting means lining up boxes, logos, text and charts to the pixel, and PowerPoint buries object alignment under Arrange, then Align. You fix that with the Quick Access Toolbar. Press Alt and each command on the toolbar shows a number; press the number and the command runs. The first nine positions take a single digit, so give them to the commands you repeat most.

• Alignment first. Align Left, Centre, Right, Top, Middle and Bottom, then Distribute Horizontally and Distribute Vertically. That fills eight of the nine single-digit slots.
• Colour and layering next. Shape Fill, Shape Outline, Font Colour, Bring to Front and Send to Back.

To add a command, right-click it on the ribbon and choose Add to Quick Access Toolbar. Reorder the list under File, Options, Quick Access Toolbar, where the Import/Export button saves your setup to a file you can load on a new laptop. Importing replaces any customisations already there, so export the old setup first. Excel's toolbar works the same way, which suits commands like Paste Values.

A few built-in PowerPoint shortcuts cover most of the rest: Ctrl+G groups objects, Ctrl+Shift+G ungroups them, Ctrl+D duplicates, and Ctrl+Shift+C then Ctrl+Shift+V copies one object's formatting onto another. You will use all of them on the decks an analyst builds in a sell-side M&A process.

How to make it stick this week

1. Learn five shortcuts a day, in context. Use only the keyboard for those five actions until it feels automatic. At that pace the list takes a working week.
2. Rebuild one model without the mouse. Build the four-step DCF from Walk Me Through a DCF: project five years, fill across with Ctrl+R, anchor the WACC cell with F4, colour it, then audit it with Go To Special and Ctrl+[. For merger-model tests, repeat the drill on a simple accretion/dilution build.
3. Set up both toolbars tonight and export them. Ten minutes tonight beats rebuilding them under deadline.

A clean, readable model is the first impression you make on whoever grades your test, and on the associate who checks your work once you start. More free material is in the Knowledge Base, and new pieces land in IBB Insights.