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)`

Lookup & Reference

These functions search ranges and return matching values or positions — the spreadsheet equivalents of VLOOKUP, HLOOKUP, XLOOKUP, MATCH, INDEX, and OFFSET.

LOOKDOWN

Searches the first column of a range top-to-bottom and returns the value at a given column index in the matched row. Equivalent to VLOOKUP.

Syntax

LOOKDOWN(lookup_value, range, col_index, exact_match?)

Argument	Description
`lookup_value`	Value to search for. Can be a cell ref, string, or number.
`range`	Range like A1:D20. Only the first column is searched.
`col_index`	1-based column to return. 1 = first column, 2 = second, etc.
`exact_match`	true (default) = exact. false = approximate — first column must be sorted ascending.

Examples

=LOOKDOWN("Widget", A1:C50, 3)          # price for "Widget"
=LOOKDOWN(D2, A1:B10, 2, false)          # tax bracket (approximate)
=IFERROR(LOOKDOWN(A1, B1:D20, 3), "—")  # with fallback

LOOKACROSS

Searches the first row of a range left-to-right and returns the value at a given row index in the matched column. Equivalent to HLOOKUP.

Syntax

LOOKACROSS(lookup_value, range, row_index, exact_match?)

Argument	Description
`lookup_value`	Value to search for in the first row of the range.
`range`	Range like A1:Z3. Only the first row is searched.
`row_index`	1-based row to return. 1 = header row, 2 = second row, etc.
`exact_match`	true (default) = exact. false = approximate — header row must be sorted ascending.

Examples

=LOOKACROSS("Alice", A1:F3, 2)           # score for "Alice" from row 2
=LOOKACROSS("Q3", A1:D5, 3)              # quarter lookup
=IFERROR(LOOKACROSS(B1, A1:Z10, 4), "—")

FIND

Searches a range for a value and returns the corresponding value from a return range. Supports wildcards, match modes, and search direction. Equivalent to XLOOKUP.

Syntax

FIND(lookup_value, search_range, return_range, if_missing?, match_mode?, search_mode?)

Argument	Description
`lookup_value`	Value to search for. Supports `*` (any chars) and `?` (any single char) wildcards.
`search_range`	A single row or column range to search in (e.g. A1:A20).
`return_range`	A range of the same size to return the matched value from.
`if_missing`	Value to return if no match. Defaults to #N/A.
`match_mode`	0 exact (default), 1 next larger, -1 next smaller, 2 wildcard.
`search_mode`	1 first-to-last (default), -1 last-to-first, 2 binary ascending, -2 binary descending.

Examples

=FIND("Widget", A1:A20, B1:B20)              # price for "Widget"
=FIND(D1, A1:A50, C1:C50, "Not found")       # with fallback
=FIND("Pro*", A1:A20, B1:B20, "—", 2)        # wildcard match
=FIND(E1, A1:A100, B1:B100, "", 0, -1)       # last-to-first (latest match)

LOCATE

Searches a range for a value and returns its 1-based position (row or column index). Equivalent to MATCH.

Syntax

LOCATE(lookup_value, lookup_range, match_type?)

Argument	Description
`lookup_value`	The value to search for.
`lookup_range`	A single row or column range (e.g. A1:A20 or A1:Z1).
`match_type`	0 exact (default), 1 largest value ≤ lookup (sorted ascending), -1 smallest value ≥ lookup (sorted descending).

Examples

=LOCATE("Alice", A1:A20)              # position of "Alice" in column A
=LOCATE(D1, A1:A100, 0)               # row of the value in D1
=LOCATE(500, A1:A10, 1)               # approximate match (sorted range)
=CELLAT(B1:B20, LOCATE(D1, A1:A20))  # combined with CELLAT

CELLAT

Returns the value at a specific row and/or column within a range. Equivalent to INDEX.

Syntax

CELLAT(range, row_num, col_num?)

Argument	Description
`range`	The source range (e.g. A1:D20).
`row_num`	1-based row index within the range.
`col_num`	1-based column index within the range. Defaults to 1.

Examples

=CELLAT(B1:B20, 5)                     # 5th value in column B
=CELLAT(A1:D10, 3, 2)                  # row 3, column 2 of a table
=CELLAT(C1:C50, LOCATE(F1, A1:A50))   # full lookup with LOCATE

SHIFT

Returns the value of a cell offset from a base cell by a given number of rows and columns. Equivalent to OFFSET.

Syntax

SHIFT(base_cell, rows, cols, height?, width?)

Argument	Description
`base_cell`	Starting cell reference (e.g. A1).
`rows`	Rows to move down (negative = up).
`cols`	Columns to move right (negative = left).
`height`	Number of rows to return (default 1). Values > 1 spill.
`width`	Number of columns to return (default 1). Values > 1 spill.

Examples

=SHIFT(A1, 1, 1)           # one row below and one column right of A1
=SUM(SHIFT(B1, 2, 0, 3))   # dynamic sum — 3 rows starting 2 rows below B1
=SHIFT(A5, -1, 0)           # same column, previous row

Note: LOCATE + CELLAT is the recommended replacement for the classic INDEX/MATCH pattern.

Multi-criteria & Array Functions

SUMWHERE

Sums a range based on one or more criteria applied to corresponding ranges. Equivalent to SUMIFS.

Syntax

SUMWHERE(sum_range, criteria_range1, criteria1, criteria_range2?, criteria2?, ...)

Argument	Description
`sum_range`	The range of values to sum.
`criteria_range1`	Range to evaluate against criteria1.
`criteria1`	Condition string: exact value, `">50"`, `"<>Done"`, or blank `""`. Wildcards `*` and `?` supported.
Additional pairs	More range/criteria pairs — all conditions must match.

Examples

=SUMWHERE(B1:B20, A1:A20, "Done")                         # sum col B where col A is "Done"
=SUMWHERE(D1:D50, C1:C50, "Active", D1:D50, ">100")       # multiple conditions
=SUMWHERE(B1:B20, A1:A20, "Pro*")                          # wildcard

COUNTWHERE

Counts rows where all specified criteria match their respective ranges. Equivalent to COUNTIFS.

Syntax

COUNTWHERE(criteria_range1, criteria1, criteria_range2?, criteria2?, ...)

Argument	Description
`criteria_range1`	Range to evaluate against criteria1.
`criteria1`	Condition string — same format as SUMWHERE.
Additional pairs	More range/criteria pairs — all must match for a row to be counted.

Examples

=COUNTWHERE(A1:A20, "Done")                        # rows where col A is "Done"
=COUNTWHERE(A1:A50, "Active", B1:B50, ">80")       # status "Active" AND score > 80
=COUNTWHERE(A1:A20, "")                             # count blank cells

UNIQUE

Returns a deduplicated list of values from a range. Result spills as val1, val2 | val3, val4 | …

Syntax

UNIQUE(range, by_col?, exactly_once?)

Argument	Description
`range`	The source range to deduplicate.
`by_col`	false (default) — deduplicate rows. true — deduplicate columns.
`exactly_once`	false (default) — all unique values. true — only values that appear exactly once.

Examples

=UNIQUE(A1:A50)              # unique values from column A
=UNIQUE(A1:A50, false, true) # values that appear exactly once

SORT

Sorts a range and returns the sorted result as a spill.

Syntax

SORT(range, sort_index?, sort_order?, by_col?)

Argument	Description
`range`	The range to sort.
`sort_index`	1-based row or column to sort by (default 1).
`sort_order`	1 ascending (default), -1 descending.
`by_col`	false (default) — sort rows. true — sort columns.

Examples

=SORT(A1:A20)          # sort column A ascending
=SORT(A1:D20, 2, -1)   # sort table by column 2 descending

SORTBY

Sorts a range by one or more external sort ranges. The source and sort ranges must have the same row count.

Syntax

SORTBY(range, sort_range1, sort_order1, sort_range2?, sort_order2?, ...)

Argument	Description
`range`	The range to sort.
`sort_range1`	A range to sort by (same number of rows as range).
`sort_order1`	1 ascending (default), -1 descending.
Additional pairs	Secondary (and further) sort keys applied in order.

Examples

=SORTBY(A1:A20, B1:B20, 1)              # sort col A by values in col B
=SORTBY(A1:C50, B1:B50, -1, C1:C50, 1) # sort table by col B desc, then col C asc

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)
`#N/A`	LOOKDOWN / LOOKACROSS / FIND / LOCATE could not find the lookup value

Hover any error cell to see a descriptive tooltip. Wrap with IFERROR to handle errors gracefully:

=IFERROR(DIVIDE(A1, B1), 0)
=IFERROR(SQRT(A1), "Invalid")
=IFERROR(LOOKDOWN(A1, B1:D20, 2), "—")

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 on

Examples

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)

On this page

Overview
Column Types
Writing Formulas
Arithmetic Operators
Math Functions
Aggregate & Stats
Conditional Functions
Text Functions
Date Functions
Lookup & Reference
Multi-criteria & Array
Error Values
Auto-fill Templates
Examples