Training Program No related posts.
When a formula is copied, the relative reference is used. Relative reference is the distance, in rows and columns, between the reference and the cell containing the formula. For example, in Cell A1, type the number 100. In Cell B1, type the formula =A1. Cell B1 is one column to the right of Cell A1. When the formula is copied from Cell B1 to Cell B10, the distance between the reference and the cell containing the formula remains one column. The formula in Cell B10 is =A10.
Use the previous example and select Cell B1. In the formula bar, select the reference A1, and press F4. The result is =$A$1.
Copy the contents of Cell B1 to Cell B10. Notice that the formula does not change; the formula reference remains constant as =$A$1.
The F4 key
This is an important shortcut. The F4 shortcut, which deals with relative and absolute references, has four states. Select Cell B1, and then select the formula bar after the = sign. Press F4 several times. Notice how the formula changes each time you press F4.
State 1 – absolute reference to the column and row, =$A$1.
State 2 – relative reference (column) and absolute reference (row), =A$1. State 3 – absolute reference (column) and relative reference (row), =$A1. State 4 – relative reference to the column and row, =A1.
Maintaining a relative reference when pasting a formula
In many cases, you copy a formula from cell to cell when you want to avoid changing the reference of the cell containing the formula. In this case, use F4 to change the formula to absolute, copy it and paste it; then use F4 to change the original formula back to relative.
Using F4 twice is annoying, but there is a way to avoid this repetitive task.
Copy and paste the formula from the formula bar back to the formula bar, instead of from cell to cell.
For example: Cell A1 contains the formula =A$1. Select the text from the formula bar (that is, select the formula) and press Ctrl+C (copy). Leave the formula bar by hitting Esc or by clicking Enter or Cancel (click the √ or the X to the left of fx on the formula bar). Select another cell in the sheet and press Ctrl+V.