Excel formulas cheat sheet
The highest-traffic formulas with syntax and examples on one page. Then write each one once in the practice hall — that is what makes it stick.
Full formula list (static, crawlable)
Math & statistics
| Function | Syntax | Example | What it does |
|---|---|---|---|
SUM | SUM(range) | =SUM(B2:B10) | Adds every number in the range |
AVERAGE | AVERAGE(range) | =AVERAGE(B2:B10) | Arithmetic mean (skips blanks and text) |
COUNT | COUNT(range) | =COUNT(B2:B10) | Counts cells that contain numbers |
COUNTA | COUNTA(range) | =COUNTA(A2:A10) | Counts all non-empty cells |
ROUND | ROUND(number, digits) | =ROUND(A2, 2) | Rounds to a set number of decimals |
MIN / MAX | MIN(range) | =MAX(B2:B10) | Smallest / largest value in the range |
SUMIF | SUMIF(range, criteria, sum_range) | =SUMIF(A2:A9, "North", B2:B9) | Sums only rows that match a condition |
AVERAGEIF | AVERAGEIF(range, criteria, avg_range) | =AVERAGEIF(A2:A9, "North", B2:B9) | Averages only matching rows |
Logic & decisions
| Function | Syntax | Example | What it does |
|---|---|---|---|
IF | IF(test, then, else) | =IF(B2>=60, "Pass", "Fail") | Returns one value when the test passes, another when it fails |
AND / OR | AND(log1, log2, …) | =AND(B2>=60, C2="Y") | TRUE when all / any conditions hold |
IFERROR | IFERROR(value, fallback) | =IFERROR(A2/B2, "—") | Swaps formula errors for a clean fallback |
COUNTIF | COUNTIF(range, criteria) | =COUNTIF(B2:B9, ">=60") | Counts cells meeting one condition |
MEDIAN | MEDIAN(range) | =MEDIAN(B2:B10) | Middle value of a sorted list |
Lookup & reference
| Function | Syntax | Example | What it does |
|---|---|---|---|
VLOOKUP | VLOOKUP(key, table, col, FALSE) | =VLOOKUP(E2, A2:C6, 3, FALSE) | Finds the key in the table's first column, returns the col-th column to the right |
XLOOKUP | XLOOKUP(key, lookup, return) | =XLOOKUP(E2, A2:A6, C2:C6) | Modern lookup: any direction, exact match by default |
INDEX | INDEX(range, row_num) | =INDEX(C2:C6, 3) | Returns the n-th cell of a range |
MATCH | MATCH(key, range, 0) | =MATCH(E2, A2:A6, 0) | Returns the position of a key inside a range |
INDEX+MATCH | INDEX(ret, MATCH(key, lkup, 0)) | =INDEX(A2:A6, MATCH(E2, C2:C6, 0)) | Reverse lookup — key on the right, result on the left |
Text handling
| Function | Syntax | Example | What it does |
|---|---|---|---|
LEFT | LEFT(text, n) | =LEFT(A2, 3) | First n characters from the left |
RIGHT | RIGHT(text, n) | =RIGHT(A2, 4) | Last n characters from the right |
MID | MID(text, start, n) | =MID(A2, 5, 4) | n characters starting at position start |
LEN | LEN(text) | =LEN(A2) | Counts characters |
TRIM | TRIM(text) | =TRIM(A2) | Removes stray spaces (leading, trailing, doubles) |
TEXTJOIN | TEXTJOIN(delim, TRUE, range) | =TEXTJOIN("; ", TRUE, A2:A5) | Joins cells with a delimiter, optionally skipping blanks |
CONCAT | CONCAT(t1, t2, …) | =CONCAT(A2, " ", B2) | Glues text together (& does the same) |
TEXT | TEXT(value, format) | =TEXT(A2, "0.0%") | Formats a number as text |
How to actually remember them
Formulas are learned by context, not flashcards. Master one per family first — SUM, IF, VLOOKUP, TRIM — and the rest are variations on the same shape.
FAQ
What are the most-used Excel formulas?
SUM and AVERAGE for math, IF and IFERROR for logic, VLOOKUP, XLOOKUP, and INDEX+MATCH for lookups, LEFT, TRIM, and TEXTJOIN for text cleanup. Each one below includes syntax and a worked example.
Can I print this cheat sheet?
Yes. The table is plain static HTML — print it or save as PDF straight from your browser.
How do I practice after reading this?
Every function here has a matching interactive exercise in the SheetDo practice hall: write the formula on a locked mini sheet and get graded instantly. The links below jump straight there.