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.
- Blank rows are skipped. A total separated from its data by an empty spacer row still finds the numbers above it, the same as Excel. The range stops at the first non-numeric cell, so a header above the data is left out.
- Several totals at once. Select a row of cells under your data (for example
D11:F11) and pressAlt+=: each cell gets the SUM of its own column. Select a column of cells beside your data and each gets the SUM of its own row. Cells with nothing to total are left alone, and the whole operation undoes in one step.
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.