Top Excel Shortcuts For Finance and Modeling From an Ex-JP Morgan Investment Banking Analyst!

Main ideas / lessons

• Excel speed matters in finance modeling: the video emphasizes keyboard shortcuts and “best practices” for building a DCF-style financial model faster.
• Start with correct data and structure before calculating: copy formatted template blocks (e.g., revenue/assumptions/income statement/cash flow/DCF), then link calculations.
• Use keyboard navigation and shortcuts over the mouse whenever possible.
• Build once, then replicate efficiently:
  • Copy formatting and/or values across rows/columns using Paste Special shortcuts.
  • Use anchoring to ensure copied formulas reference the correct year/row.
• Color-code linked inputs/outputs to make auditing easier (e.g., inputs linked from another sheet).
• Use “offset/dynamic” techniques so outputs adjust automatically when assumptions change (the video notes this as a future deep-dive).

---

Methodology & workflow (as demonstrated)

1) Setup: create/prepare the model sheets

1. Start with a blank spreadsheet.
2. Copy template sheets into the new workbook:
   • Navigate to existing sheets and copy them over.
   • Use shift-based selection/copy actions and bring them into the working sheet.
3. Rename the new DCF sheet (e.g., “dcf”).
4. Zoom in for better visibility.
5. Apply layout formatting:
   • Highlight/select all relevant rows/sections.
   • Add borders (e.g., bottom borders/grid styling) and optionally remove gridlines for a cleaner model.
   • Set title font size and boldness.
   • Set column widths consistently (best practice for models).

2) Build the “Assumptions” section

• Create a header like “assumptions” and format it (background/borders/font color).
• Reduce row height for style.
• Insert assumption rows (examples referenced):
  • Valuation assumptions / switches (e.g., growth/margin/tax-related toggles)
• Apply cell formatting:
  • Use color fills/styles for readability.
  • Convert certain columns to percentages vs general numbers.
• Demonstrate format copying:
  • Copy an existing formatted cell and paste format to another cell range to apply consistent percentage formatting, borders, etc.

3) Create financial statements (Income Statement and Cash Flow)

Income Statement build approach

• Unhide columns when needed.
• Copy formatting blocks from an existing statement layout.
• Link each line item to source sheet cells using formulas (use = then navigate to the source cell).
• Growth rate computation:
  • Formula structure: (current year / prior year) − 1
  • Apply percentage formatting afterward.
• Auto-fit vs fixed width:
  • Auto-fit is mentioned, but the stated best practice is to keep columns the same size.

Cash Flow build approach

• Copy cash flow sections with formatting replicated.
• Delete rows that should be blank/not used for some years.
• Compute ratios (examples mentioned):
  • Depreciation/amortization as % of revenue
  • Capex as % of revenue
• Faster navigation:
  • Use Ctrl + arrow navigation to skip empty cells faster.

4) Key concept: Anchoring formulas when copying across/down

• Problem addressed:
  • When copying formulas across, relative references can break (e.g., dividing by the wrong year’s revenue).
• Technique used: F4 anchoring
  • Press F4 repeatedly to cycle through anchoring modes (adds $ signs).
• Desired outcome:
  • Each row should divide by the revenue from the correct corresponding year.
• Additional note:
  • Different anchoring modes anchor different dimensions (column vs cell/row/relative position), so it may require trial and error to ensure correct movement while copying.

5) Build the DCF logic by linking statements + assumptions

• Copy/link DCF sections:
  • Link revenue, growth, margin/EBIT/tax structures to assumptions and calculated statement lines.
• Color code linked values (e.g., purple suggested) and keep formatting consistent:
  • Percent formatting for margins/ratios
  • Italicize percentage/switch areas
  • Set linked cells to distinctive colors for auditability
• Efficient replication of formatting:
  • If one cell is perfectly formatted, copy it and use paste-special formatting across the rest.

6) Copy assumptions into the DCF as values (not formulas)

• The video distinguishes:
  • Assumptions shown via formulas/links vs
  • Copying only the resulting numbers
• Method:
  • Copy assumptions from one sheet and paste them as values into the DCF assumptions area:
    • Paste Special → values (so they become static numbers rather than live formulas).
• Then, separately, introduce “dynamic” behavior for outputs using formulas like OFFSET:
  • Example: a revenue growth switch referenced via an OFFSET pattern so the DCF recalculates based on the correct assumption row.

7) Grouping/hiding sections for clarity

• Demonstrates grouping and hiding/unhiding using outline/group controls to manage screen clutter.

8) Final tuning / consistency

• Adjust decimals consistently (e.g., reduce decimals to one decimal or zero depending on what’s desired).
• Ensure outputs are correctly color-coded (black for computed outputs per the video’s convention).

---

Excel shortcut list (as explicitly presented in the subtitles)

Subtitles may compress/mis-transcribe exact keystrokes; the following are the shortcuts/commands mentioned.

Navigation / switching

• Alt + Tab: switch between Excel sheets/programs.
• Ctrl + Page Up / Ctrl + Page Down: move among worksheet content areas/sections.

Sheet selection/copying into another sheet/workbook

• Ctrl + Shift + Page Down: highlights multiple sheets (example: two sheets).
• Shift + Tab: used after selection to copy/move into the target workbook.
• Shift + Space: creates a copy of a selected row (as described).
• Arrow keys + Shift: used to extend selection.

Zoom / view

• Ctrl + Alt + + / Ctrl + Alt + -: zoom in/out.

Formatting essentials

• Shift + Down: highlight rows.
• Alt + H + B + O: add bottom border.
• Alt + W + V + G: remove grid lines.
• Shift + Space: row selection (used repeatedly).
• Alt + H + F + S: change font size.
• Ctrl + B: bold.
• Alt + H + O + W: adjust column width (then enter a width like 8–9).
• Alt + H + O + H: adjust row height.
• Alt + H + A + C: center alignment.
• Ctrl + Spacebar: select a whole column.
• Ctrl + Z: undo.

Styles / fill / borders

• Alt + H + F + C: choose a fill color / apply color style.
• Alt + H + B variants:
  • Alt + H + B + Y: apply a border style (as described).
  • Alt + H + B + N: remove borders (as described).
• Alt + E + M: used in the sheet-copy workflow.
• Alt + E + S + T: paste special formatting (formatting transfer / “match formatted style”).

Paste special

• Alt + E + S + T: paste special → formatting.
• Alt + E + S + V: paste special → values.
• Also mentioned conceptually:
  • “Paste special on empty cell” to remove formatting / revert to white.

Number formatting and editing

• Ctrl + .: increase decimals / add decimal places.
• Ctrl + ,: decrease decimals.
• Percentage formatting:
  • Subtitles reference a shortcut as “alt h e r” for % conversion; treated as “apply percentage format.”

Clear / delete / remove rows

• Ctrl + Shift + Down Arrow: used to delete/clear blocks (as described).
• Shift + Right Arrow + Delete: delete contents for specific rows/areas (as described).
• “Shift + Down Space” pattern: used for copying a row (as described).
• Shift + Down Arrow and Control + Right Arrow: used in multi-row/multi-cell paste workflows.

Calculations / formulas

• = then navigate with arrows: link cell to another cell.
• F2: check/edit a cell formula (used to confirm correct linking).
• F4: anchoring (cycles anchoring modes; adds $ signs).

Grouping/hiding

• Alt + A + G + G: group/hide (as described).

---

DCF-specific concepts demonstrated

• Growth and margins are calculated as ratios (percentages).
• EBIT/margins feed into taxes and downstream cash flow items.
• Switch/assumption-driven outputs:
  • Changing switches updates derived outputs and ultimately the implied share price.
• Dynamic referencing concept:
  • Use OFFSET so the model references the correct row range for revenue growth.

---

Speakers / sources featured

• Speaker: Ben (ex-JP Morgan investment banking analyst; host of the video)
• Sponsor / company: Wall Street Prep
• Named individual referenced: Andrew Federico (mentioned as Ben’s classmate at JP Morgan / Wall Street Prep)