Add and edit formulas
To add and edit formulas, you use the Formula Editor. The Formula Editor has a text field that holds your formula. You can add cell references, operators, functions, or constants to a formula.
Note: In Numbers, you can also use the formula bar to work with formulas.
Insert a formula
You can create simple or complex arithmetic formulas to perform calculations on the values in your tables. Formulas can be simple, such as an equation like 6x3-2; they can use basic functions like SUM, AVERAGE, and MAX; or they can use more complex functions like REGEX.EXTRACT or XLOOKUP. Formulas can also use a combination of values and functions.
Click or tap the cell where you want the result to appear, then type the equal sign (=) to open the Formula Editor.
Click or tap one or more cells to use in your formula, or enter a value (for example, a number such as 0 or 5.20).
You can also add functions. See Add a function to a formula.
Type an arithmetic operator (for example, +, -, *, or /), then select another cell to use in your formula, or type a value.
If you don’t specify an operator, Numbers inserts a + between cell references by default.
Continue adding operators and cell references until your formula is complete, then press Return or click
in the Formula Editor when you’re done.If you click
, you exit the Formula Editor without saving your changes.
Add a function to a formula
In the Formula Editor or formula bar (Numbers), place the insertion point where you want the function added.
Open the Functions Browser.
Select a function category.
To add a function, select it and tap or click Insert Function.
In the Formula Editor or formula bar (Numbers), replace each argument placeholder in the inserted function with a value.
Alternatively, you can type a function directly into the text field of the Formula Editor or formula bar (Numbers).
If you aren’t sure which function you want, you can learn more about each function either in the Functions Browser or on the web. See List of functions by category.
Compare values using a formula
You can use comparison operators to check whether the values in two cells are equal, or if one value is greater or less than the other. To do this, you must set up a statement within a cell—for example A1 > A2, meaning the value in cell A1 is greater than the value in cell A2. The result of the comparison operator is expressed as "true" or "false."
In the Formula Editor, select a cell to use as the first argument in your formula, or type a value.
Select a comparison operator (<, >, =, or ≠) in the top row of the keyboard.
If you don’t see the comparison operators, drag the top row to the left.
Select a cell to use as the next argument in your formula, or type a value.
Click or tap
in the Formula Editor to enter the formula in the cell.
Preserve row or column references in formulas
You can set row and column references in a formula to be absolute so that you can use the same formula elsewhere in your spreadsheet without the cell references changing, as they would otherwise.
If you don’t preserve the row or column references, if you move the formula (by cutting and pasting, or by adding new rows and columns), the references are adjusted relative to the formula’s new location.
Double-click or double-tap the cell with the formula whose cell addresses you want to maintain.
Click or tap the disclosure triangle on the token representing the cell range you want to preserve.
Select Preserve Row or Preserve Column for the beginning or ending addresses of the selected range.
If you change the number of rows or columns in the table, or if you move the formula to a different cell, the preserved row or column references are adjusted, but they maintain their absolute original cell references.
To save your changes, click or tap
.
Add concatenation to a formula
The concatenation operator (&) can be used in formulas to concatenate, or join, two or more strings or the contents of two or more cell references.
Place the insertion point in the Formula Editor or formula bar (Numbers) at the point where you want to insert the concatenation.
Add the first string or cell reference to be included in the concatenation.
Type &.
Add the second string or cell reference to be included in the concatenation.
Repeat steps 3 and 4 as many times as needed until all items to be concatenated are included.
Change an existing formula
You can edit an existing formula so that it refers to different cells.
Double-click or double-tap the cell with the formula you want to edit, then do any of the following:
Add more cell references: Click or tap within the argument area for the formula, then select the new cells you want to add.
Remove cell references: In the Formula Editor, select the unwanted cell references, then press Delete on your keyboard.
Change the cell references: Select the existing cell addresses you want to change, then select the new cells.
When you’re done making changes, choose
in the Formula Editor.
Remove a formula from a cell
If you no longer want to use a formula that’s associated with a cell, you can delete it.
Select the cell that contains the formula, then tap or press the Delete key.
For specific instructions on how to accomplish tasks in Numbers, Pages, or Keynote, see the user guide for that app.