Common Excel formulas
IF, XLOOKUP, SUMIF, INDEX/MATCH: the Excel formulas you use 90% of the time, each with an example.
Conditions
=IF(A2>=10, "Pass", "Fail") =IF(AND(A2>=10, B2="present"), "OK", "KO") =IFS(A2>=16,"A", A2>=14,"B", A2>=10,"C", TRUE,"Fail")
Lookup
=XLOOKUP(D2, A:A, B:B, "Not found") =INDEX(B:B, MATCH(D2, A:A, 0))
XLOOKUPreplacesVLOOKUP: the key needn't be in the first column, and exact match is the default.
Conditional aggregation
| Formula | Purpose |
|---|---|
=SUMIF(A:A, ">100") |
Sum values greater than 100 |
=SUMIFS(C:C, A:A, "FR", B:B, ">0") |
Multi-criteria sum |
=COUNTIF(A:A, "yes") |
Count cells equal to "yes" |
=AVERAGEIF(A:A, ">0") |
Average of positive values |
Text and dates
=LEFT(A2, 3) =RIGHT(A2, 4) =MID(A2, 2, 5) =CONCAT(A2, " ", B2) =UPPER(A2) =TODAY() =DATEDIF(A2, TODAY(), "y") (age in years)
Relative, absolute and mixed references
| Notation | Behaviour when the formula is copied |
|---|---|
A1 |
Relative: row and column both adjust |
$A$1 |
Absolute: never moves |
$A1 |
Fixed column, relative row |
A$1 |
Fixed row, relative column |
F4 right after typing a reference cycles through all four variants — no need to type the $ signs by hand.
Handling errors
=IFERROR(A2/B2, "Error") =ISNA(XLOOKUP(D2, A:A, B:B)) =IFERROR(XLOOKUP(D2, A:A, B:B), "Not found")
IFERROR catches any error (#DIV/0!, #N/A, #VALUE!...) and shows a fallback value instead — much more readable than a raw error message in a final table.
Statistical and array functions
| Formula | Purpose |
|---|---|
=LARGE(A:A, 1) |
The largest value (2 for the 2nd-largest, etc.) |
=SMALL(A:A, 1) |
The smallest value |
=RANK(A2, A:A) |
The value's rank within the range |
=COUNTA(A:A) |
Count non-empty cells |
=UNIQUE(A:A) |
List of distinct values (Excel 365) |
=SORT(A:A, 1, TRUE) |
Dynamically sort a range (Excel 365) |
Conditional formatting with a formula
In Conditional Formatting → New Rule → "Use a formula", a formula like =$C2<$B2 (applied to a whole row) automatically colors rows where a condition is true — handy for spotting gaps or overruns without manually scanning every row.
Thanks for the feedback!