Write formulas
Write Excel-style formulas, refer to other sheets, and look up workspace records with AXLOOKUP.
Calculate values with Excel-style formulas, refer to other cells and sheets, and look up records from your workspace.
Before you start: Can edit on Sheets.
Steps
- Click a cell and type
=. A formula always starts with=. - Type a function name. A list of matching functions appears with a hint for the arguments. Press
TaborEnterto accept one. Use the arrow keys to choose. - While typing, click or drag cells to insert references such as
A1orA1:B4at the cursor. - Press
Enter. The result shows in the cell, and the formula stays in the formula bar.
Example: =SUM(B2:B20), =IF(C2>1000,"Large","Small") or =SUMIFS(E:E,H:H,"Overdue"). See every function in the Function reference.
References
| Write | Meaning |
|---|---|
A1 |
One cell. |
$A$1, $A1, A$1 |
A locked reference: the part after $ does not change when the formula is copied or filled. |
A1:B4 |
A range. |
A:A, B:D |
Whole columns. They grow with the data. |
Data!B2, 'My Sheet'!B2 |
A cell on another sheet. Quote the name if it has spaces. |
Copying and filling formulas
When you copy, paste, fill (Ctrl + D, Ctrl + R, the fill handle) or sort, references move with the formula: =B2*C2 filled down becomes =B3*C3. Lock a column or row with $ to keep it fixed — =B2*$F$1 always multiplies by F1. A reference pushed off the sheet shows #REF!.
Operators
+, -, *, /, ^ (power), & (join text), and comparisons =, <>, <, <=, >, >=. A leading - or + works on numbers.
Criteria (COUNTIF, SUMIF, AVERAGEIF and the …IFS functions)
Write the test as text: ">10", "<=5", "<>paid", or just "paid". Wildcards are not supported.
Look up a record: AXLOOKUP
AXLOOKUP(type, key_field, key, target_field) reads one field of one record in your workspace.
Example: =AXLOOKUP("orders","number",A2,"amount") finds the order whose number equals the value in A2 and returns its amount.
Important:
AXLOOKUPruns with the permissions of the person who last recalculated the sheet. The looked-up value is saved in the cell, so anyone who can open the workbook can see it.
Errors
Errors are shown as values and can be caught with IFERROR.
| Error | Meaning |
|---|---|
#DIV/0! |
Division by zero, or an average of nothing. |
#VALUE! |
Wrong kind of value or wrong number of arguments. |
#NAME? |
Unknown function. |
#REF! |
The reference is invalid, for example its row was deleted. |
#N/A |
A lookup found nothing. |
#CYCLE! |
Cells depend on each other in a loop. |
What happens next
- Every edit recalculates the cells that depend on it. A change and everything it affects are saved together.
- A formula result is stored in the cell. If it does not fit the column type (for example text in a Number column) the stored value is empty, but the formula still shows its result.
- Inserting or deleting rows or columns updates the references in your formulas.
- Text that looks like a number in a Text column works as a number in formulas.
Limits
A formula can be up to 2,000 characters.
Tips
- Use Function reference to find the right function.
Troubleshooting
#NAME? Check the spelling of the function.
My lookup shows #N/A
VLOOKUP and MATCH need an exact match. Check spaces and spelling.