- Formula Basics
- Summarizing Data Quickly
- Elements of Formulas
- Entering Formulas
- Determining the Order of Operations
- Understanding Cell References
- Using Cell References
- Understanding Comparison Operators
- Using Comparison Operators
- Understanding Functions
- Using Functions
- Copying and Moving Formulas
- Handling Errors
Copying and Moving Formulas
When you copy or move numbers, text, or other raw values, Numbers duplicates the value in the target cells. Copying and moving formulas, however, is complicated by cell references, which you may not want to duplicate. Numbers’ default behavior is what you want most of the time:
- When you move a formula cell, Numbers leaves its original cell references untouched; in its new location, the formula still points to the same cells that it used to and .
- When you copy a formula cell, Numbers updates the formula’s cell references so that they point to different cells relative to the formula’s new location .
You can move formulas by dragging. You can copy formulas by cut-and-paste, copy-and-paste, or filling . These techniques are covered in “Copying and Moving Cells” and “Filling Cells with Data Series” in Chapter 3. You might think that cut-and-paste moves a cell, but it actually copies it—it only looks moved because its contents disappear from its original location. Numbers considers all pasted cells to be copies of the original. Still, it’s common to refer to cutting-and-pasting as moving (particularly when dealing with raw values rather than formulas).
By default, cell addresses in formulas are relative cell references, meaning their row or column addresses can change when you copy formulas (refer to ). For situations where you want to preserve row or column positions, Numbers offers absolute cell references, which freeze cell addresses no matter where you copy formula cells.
To set relative and absolute cell references:
- In the formula bar, tap the triangle in the placeholder of the cell reference that you want to preserve.
- Slide Preserve Row or Preserve Column to ON (absolute) or OFF (relative) for the beginning or end addresses of the selected
range . A $ character in the cell reference indicates an absolute row or column:
Relative column–relative row (A1). When the formula cell is copied, the cell reference changes so that it retains the same position relative to the formula cell (refer to ).
Absolute column–absolute row ($A$1). When the formula cell is copied, the cell reference doesn’t change .
Relative column–absolute row (A$1). When the formula cell is copied, only the column component can change to retain its position relative to the formula cell.
Absolute column–relative row ($A1). When the formula cell is copied, only the row component can change to retain its position relative to the formula cell.