Excel — Cheat Sheets

Part 1

Shortcuts

Keyboard shortcuts for getting around, editing, formatting, and structuring spreadsheets without touching the mouse.

Navigation

Get around big spreadsheets fast.

Jump to the edge of the data block in any direction

Ctrl+↑ ↓ ← →

If empty, jumps to next non-empty cell. If filled, jumps to the last filled cell.

Select from current cell to the edge of the data IB

Ctrl+Shift+↑ ↓ ← →

Bread-and-butter way to grab an entire column or row.

Go to cell A1

Ctrl+Home

Go to a specific cell or named range (Go To dialog)

F5orCtrl+G

F5 → Special finds blanks, formulas, constants.

Switch between worksheet tabs

Ctrl+PgUp/PgDn

Editing & References

The keys you'll hit hundreds of times a day.

Edit the active cell

Drops cursor inside the formula.

Toggle absolute / relative references ($A$1 ↔ A$1 ↔ $A1 ↔ A1) IB

The single most-used key in IB modeling.

AutoSum

Alt+=

Paste Special dialog IB

Ctrl+Alt+V

Then a single letter: V=values, F=formulas, T=formats, E=transpose.

Undo / Redo

Ctrl+Z/Ctrl+Y

Formatting

IB shops are obsessive about formatting consistency.

Format Cells dialog IB

Ctrl+1

Number formats, alignment, fonts, borders — all in one place.

Bold / Italic / Underline

Ctrl+B/I/U

Number with thousands separator (1,234.00) IB

Ctrl+Shift+1

Standard "money-but-not-currency" IB format.

Currency ($1,234.00)

Ctrl+Shift+4

Percentage

Ctrl+Shift+5

Apply borders via the borders menu

AltthenH,B

Outline border alone: Ctrl+Shift+7.

View & Audit

Navigating big models and tracing where numbers come from.

Freeze panes

AltthenW,F,F

Click below and right of where you want the freeze line first.

Hide / Unhide rows

Ctrl+9/Ctrl+Shift+9

Hide columns

Ctrl+0

Unhide: Alt, H, O, U, L.

Trace precedents IB

Ctrl+[

Ctrl+] for dependents. Jump back with F5, Enter.

Fill & Autofill

Autofill a series (dates, numbers) without dragging

AltthenH,F,I,S

Type the first date, select the range, press the keys. In Series dialog, choose Date and set the step.

Fill Down (copies above into selection)

Ctrl+D

Useful for copying formulas. Doesn't recognize series patterns.

Insert today's date (static)

Ctrl+;

Rows & Columns

Insert a new row (or column) above the selection

Ctrl+Shift++

If a whole row is selected, inserts instantly.

Select the entire row of the active cell

Shift+Space

Delete the selected row, column, or cells

Ctrl+-

Part 2

Functions & Formulas

The formulas that do the actual work — lookups, conditional logic, financial math, date handling, and text manipulation.

Lookup Functions

Pull a value from one place into another based on a matching key.

VLOOKUP

Find a value in the leftmost column of a table, return a value from a column to its right.

=VLOOKUP(lookup_value, table_array, col_index, [range_lookup])

Best for:

Quick lookups where the data layout is fixed
Pulling a single field using an ID

Watch out for:

Can't look left — key column must be column 1
Inserting a column inside table_array breaks it
Slower than INDEX/MATCH on large datasets

Always use FALSE (or 0) as the last argument for exact match.

Animated example — finds Carol's department

=VLOOKUP("E003", A2:D5, 3, FALSE) → Engineering

A (ID)

B (Name)

C (Dept)

D (Salary)

E001

Alice

Marketing

65,000

E002

Bob

Sales

70,000

E003

Carol

Engineering

90,000

E004

Dave

Finance

75,000

Blue = scanning, yellow = match, green = return.

INDEX / MATCH

A more flexible two-function combo — works in any direction, survives column changes.

=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))

How it works:

MATCH finds the row position of the lookup value
INDEX returns the value at that position from the column you choose

Best for IB because:

Doesn't break when columns shift
Can look left as well as right
The IB industry standard

XLOOKUP note: Excel 365/2019+ has XLOOKUP which combines both. Use it personally, but learn INDEX/MATCH for compatibility.

Animated example — INDEX/MATCH style

=INDEX(C2:C5, MATCH("E003", A2:A5, 0)) → Engineering

A (ID)

B (Name)

C (Dept)

D (Salary)

E001

Alice

Marketing

65,000

E002

Bob

Sales

70,000

E003

Carol

Engineering

90,000

E004

Dave

Finance

75,000

Step 1 (blue): MATCH scans column A. Step 2 (green): INDEX returns from column C.

Logical & Conditional Functions

Branching logic and conditional aggregation. Used in basically every IB model.

IF

Branch on a condition.

=IF(condition, value_if_true, value_if_false)

Examples:

=IF(A1>100, "High", "Low")

=IF(Revenue>Budget, Revenue-Budget, 0)

Nesting: If you nest more than 2 deep, switch to IFS or a lookup table.

IFERROR IB

Wraps a formula to show a clean fallback when it errors. Used in every model.

=IFERROR(formula, value_if_error)

=IFERROR(VLOOKUP(A1, Data, 2, FALSE), "Not found")

=IFERROR(B1/C1, 0)

Without it, missing lookups or divide-by-zero blow up your whole model.

Caution: Don't blanket-wrap everything — errors sometimes signal real bugs.

SUMIFS IB

Sum a range only where multiple conditions are met.

=SUMIFS(sum_range, criteria_range1, criteria1, ...)

Example: total Q1 revenue from the North region:

=SUMIFS(Revenue, Region, "North", Quarter, "Q1")

Operators in criteria: ">100", "<=50", "*Inc*" (wildcard).

Sister functions: COUNTIFS, AVERAGEIFS, MAXIFS/MINIFS.

COUNTIFS

Count rows where multiple conditions hold.

=COUNTIFS(criteria_range1, criteria1, ...)

=COUNTIFS(Status, "Closed", Region, "EMEA", Year, 2026)

Financial Functions

The valuation building blocks. XNPV and XIRR handle real (irregular) dates.

NPV & XNPV IB

Present value of cash flows discounted at a rate.

=NPV(rate, cashflow1, cashflow2, ...)

=XNPV(rate, cashflows, dates)

Critical NPV gotcha: Excel's NPV assumes the first cash flow is at the END of period 1. If you have a t=0 investment, add it outside:

=A1 + NPV(rate, B1:Z1)

Use XNPV instead when you have actual dates — uses precise day-count and handles t=0 correctly.

IRR & XIRR IB

The discount rate that makes NPV equal zero — the implied return.

=IRR(cashflows, [guess])

=XIRR(cashflows, dates, [guess])

First cash flow is typically negative (initial investment). Use XIRR for irregular dates.

PMT

Periodic payment for a loan with constant rate.

=PMT(rate, nper, pv, [fv], [type])

Example: monthly payment on a $500k mortgage at 6% APR for 30 years:

=PMT(6%/12, 30*12, 500000)

Result is negative (cash outflow). In LBOs, used to size mandatory debt amortization.

Date Functions

For fiscal periods, partial-year discounting, and anything time-aware.

End of month, optionally offset by N months IB

=EOMONTH(start, months)

=EOMONTH(TODAY(),0) = last day of this month.

Same date N months later

=EDATE(start, months)

Fraction of a year between two dates IB

=YEARFRAC(start, end, [basis])

For partial-year DCF discounting. Common basis: 0 (30/360) or 1 (actual/actual).

Build a date from year/month/day

=DATE(year, month, day)

Today's date (auto-updates)

=TODAY()

Volatile. Use Ctrl+; for static.

Text Functions

Cleaning up data dumps from clients and building dynamic labels.

Format a number as a custom-formatted text string

=TEXT(value, "format")

=TEXT(A1, "$#,##0.00")

Combine text with the & operator

=A1 & " " & B1

Cleaner than CONCATENATE.

Extract characters from left, right, or middle

=LEFT(text, n)

Also RIGHT and MID(text, start, n).

Remove extra spaces

=TRIM(text)

First thing to try when VLOOKUP returns #N/A.

Length of a text string

=LEN(text)

Common Formula Recipes

The handful of formulas you'll write hundreds of times.

Year-over-year growth rate

=(B/A) - 1

CAGR IB

=(End/Begin)^(1/Years) - 1

Discount factor for period n IB

=1/(1+rate)^n

Mid-year convention discount factor IB

=1/(1+rate)^(n - 0.5)

Terminal value (Gordon growth)

=FCF*(1+g) / (r-g)

Margin (gross, operating, net)

=Profit / Revenue

Part 3

IB Toolkit

Conventions, advanced techniques, and IB-specific habits.

Pro Tips & Conventions

The unwritten rules of IB Excel work.

The font color convention (memorize on day one)

Blue — hardcoded inputs
Black — formulas in this same sheet
Green — links to other sheets
Red — links to other workbooks

Blue = fair game. Black = stop, you're about to break a formula.

F4 is your most-used key

A1 — fully relative
$A$1 — fully absolute
A$1 — row locked only
$A1 — column locked only

Paste Special is the second most-used

Ctrl+Alt+V, V, Enter — paste values
Ctrl+Alt+V, T, Enter — paste formats
Ctrl+Alt+V, E, Enter — transpose

Auditing a model someone hands you

F5 → Special → Constants highlights every hardcoded number
Ctrl+[ jumps to a formula's precedents
Ctrl+~ toggles "show all formulas" view

The three reflexes you need before day one

Select column: Ctrl+Shift+↓
Paste values: Ctrl+Alt+V, V, Enter
Toggle reference lock: F4

Custom Number Formats

Press Ctrl+1, choose Custom, paste any of these.

Format string	What it shows	When to use
`#,##0_);(#,##0);-`	1,234 \| (1,234) \| -	Standard IB number format.
`$#,##0.00_);($#,##0.00)`	$1,234.00 \| ($1,234.00)	Dollar amounts, two decimals.
`#,##0,;(#,##0,)`	1,235 (thousands)	One trailing comma divides display by 1,000.
`#,##0,,;(#,##0,,)`	1 (millions)	Two trailing commas = millions.
`0.0%;(0.0%)`	12.5% \| (12.5%)	Percent with one decimal.
`0.0"x"`	1.5x \| 12.4x	Multiples (EV/EBITDA, P/E).
`[Red](#,##0)`	(1,234) in red	Color a negative section.

The underscore trick

The _) at the end of the positive format adds an invisible space the width of a closing paren — so positive and negative numbers line up vertically.

Sensitivity Analysis

Every IB deck has a sensitivity table. Excel's Data Table feature builds them automatically.

Data Table — One Variable

Below your output, list the input values in one column
One column to the right, in the row above the first input, type =output_cell
Select the whole rectangle
Alt, A, W, T
Leave Row input blank; in Column input, click the cell that drives your model

Data Table — Two Variables IB

List one set of inputs in a column, the other in a row
In the corner cell type =output_cell
Select the entire grid; Alt, A, W, T
Row input = top-row variable. Column input = left-column variable.

Format the corner cell as ;;; to hide its formula reference.

Goal Seek

"What input makes my output equal X?" Excel solves it.

Alt, A, W, G
Set cell: the output. To value: the target. By changing cell: the input.

Classic IB use: solve for the offer price that gives a target IRR.

Three-Statement Linking

How the income statement, cash flow statement, and balance sheet tie together.

The four critical links

Net Income flows from the bottom of the IS to the top of the CFS
D&A is subtracted on the IS, then added back on the CFS
CapEx in investing on the CFS, increases PP&E on the BS
Ending Cash from CFS becomes cash on next BS. NI (less dividends) flows into Retained Earnings.

The "tie-out" check

BS must satisfy Total Assets = Total Liabilities + Total Equity. Build a check row: =Assets - (Liabilities + Equity) — should be zero.

Circular References & Calculation Modes

Some IB models intentionally rely on circular formulas.

The classic LBO circularity

Interest expense depends on average debt, which depends on cash flow, which depends on interest expense.

Enable iterative calculation:

File → Options → Formulas
Check Enable iterative calculation
Maximum Iterations ≈ 100, Maximum Change ≈ 0.001

Manual vs. Automatic calculation

Formulas → Calculation Options → Manual
F9 — recalculate workbook
Shift+F9 — only the active sheet
Ctrl+Alt+F9 — full rebuild

Part 4

Reference

Lookup tables and learning aids.

Common Errors & Fixes

Excel's seven main error types and what each tells you.

Error	Means	Most common fix
`#N/A`	Lookup couldn't find a match.	Check spelling. Wrap in `IFERROR`. Try `TRIM` — trailing spaces are #1 cause.
`#REF!`	Reference is invalid (deleted cell or column).	Look at recent deletes. Undo if possible.
`#VALUE!`	Wrong type of argument.	Check that referenced cells contain the type expected.
`#DIV/0!`	Dividing by zero or empty cell.	Wrap in `IFERROR`, or check denominator first.
`#NAME?`	Function or named range not recognized.	Check spelling. Confirm named range exists.
`#NULL!`	Space between two non-intersecting ranges.	Use a comma instead.
`#####`	Column too narrow.	Widen the column — double-click right edge of header.

Memory Tips

How to actually remember all of this.

The big secret: you don't have to memorize them at all

Press Alt on its own and Excel displays every keyboard shortcut directly on the ribbon. Tiny letters appear over each tab and button.

So Alt+H, F, I, S means:

Press Alt → ribbon lights up with hints
Press H → Home tab opens, new letters appear
Press F → Fill menu opens
Press I, then S → confirms Series

Mnemonic for the ribbon tabs

Alt + F → File
Alt + H → Home
Alt + N → iNsert
Alt + P → Page Layout
Alt + M → forMulas
Alt + A → dAta
Alt + R → Review
Alt + W → vieW

Patterns that make Ctrl+ shortcuts easier

Ctrl + Shift + [number] = number formats (1=number, 4=currency, 5=percent, 7=border)
Ctrl + [letter] follows Word equivalents (B/I/U, F find, S save, P print)
Ctrl + 9 / Ctrl + 0 = hide rows / columns (9 tall, 0 wide)
F-keys: F2 edits, F4 locks, F5 goes to, F9 recalculates
+ inserts, − deletes

The two rules of getting fast

1. Force the shortcut even when slower at first. By week three your fingers do it automatically.

2. Sit on your mouse hand. A trick analysts use to force keyboard-only workflows.