Formulas and functions
Write formulas, use cell addresses and ranges, copy formulas, and find the right function among the 180 Spreadsheet knows.
Writing a formula
A formula starts with =. It can use numbers, text in quotes, cell addresses, ranges and functions:
=B2*C2- B2 times C2=SUM(B2:B20)- the total of B2 to B20=IF(D2>1000, "Large", "Small")=Sheet2!A1or='Sales 2026'!B4- a cell on another sheet
While you type, the cells you point at are outlined in colour, and a list of matching functions appears - press Tab or Enter to pick one. Inside a function, a line shows what it needs.
Pointing: after =, +, ( or ,, click a cell (or drag across a range) to put its address in the formula. In a formula you started by typing, the arrow keys point too.
Copying formulas
Copy a formula to other cells (Ctrl+C / Ctrl+V, or drag the small square at the bottom right of the selection) and its addresses move with it: =B2*C2 copied one row down becomes =B3*C3.
Put $ in front of a column or row to keep it fixed: =B2*$F$1 always uses F1.
Dragging the small square also continues series: 1, 2 becomes 3, 4, 5..., and Item 1 becomes Item 2, Item 3....
The functions
Spreadsheet has about 180 functions. Insert > Function or Help > Functions list shows them all with a line on each, and you can search them.
| Kind | Examples |
|---|---|
| Maths | SUM, SUMIF, SUMIFS, SUMPRODUCT, ROUND, ROUNDUP, MROUND, MOD, ABS, POWER |
| Counting and statistics | COUNT, COUNTA, COUNTIF, COUNTIFS, AVERAGE, AVERAGEIFS, MEDIAN, MIN, MAX, MINIFS, MAXIFS, LARGE, RANK, STDEV |
| Logic | IF, IFS, IFERROR, IFNA, AND, OR, NOT, SWITCH, CHOOSE |
| Lookup | VLOOKUP, HLOOKUP, XLOOKUP, INDEX, MATCH, XMATCH, LOOKUP, OFFSET, INDIRECT, FILTER, SORT, UNIQUE |
| Text | CONCATENATE, TEXTJOIN, LEFT, RIGHT, MID, LEN, TRIM, UPPER, PROPER, SUBSTITUTE, FIND, SEARCH, TEXT, VALUE |
| Dates | TODAY, NOW, DATE, YEAR, MONTH, DAY, EDATE, EOMONTH, DATEDIF, NETWORKDAYS, WORKDAY, WEEKNUM, WEEKDAY |
| Money | PMT, FV, PV, NPV, IRR, RATE, NPER, IPMT, PPMT |
| Your business | ELLIBO.PIVOT, ELLIBO.LIST, ELLIBO.BALANCE, ELLIBO.DEBIT, ELLIBO.CREDIT, ELLIBO.RATE, ELLIBO.FILTER |
The ELLIBO. functions read your other Ellibo apps - see Live data from your apps and Ledger formulas.
When something is wrong
A cell shows an error instead of a value. Point at it to read why.
- #DIV/0! - dividing by zero or an empty cell
- #VALUE! - text where a number is needed
- #REF! - the cell or sheet it pointed at was deleted
- #NAME? - a function name is mistyped
- #N/A - a lookup didn't find what it looked for
- #CYCLE! - the formula uses its own cell, directly or through others
- #ACCESS - you don't have access to that data in its app
- Loading... - app data is on its way
Wrap a formula in IFERROR to show something else instead: =IFERROR(B2/C2, 0).
Named ranges
Insert > Named range gives a range a name, like TaxRate for Settings!B2. Use the name in any formula: =A2*TaxRate.