iWork '09: The Missing Manual

Copying and Moving Formulas

When you’re working with straight-up text or numbers, copying and moving data around your table doesn’t require much planning, and the results are rarely surprising. Whether you drag a cell, copy it, paste it, or autofill its value, the data lands in its new location looking just as it did in its original spot—Numbers’ copy machine always makes a perfect duplicate. With formulas, however, it’s not always clear what “perfect duplicate” means, especially when cell references are involved. When you plunk a formula into a new location, Numbers sometimes makes changes to those cell references in an effort to anticipate your needs. As you shuffle formulas around your spreadsheet, it’s important to understand how and when Numbers modifies your formula to blend in with its new surroundings—and how you can control those changes.

POWER USERS’ CLINIC: Dancing Sheet to Sheet

Fans of keyboard shortcuts have yet another alternative for selecting cells in other tables and sheets:

1. With the insertion point in the Formula Bar or Formula Editor, hold down the Option and Command (⌘) keys and tap Page Down (or Page Up) to flip through the tables in your spreadsheet, including those on other sheets. (On laptops without Page Down or Page Up keys, press ⌘-Fn-Option-down-arrow or ⌘-Fn-Option-up-arrow, respectively.)

2. When you land on the table you want, release the ⌘ key, but hang onto the Option key.

3. While holding Option, use the arrows to select the cell to reference.

4. Release the ...