Excel function reference
Thirty functions that carry most real work — chosen because they appear in actual jobs and actual interviews, not to pad a list. Each page has the syntax, a worked example with real numbers, and the mistake people make with it.
Reading these produces recognition. If you want recall, go straight to the practice exercises instead — they are where the learning happens.
Logical Functions
Make a spreadsheet decide: bonuses, flags, thresholds and tiers.
Lookup & Reference
Pull matching values across tables instead of copying them by hand.
- VLOOKUPFinds a value in the first column of a table and returns a value from another column.1 exercise
- XLOOKUPFinds a value in one range and returns the matching value from another — in any direction.3 exercises
- INDEXReturns the value at a given row and column position in a range.2 exercises
- MATCHReturns the position of a value within a range.2 exercises
Text Functions
Split, join and clean the messy text every export produces.
- LEFTReturns a set number of characters from the start of a text value.1 exercise
- RIGHTReturns a set number of characters from the end of a text value.
- MIDReturns characters from the middle of a text value, given a start point.1 exercise
- LENCounts the characters in a text value, spaces included.1 exercise
- FINDReturns the position of one text value inside another. Case-sensitive.1 exercise
- SEARCHLike FIND, but ignores case and accepts wildcards.
- CONCATJoins text values together into one.
- TEXTJOINJoins text values with a separator, and can skip the empty ones.1 exercise
Date & Time
Calculate ageing, tenure, deadlines and month-on-month periods.
Math Functions
Total, round and aggregate numbers the way finance expects.
Statistical Functions
Find averages, extremes and rankings that survive scrutiny.
- AVERAGEReturns the arithmetic mean of a range, ignoring empty cells.1 exercise
- COUNTCounts how many cells in a range contain numbers.1 exercise
- COUNTIFCounts cells that meet one condition.3 exercises
- COUNTIFSCounts cells that meet several conditions at once.1 exercise
- MINReturns the smallest number in a range.1 exercise
- MAXReturns the largest number in a range.1 exercise
- LARGEReturns the nth largest value — the third-best rep, not just the best.1 exercise
- SMALLReturns the nth smallest value in a range.
- MEDIANReturns the middle value — the one an outlier can't drag around.