Formula Reference
Quick Lookup
This reference covers the most commonly used Excel functions, organized by category.
Math Functions
| Function | Syntax | Description |
|---|
| SUM | =SUM(range) | Add all numbers |
| AVERAGE | =AVERAGE(range) | Calculate mean |
| MIN | =MIN(range) | Smallest value |
| MAX | =MAX(range) | Largest value |
| COUNT | =COUNT(range) | Count numbers |
| COUNTA | =COUNTA(range) | Count non-empty |
| ROUND | =ROUND(number, decimals) | Round to decimals |
| ROUNDUP | =ROUNDUP(number, decimals) | Round up |
| ROUNDDOWN | =ROUNDDOWN(number, decimals) | Round down |
| ABS | =ABS(number) | Absolute value |
| SQRT | =SQRT(number) | Square root |
| POWER | =POWER(number, power) | Raise to power |
| MOD | =MOD(number, divisor) | Remainder |
| INT | =INT(number) | Integer part |
Conditional Functions
| Function | Syntax | Description |
|---|
| IF | =IF(test, if_true, if_false) | Conditional result |
| IFS | =IFS(test1, value1, test2, value2, ...) | Multiple conditions |
| SUMIF | =SUMIF(range, criteria, sum_range) | Sum with condition |
| SUMIFS | =SUMIFS(sum_range, range1, criteria1, ...) | Sum with multiple conditions |
| COUNTIF | =COUNTIF(range, criteria) | Count with condition |
| COUNTIFS | =COUNTIFS(range1, criteria1, range2, criteria2, ...) | Count with multiple conditions |
| AVERAGEIF | =AVERAGEIF(range, criteria, avg_range) | Average with condition |
| AVERAGEIFS | =AVERAGEIFS(avg_range, range1, criteria1, ...) | Average with multiple conditions |
| IFERROR | =IFERROR(value, error_value) | Return alternative if error |
| IFNA | =IFNA(value, na_value) | Return alternative if #N/A |
Lookup Functions
| Function | Syntax | Description |
|---|
| VLOOKUP | =VLOOKUP(lookup, table, col, FALSE) | Vertical lookup |
| HLOOKUP | =HLOOKUP(lookup, table, row, FALSE) | Horizontal lookup |
| XLOOKUP | =XLOOKUP(lookup, lookup_range, return_range) | Modern lookup |
| INDEX | =INDEX(range, row, col) | Return value at position |
| MATCH | =MATCH(lookup, range, 0) | Find position |
| OFFSET | =OFFSET(ref, rows, cols, height, width) | Dynamic reference |
| INDIRECT | =INDIRECT(reference_text) | Reference from text |
| CHOOSE | =CHOOSE(index, value1, value2, ...) | Return nth value |
Text Functions
| Function | Syntax | Description |
|---|
| CONCAT | =CONCAT(text1, text2, ...) | Join text |
| TEXTJOIN | =TEXTJOIN(delimiter, ignore_empty, range) | Join with delimiter |
| LEFT | =LEFT(text, num_chars) | First n characters |
| RIGHT | =RIGHT(text, num_chars) | Last n characters |
| MID | =MID(text, start, num_chars) | Extract from middle |
| LEN | =LEN(text) | Character count |
| TRIM | =TRIM(text) | Remove extra spaces |
| CLEAN | =CLEAN(text) | Remove non-printable |
| UPPER | =UPPER(text) | Uppercase |
| LOWER | =LOWER(text) | Lowercase |
| PROPER | =PROPER(text) | Title Case |
| FIND | =FIND(find_text, text) | Position (case-sensitive) |
| SEARCH | =SEARCH(find_text, text) | Position (not case-sensitive) |
| SUBSTITUTE | =SUBSTITUTE(text, old, new) | Replace text |
| REPLACE | =REPLACE(text, start, chars, new) | Replace by position |
| TEXT | =TEXT(value, format) | Format as text |
| VALUE | =VALUE(text) | Convert text to number |
Date Functions
| Function | Syntax | Description |
|---|
| TODAY | =TODAY() | Current date |
| NOW | =NOW() | Current date/time |
| DATE | =DATE(year, month, day) | Create date |
| YEAR | =YEAR(date) | Extract year |
| MONTH | =MONTH(date) | Extract month |
| DAY | =DAY(date) | Extract day |
| WEEKDAY | =WEEKDAY(date) | Day of week (1-7) |
| WEEKNUM | =WEEKNUM(date) | Week number |
| EOMONTH | =EOMONTH(date, months) | End of month |
| EDATE | =EDATE(date, months) | Date + months |
| DATEDIF | =DATEDIF(start, end, unit) | Difference ("Y","M","D") |
| NETWORKDAYS | =NETWORKDAYS(start, end) | Working days |
| WORKDAY | =WORKDAY(date, days) | Add working days |
Logical Functions
| Function | Syntax | Description |
|---|
| AND | =AND(condition1, condition2, ...) | All true? |
| OR | =OR(condition1, condition2, ...) | Any true? |
| NOT | =NOT(condition) | Reverse true/false |
| TRUE | =TRUE() | Returns TRUE |
| FALSE | =FALSE() | Returns FALSE |
| XOR | =XOR(condition1, condition2, ...) | Exclusive or |
Statistical Functions
| Function | Syntax | Description |
|---|
| MEDIAN | =MEDIAN(range) | Middle value |
| MODE | =MODE(range) | Most common |
| STDEV | =STDEV(range) | Standard deviation (sample) |
| STDEVP | =STDEVP(range) | Standard deviation (population) |
| VAR | =VAR(range) | Variance (sample) |
| PERCENTILE | =PERCENTILE(range, k) | Kth percentile |
| QUARTILE | =QUARTILE(range, quart) | Quartile value |
| LARGE | =LARGE(range, k) | Kth largest |
| SMALL | =SMALL(range, k) | Kth smallest |
| RANK | =RANK(number, range) | Rank in range |
Information Functions
| Function | Syntax | Description |
|---|
| ISBLANK | =ISBLANK(cell) | Is empty? |
| ISNUMBER | =ISNUMBER(value) | Is number? |
| ISTEXT | =ISTEXT(value) | Is text? |
| ISERROR | =ISERROR(value) | Is any error? |
| ISNA | =ISNA(value) | Is #N/A? |
| TYPE | =TYPE(value) | Value type code |
| CELL | =CELL("info_type", ref) | Cell information |
Common Formula Patterns
Sum If Multiple Criteria
=SUMIFS(sum_range, range1, criteria1, range2, criteria2)
Find Last Value in Column
=LOOKUP(2,1/(A:A<>""),A:A)
Extract Text Before Character
=LEFT(A1, FIND(" ", A1)-1)
Extract Text After Character
=RIGHT(A1, LEN(A1)-FIND(" ", A1))
Count Unique Values
=SUMPRODUCT(1/COUNTIF(A1:A100, A1:A100))
Conditional Rank
=SUMPRODUCT((B:B>B2)*(A:A=A2))+1
Dynamic Range
=OFFSET(A1, 0, 0, COUNTA(A:A), 1)
Two-Way Lookup
=INDEX(data, MATCH(row_lookup, rows, 0), MATCH(col_lookup, cols, 0))
Criteria Syntax
| Criteria | Meaning |
|---|
| "Sales" | Equals "Sales" |
| ">100" | Greater than 100 |
| "<100" | Less than 100 |
| ">=100" | Greater than or equal |
| "<>North" | Not equal to "North" |
| "A*" | Starts with A (wildcard) |
| "test" | Contains "test" |
| "???" | Exactly 3 characters |
Error Reference
| Error | Meaning | Common Fix |
|---|
| #DIV/0! | Division by zero | Check denominator |
| #VALUE! | Wrong value type | Check inputs |
| #REF! | Invalid reference | Fix broken reference |
| #NAME? | Unknown name | Check spelling |
| #N/A | Not available | Check lookup |
| #NUM! | Invalid number | Check calculation |
| #NULL! | Incorrect range | Check range syntax |