When copying formulas in Microsoft Excel or Apple Numbers, the applications adjust formulas by the number of columns or rows offset when pasted. For example, a formula B2-A2 in cell C2 would appear as E2-D2 when pasted in cell F2. If you meant your pasted formula to continue referencing A2, you would have to edit the formula. As an alternative, you could have created the formula in C2 in the following format: B2-$A$2. When you paste the formula in cell F2, the first value would update to its relative position, but the formula would still absolutely reference cell A2. Absolute cell references in Apple Numbers work much in the same way.
Absolute cell references in Apple Numbers
If you want to use absolute cell references in Apple Numbers, you have two options.
Suppose you had a value in cell C4 and another value in cell D4, and you created a formula in cell E4, D4/C4. If you copy and paste the formula in cell E5, the formula will automatically update to D5/C5. What happens, however, when you want to copy the formula and paste it into G4? Apple Numbers automatically updates the formula to F4/E4. If you want the formula to use C4 as a reference, you can create an absolute reference to that cell. In such a case, your change your formula in E 4 to be: D4/$C$4. When you copy this formula to any other cell, the second value will be C4.
As an alternative, you can enter the following formula into cell E4: D4/C4. When you do this, you will see a small arrow to the right of each cell reference. When you do this, you can choose either “Preserve Row” or “Preserve Column.”
Choose “Preserve Column” to make that part of the formula absolute. In other words, preserving the column for the formula D4/C4 will result in D4/$C4. Copying this formula and pasting it into cell E5 will change the formula to D5/$C5.
Pasting the formula into cell G4 will result in F4/$C4.
Should you choose “Preserve Row” and “Preserve Column,” the formula will place an absolute reference to C4 in the formula. It will look like this: D5/$C$4, for example, if you paste it into cell E5.
Using absolute cell references in Apple Numbers works in ways that are similar to the way Microsoft Excel works:
$C$4 – Preserve column AND preserve row. Paste this formula anywhere to reference cell C4.
$C4 – Preserve column. Everywhere you paste this formula, it will reference column C.
C$4 – Preserve row. Everywhere you paste this formula, it will reference row 4.
C4 – Relative. Everywhere you paste this formula, it will update cell references to its relative position.