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.