Uncategorized , , , , , ,

Type of Cell Reference in Excel?

Post By admin 26-October-2017

Type of Cell Reference in Excel?

Relative reference

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.

Absolute reference

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.