Publishers of technology books, eBooks, and videos for creative people

Home > Articles

  • Print
  • + Share This
This chapter is from the book

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:

You can move formulas by dragging. You can copy formulas by cut-and-paste, copy-and-paste, or filling circle-d.jpg. 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 circle-gray-c.jpg). 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:

  1. In the formula bar, tap the triangle in the placeholder of the cell reference that you want to preserve.
  2. Slide Preserve Row or Preserve Column to ON (absolute) or OFF (relative) for the beginning or end addresses of the selected range circle-e.jpg. 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 circle-gray-c.jpg).

    Absolute column–absolute row ($A$1). When the formula cell is copied, the cell reference doesn’t change circle-f.jpg.

    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.

  • + Share This
  • 🔖 Save To Your Account