# Type of Cell Reference in Excel?

## 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**.