Formulas
Entering Formulas
Section titled “Entering Formulas”Start a formula with =. For example:
=A1+B1— Add two cells=SUM(A1:A10)— Sum a range=AVERAGE(B1:B5)*2— Combine functions and operators
Available Functions
Section titled “Available Functions”Math Functions
Section titled “Math Functions”| Function | Syntax | Description |
|---|---|---|
| SUM | =SUM(range) | Adds all numbers in a range |
| AVERAGE | =AVERAGE(range) | Returns the arithmetic mean |
| MIN | =MIN(range) | Returns the smallest value |
| MAX | =MAX(range) | Returns the largest value |
| COUNT | =COUNT(range) | Counts numbers in a range |
| COUNTA | =COUNTA(range) | Counts non-empty cells |
| ABS | =ABS(value) | Returns the absolute value |
| ROUND | =ROUND(value, decimals) | Rounds to specified decimals |
| INT | =INT(value) | Truncates to integer |
| MOD | =MOD(number, divisor) | Returns remainder |
| POWER | =POWER(base, exp) | Returns base^exp |
| SQRT | =SQRT(value) | Square root |
| PRODUCT | =PRODUCT(range) | Multiplies all values |
| MEDIAN | =MEDIAN(range) | Returns median value |
Logical Functions
Section titled “Logical Functions”| Function | Syntax | Description |
|---|---|---|
| IF | =IF(cond, true, false) | Conditional result |
| AND | =AND(cond1, cond2, ...) | TRUE if all true |
| OR | =OR(cond1, cond2, ...) | TRUE if any true |
| NOT | =NOT(condition) | Reverses boolean |
| IFERROR | =IFERROR(val, err_val) | Error handling |
| ISBLANK | =ISBLANK(cell) | TRUE if empty |
| ISNUMBER | =ISNUMBER(value) | TRUE if number |
| ISTEXT | =ISTEXT(value) | TRUE if text |
Text Functions
Section titled “Text Functions”| Function | Syntax | Description |
|---|---|---|
| CONCATENATE | =CONCATENATE(a, b, ...) | Joins text |
| LEFT | =LEFT(text, n) | Left n characters |
| RIGHT | =RIGHT(text, n) | Right n characters |
| MID | =MID(text, start, n) | Middle characters |
| LEN | =LEN(text) | Length of text |
| UPPER | =UPPER(text) | Uppercase |
| LOWER | =LOWER(text) | Lowercase |
| TRIM | =TRIM(text) | Remove extra spaces |
| FIND | =FIND(find, in) | Find position |
| SUBSTITUTE | =SUBSTITUTE(text, old, new) | Replace text |
Conditional Functions
Section titled “Conditional Functions”| Function | Syntax | Description |
|---|---|---|
| SUMIF | =SUMIF(range, criteria, sum_range) | Conditional sum |
| COUNTIF | =COUNTIF(range, criteria) | Conditional count |
| COUNTBLANK | =COUNTBLANK(range) | Count empty cells |
Lookup & Reference Functions
Section titled “Lookup & Reference Functions”| Function | Syntax | Description |
|---|---|---|
| VLOOKUP | =VLOOKUP(value, range, col, [sorted]) | Vertical lookup |
| HLOOKUP | =HLOOKUP(value, range, row, [sorted]) | Horizontal lookup |
| INDEX | =INDEX(range, row, [col]) | Value at position |
| MATCH | =MATCH(value, range, [type]) | Find position |
| ROW | =ROW([cell]) | Row number |
| COLUMN | =COLUMN([cell]) | Column number |
| ROWS | =ROWS(range) | Count rows |
| COLUMNS | =COLUMNS(range) | Count columns |
Date & Time Functions
Section titled “Date & Time Functions”| Function | Syntax | Description |
|---|---|---|
| TODAY | =TODAY() | Current date |
| NOW | =NOW() | Current date and time |
| DATE | =DATE(year, month, day) | Create date |
| YEAR | =YEAR(date) | Extract year |
| MONTH | =MONTH(date) | Extract month (1-12) |
| DAY | =DAY(date) | Extract day |
| WEEKDAY | =WEEKDAY(date, [type]) | Day of week (1-7) |
| DATEDIF | =DATEDIF(start, end, unit) | Date difference |
| EDATE | =EDATE(date, months) | Add months |
| EOMONTH | =EOMONTH(date, months) | End of month |
| HOUR | =HOUR(time) | Extract hour |
| MINUTE | =MINUTE(time) | Extract minute |
| SECOND | =SECOND(time) | Extract second |
Operators
Section titled “Operators”Arithmetic: + - * / (parentheses for grouping)
Comparison: < > = <= >= <> (returns TRUE/FALSE)
Text: & (concatenation, e.g., =A1&" "&B1)
Cell References
Section titled “Cell References”- Single cell:
A1,B2,AA100(supports multi-letter columns) - Ranges:
A1:B5,AA1:AZ50 - Relative:
A1— changes when copied - Absolute:
$A$1— stays fixed when copied - Mixed:
$A1orA$1— partially fixed
Press F4 while editing a reference to cycle through reference types:
A1 → $A$1 → A$1 → $A1 → A1
String Literals
Section titled “String Literals”Use double quotes for text in formulas:
="Hello World"=IF(A1>0, "Positive", "Negative")=A1&" "&B1(concatenates with space)
AutoSum
Section titled “AutoSum”Select a cell below or to the right of numbers and press Alt+= to automatically insert a SUM formula.
View All Formulas
Section titled “View All Formulas”Press Ctrl+` (backtick) to toggle between showing formula results and showing the actual formulas in all cells.