Spreadsheet
The Moderrno Spreadsheet is a flexible data table with typed columns, live formula evaluation, conditional logic, and auto-fill templates — all without leaving your workspace.
Column Types
Each column has a type that determines how data is entered and displayed. Click a column header to change its type.
| Type | Description |
|---|---|
Text | Free-form text. Default type for new columns. |
Number | Numeric values. Supports formatting (currency, percent, decimal). |
Formula | Computed value. Enter a formula starting with = and it evaluates live. |
Date | Date picker. Stores values as YYYY-MM-DD. |
Select | Single-choice dropdown. Define your own options. |
Tags | Multi-select labels. Color-coded for quick scanning. |
Checkbox | Boolean toggle — checked or unchecked. |
Writing Formulas
All formulas must begin with =. Cell references use A1 notation — a column letter followed by a row number. Ranges are written as A1:C5.
Basic formula syntax
=A1+B1 # add two cells =SUM(A1:A10) # sum a range =IF(B1="Done", 1, 0) # conditional =CONCAT(A1, " ", B1) # join text
Note: Column letter A = column 1, B = column 2, and so on. Row numbers start at 1.
Arithmetic Operators
Use these directly without a function name. They can be chained: =A1+B1*C1-D1
| Operator | Description | Example |
|---|---|---|
+ | Addition | =A1+B1 |
- | Subtraction | =A1-B1 |
* | Multiplication | =A1*B1 |
/ | Division | =A1/B1 |
Math Functions
| Formula | Arguments | Description | Example |
|---|---|---|---|
SUBTRACT(a, b) | two values | Explicit subtraction | =SUBTRACT(A1, B1) |
MULTIPLY(a, b) | two values | Explicit multiplication | =MULTIPLY(A1, 5) |
DIVIDE(a, b) | two values | Division — returns #DIV/0! if b is 0 | =DIVIDE(A1, B1) |
POWER(base, exp) | two values | base raised to the power of exp | =POWER(A1, 2) |
SQRT(n) | number | Square root — #NUM! if negative | =SQRT(A1) |
MOD(n, divisor) | two numbers | Remainder after division | =MOD(A1, 3) |
ABS(n) | number | Absolute value | =ABS(A1) |
ROUND(n, digits) | number, int | Round to N decimal places | =ROUND(A1, 2) |
FLOOR(n) | number | Round down to nearest integer | =FLOOR(A1) |
CEIL(n) | number | Round up to nearest integer | =CEIL(A1) |
LOG(n, base) | two numbers | Logarithm with custom base | =LOG(A1, 10) |
LOG10(n) | number | Base-10 logarithm | =LOG10(A1) |
EXP(n) | number | e raised to the power of n | =EXP(A1) |
PI() | none | Value of π (3.14159…) | =A1*PI() |
Aggregate & Statistical Functions
Accept a cell range A1:A10, individual cell refs, or comma-separated values.
| Formula | Description | Example |
|---|---|---|
SUM(range) | Sum of all numeric values | =SUM(A1:A10) |
PRODUCT(range) | Multiply all values together | =PRODUCT(A1:A5) |
AVG(range) | Arithmetic mean | =AVG(B1:B20) |
AVERAGE(range) | Alias for AVG | =AVERAGE(B1:B20) |
MEDIAN(range) | Middle value when sorted | =MEDIAN(A1:A10) |
STDEV(range) | Sample standard deviation | =STDEV(A1:A10) |
MIN(range) | Smallest value | =MIN(A1:A10) |
MAX(range) | Largest value | =MAX(A1:A10) |
COUNT(range) | Count of numeric cells | =COUNT(A1:A10) |
COUNTA(range) | Count of non-empty cells (any type) | =COUNTA(A1:A10) |
Conditional Functions
Tip: Use
= for equality and <> for not-equal inside IF conditions — e.g. A1="Done" or A1<>"Active".| Formula | Description | Example |
|---|---|---|
IF(cond, yes, no) | Returns yes if condition is true, no otherwise | =IF(A1>100, "High", "Low") |
IFERROR(value, fallback) | Returns fallback if value produces an error | =IFERROR(DIVIDE(A1,B1), 0) |
SUMIF(range, "criteria", sum) | Sum cells where range matches criteria | =SUMIF(A1:A10, "Done", B1:B10) |
COUNTIF(range, "criteria") | Count cells that match criteria | =COUNTIF(A1:A10, "Done") |
AND(a, b, …) | True only if all conditions are true | =AND(A1>0, B1>0) |
OR(a, b, …) | True if any condition is true | =OR(A1>100, B1>100) |
NOT(condition) | Negates the condition | =NOT(A1>0) |
Note: SUMIF and COUNTIF criteria must be a quoted string, e.g.
"Done" or "Active".Text Functions
| Formula | Arguments | Description | Example |
|---|---|---|---|
CONCAT(a, b, …) | values/refs | Join values into one string | =CONCAT(A1, " ", B1) |
LEN(text) | text/ref | Number of characters | =LEN(A1) |
UPPER(text) | text/ref | Convert to UPPERCASE | =UPPER(A1) |
LOWER(text) | text/ref | Convert to lowercase | =LOWER(A1) |
TRIM(text) | text/ref | Remove leading/trailing spaces | =TRIM(A1) |
LEFT(text, n) | text/ref, number | First N characters | =LEFT(A1, 3) |
RIGHT(text, n) | text/ref, number | Last N characters | =RIGHT(A1, 4) |
MID(text, start, len) | text/ref, num, num | Substring — start is 1-indexed | =MID(A1, 2, 5) |
TEXT(n, "fmt") | number, format | Format a number as text | =TEXT(A1, "0.00") |
Date Functions
Date cells should store values in YYYY-MM-DD format.
| Formula | Description | Example |
|---|---|---|
TODAY() | Today's date as YYYY-MM-DD | =TODAY() |
NOW() | Current date and time | =NOW() |
YEAR(date) | Extracts the year | =YEAR(A1) |
MONTH(date) | Extracts the month (1–12) | =MONTH(A1) |
DAY(date) | Extracts the day of the month | =DAY(A1) |
Error Values
| Error | Cause |
|---|---|
#ERR | Formula syntax error or unresolvable expression |
#DIV/0! | Division by zero in DIVIDE() or / operator |
#NUM! | Invalid numeric operation (e.g. SQRT of a negative) |
Wrap formulas with IFERROR to handle errors gracefully:
=IFERROR(DIVIDE(A1, B1), 0) =IFERROR(SQRT(A1), "Invalid")
Auto-fill Templates
When a column is set to type Formula, you can set a template that auto-fills every new row. Use {n} as a placeholder for the row number. Arithmetic expressions inside {} are fully supported.
| Template | Row 5 generates | Use case |
|---|---|---|
=SUM(A{n}:C{n}) | =SUM(A5:C5) | Row total |
=MULTIPLY(D{n}, E{n}) | =MULTIPLY(D5, E5) | Row product |
=IF(B{n}>100, "Hi", "Lo") | =IF(B5>100,"Hi","Lo") | Row flag |
=E{n-1} | =E4 | Previous row reference |
=A{n+1} | =A6 | Next row reference |
Tip: To set a template: click the column header → select Formula type → enter the template → click Save or Apply all.
Budget tracker example — carry forward the ending balance as the starting balance for each new row:
Column C — Starting Balance template
=E{n-1}
# Row 2 → =E1 (end balance from row 1)
# Row 3 → =E2 (end balance from row 2)
# Row 4 → =E3 ...and so onExamples
Budget tracker
Columns: A = Type (Income/Expense), B = Description, C = Starting Balance, D = Amount, E = End Balance
E column formula (End Balance)
=IF(A1="Income", C1+D1, C1-D1)
C column template (Starting Balance — auto-filled from previous row)
=E{n-1}Price with discount
=MULTIPLY(A1, SUBTRACT(1, B1)) # A1 = price, B1 = discount rate (e.g. 0.2 for 20%)
Gross margin
=ROUND(DIVIDE(SUBTRACT(A1, B1), A1), 4) # A1 = revenue, B1 = cost
Full name from two columns
=CONCAT(A1, " ", B1)
Flag overdue items
=IF(A1<TODAY(), "Overdue", "On time")
Revenue for completed rows only
=SUMIF(C1:C20, "Done", D1:D20)