How to copy a cell formula in Numbers

Question

Lets say I have a formula like (Q7+Q11)÷Q20 in cell O20 and I wanna copy it to another cell, lets say D47, is there any key modifier or shortcut to do it without losing the original references of the formula and without rewriting it?

Something like Cmd+Alt drag and drop?

I am with Numbers 4 on Mac OS Sierra

Answer 1

Using an AppleScript to Copy Formulas

A more reusable approach would be a simple AppleScript that replicates the Copy command but only copies the bare formulas:

on join(cells, delimiter)
  set previousDelimiters to AppleScript's text item delimiters
  set AppleScript's text item delimiters to delimiter
  set resultString to (cells as text)
  set AppleScript's text item delimiters to previousDelimiters
  return resultString
end join

on getSelectedFormulas()
  set tab to character id 9
  set lf to character id 10

  tell application "Numbers"
    tell document 1's sheet 1's table 1
      tell selection range
        set {min, max} to {column 1's address, column -1's address}
        set formulaRows to {}
        repeat with aRow in rows
          set formulaCells to {}
          repeat with aCell in aRow's cells min thru max
            set formulaCells to formulaCells & (aCell's formula)
          end repeat
          set formulaRows to formulaRows & ¬
            my join(formulaCells, tab)
        end repeat
        return my join(formulaRows, lf)
      end tell
    end tell
  end tell
end getSelectedFormulas

on run()
  tell application "Numbers"
    set the clipboard to my getSelectedFormulas()
  end tell
end run

(Note: I’m the author of the script; it’s under the ISC open source license.)

This also eliminates the need to modify the original formulas.

Binding the script to a keyboard shortcut

In order to bind this script to a keyboard shortcut in Numbers, open Automator.app to create an Automator workflow. Choose the option Service receives no input in Numbers, create a single Run AppleScript step and paste the AppleScript code there.

Save the workflow under the name Copy Formulas, then open System Preferences and click on Keyboard, then Keyboard Shortcuts » Services » Numbers » Copy Formulas. Configure a keyboard shortcut; I’d recommend ⌥⇧⌘C because you’re probably going to use it often with its counterpart, Paste and Match Style, which already has the shortcut ⌥⇧⌘V.

Answer 2

Using the built-in method

As per Apple’s Knowledge Base, Numbers supports absolute references:

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.

To change your original formula to use absolute references, follow these steps:

1. Double-click on the original cell to go into edit mode.
2. For each reference, click on the triangle; then tick both boxes Preserve Row and Preserve Column.

Or, as an alternative to step 2, you can manually edit the reference to say e. g. $A$1 instead of A1.

The obvious drawbacks of this method are:

• The method basically locks your formula in a mode that makes it hard to reuse the same formula in other places where you actually want flexible references.

• If you you want to copy many different original formulas, you need to repeat the method over and over.