If you’re heading into an investment banking analyst role, Excel is going to be your primary tool for the next two or three years. Not PowerPoint. Not Bloomberg (though that matters too). Excel.
I’ve worked with hundreds of students preparing for IB analyst roles, and Excel proficiency is consistently one of the gaps that separates candidates who hit the ground running from those who struggle in their first few months. Banks don’t teach you Excel — they expect you to already know it. And the level they expect is much higher than what most students come in with.
This guide covers the essential Excel skills every IB analyst needs, how banks test for Excel proficiency, and the fastest way to get up to speed.
Why Excel Matters So Much in Investment Banking
Investment banking is fundamentally a quantitative profession. Every deal — M&A advisory, IPO, debt financing, restructuring — involves building financial models, analyzing data, and creating outputs that inform major corporate decisions. Excel is the tool that makes all of that possible.
As a first-year analyst, you’ll spend enormous amounts of time building and updating financial models, running scenario analyses, pulling and cleaning data, and formatting outputs for client presentations. Speed and accuracy in Excel directly translate to analyst performance — and to how much your seniors trust you with progressively more complex and interesting work.
The analysts who are genuinely excellent at Excel get noticed quickly. They produce cleaner work, they make fewer errors, and they finish faster — which means they get more responsibility and develop more quickly. Excel proficiency isn’t a nice-to-have. It’s a core competency.
Core Excel Skills for Investment Banking Analysts
Navigation and Keyboard Shortcuts
This is the most underrated Excel skill, but it’s immediately visible to any senior banker watching you work. IB analysts almost never use the mouse for spreadsheet navigation. Everything is done with keyboard shortcuts — and if you’re mousing around to select cells and click ribbons, seniors will notice.
Essential shortcuts to master:
- Ctrl + Arrow Keys: Jump to the edge of a data range in any direction
- Ctrl + Shift + Arrow: Select to the edge of a data range
- Ctrl + D / Ctrl + R: Fill down / fill right
- Ctrl + [ / Ctrl + ]: Navigate to precedent / dependent cells (critical for model auditing)
- F2: Enter edit mode on a cell
- F4: Toggle absolute/relative cell references (you will use this constantly)
- Ctrl + 1: Open the Format Cells dialog
- Alt + = : AutoSum
- Ctrl + Z: Undo (obvious, but worth listing)
Memorize these and practice them until they’re completely automatic. The goal is that your hands go to the keyboard automatically without thinking about it.
Cell References: Absolute vs. Relative
Understanding absolute ($A$1), relative (A1), and mixed ($A1 or A$1) cell references is fundamental to building models that work correctly. Using the wrong reference type causes cascading errors throughout a model — and senior bankers will catch these immediately.
The rule of thumb: use relative references when you want a formula to adjust as you copy it down/across. Use absolute references when a formula should always point to the same cell (like a WACC assumption in a cell at the top of the model).
Essential Formulas
There are dozens of Excel functions, but a subset of them comes up constantly in IB work. Master these:
- IF / IFS / IFERROR: Conditional logic — essential for model toggles and error handling
- VLOOKUP / HLOOKUP / INDEX-MATCH: Looking up values across tables. INDEX-MATCH is more flexible and is preferred by experienced modelers
- SUMIF / SUMIFS / COUNTIF / COUNTIFS: Conditional aggregation of data
- ROUND / ROUNDUP / ROUNDDOWN: Rounding outputs — important for clean model formatting
- MAX / MIN: Often used in debt schedules (e.g., minimum of available cash and required debt paydown)
- OFFSET: Dynamic range reference — used in rolling models and dashboards
- CHOOSE: Scenario toggling based on a numbered input
- NPV / IRR / XIRR: Core functions for DCF and LBO models
- DATE / YEAR / MONTH / EDATE: Date manipulation for timeline-based models
Pivot Tables
Pivot tables are the fastest way to summarize and analyze large datasets. As an analyst, you’ll frequently receive large datasets (trading comps data, target company data, market data) that need to be quickly organized. Pivot tables let you do this in minutes rather than hours.
Learn how to create pivot tables, filter data, add calculated fields, and format outputs cleanly. This skill is especially valuable in capital markets groups that deal with large transaction databases.
Formatting and Presentation
IB models need to look professional. Clean, consistent formatting is not optional — it’s how bankers communicate that they take pride in their work and how seniors can quickly review a model. Standard IB model formatting conventions:
- Blue font for hardcoded inputs: Any number you type directly into a cell should be blue. This makes it immediately clear what’s an input vs. a formula.
- Black font for formulas: All calculated cells are black.
- Green or different color for links to other sheets: Cells that reference another tab are often color-coded.
- Consistent number formatting: Commas for thousands, consistent decimal places, parentheses for negatives.
- Gridlines off in presentation models: Many banks turn gridlines off for client-facing Excel outputs.
Data Validation and Error Checking
Models break. Assumptions change. The ability to quickly audit a model, find errors, and verify that it’s working correctly is a critical skill. Learn to use:
- Trace Precedents/Dependents (Ctrl+[, Ctrl+]): Shows you what cells feed into or are fed by a given cell
- Show Formulas (Ctrl+`): Displays all formulas in the sheet instead of calculated values — useful for spotting inconsistencies
- Conditional Formatting: Useful for flagging errors or highlighting specific values in large datasets
- Balance Sheet checks: Always build a check row in your three-statement model that confirms assets = liabilities + equity at every period
Advanced Excel Skills That Separate Good Analysts from Great Ones
Scenario and Sensitivity Analysis
Most financial models include scenario analysis (base/bull/bear cases) and sensitivity tables (showing how a valuation changes across a range of WACC and growth rate assumptions). Learn to build clean scenario toggles using CHOOSE or data validation dropdowns, and learn Excel’s Data Table function for generating two-variable sensitivity tables.
Dynamic Model Architecture
As models get more complex, architecture matters. Well-built models are easy to navigate, clearly separated between inputs and outputs, and structured so that changes flow correctly through the model. Learn to build models that a senior banker can open and understand without you explaining it — that’s the standard.
VBA Basics
You don’t need to be a VBA programmer to be an IB analyst. But basic macro knowledge — recording macros to automate repetitive tasks, understanding what VBA code does — is useful, especially for analysts who work on high-volume deals or data-intensive processes. Banks vary in how much they use VBA, but it’s never a negative to know it.
How Banks Test Excel Skills in Interviews
Most IB interviews don’t include a live Excel test — unlike some PE interviews, where you might be given a modeling case study. However, Excel skills become visible very quickly once you’re in the seat as an intern or analyst.
Some boutique banks and certain technical interview formats will include Excel exercises — building a simple comps table, formatting a dataset, or building a basic DCF. If you’re interviewing at a firm known for technical rigor, ask around about their specific interview format so you know what to expect.
For most banks, the interview test is conceptual: can you describe how you would build a financial model? Do you understand the mechanics? The actual keyboard skills come out on the job.
How to Develop Your Excel Skills
The fastest way to develop Excel proficiency for IB:
- Build real models from scratch: Download 10-K filings from the SEC and build three-statement models for companies you’re interested in. Nothing accelerates Excel skills faster than actually building something.
- Use a structured course: Wall Street Prep and BIWS both include Excel training as part of their financial modeling courses. The Excel sections are particularly valuable for developing clean modeling habits early.
- Force yourself to use keyboard shortcuts: For the next month, every time you reach for the mouse in Excel, stop and do it with a keyboard shortcut instead. It’s painful at first and dramatically faster after a few weeks.
- Model publicly available deals: Find a recently announced M&A transaction, read the press release and investor presentation, and try to reverse-engineer the deal model.
Our free resources page includes Excel practice exercises and other technical prep materials. And if you want to understand exactly what skills your target banks expect and how to develop them efficiently, that’s something we work through in personalized coaching. Check our track record and testimonials to see how our students have performed.
Excel vs. Other Tools in Modern IB
A quick note: as the industry evolves, tools like Python, Tableau, and various BI platforms are becoming more common in some banking workflows — particularly in data-heavy roles and at firms investing in tech capabilities. But Excel remains the dominant tool in IB, and that’s not changing anytime soon. Master Excel first. Everything else is secondary.
Want Personalized Investment Banking Coaching?
Wall Street Mastermind has helped thousands of students land offers at Goldman Sachs, Morgan Stanley, JPMorgan, and every top bank. If you want personalized coaching to break into IB, apply here to learn more about how we can help you.
