Lesson 129: Absolute Reference in Excel

Absolute reference in Excel access to a fixed location on the sheet. When you copy a formula, the absolute reference will never change.

Absolute Reference in Excel

Cell reference

1. Cell C2 below refers to cell B2 and cell E2. Both are relative references.

2. This is what happens when we copy the formula in cell C2 to cell C3.

3. Fixed the reference to cell E2 by adding a $ symbol in front of the column letter and the row number.

4. To quickly copy the formula in cell C2 to other cells, select cell C2, click the lower-right corner of cell C2 and drag it down to cell C7.

Result:

Explanation: the absolute reference ($E$2) stays the same, while the relative reference (B2) changes to B3, B4, B5, B6 and B7.

Area Reference

1. Cell C2 below refers to cell B2 and the range B2:B7. Both are relative references.

2. This is what happens when we copy the formula in cell C2 to cell C3.

3. Fix the reference to the range B2:B7 by adding $ symbols before the columns and row numbers.

4. To quickly copy the formula in cell C2 to other cells, select cell C2, click the lower-right corner of cell C2 and drag it down to cell C7.

Result:

Explanation: the absolute reference ($B$2:$B$7) stays the same, while the relative reference (B2) changes to B3, B4, B5, B6 and B7.

Area name reference

1. Select the range B2:B7, enter a name in Name and press Enter.

2. You can now use this named range in your formulas. Select cell C2, enter the RANK function shown below and copy this formula to other cells.

Result:

F4

Use the F4 key to quickly switch between all 4 cell reference types.

1. For example, select cell C2 below, click in the formula bar, and move the cursor to the letter E2.

2. Press F4 to create an absolute reference.

3. Press F4 again to create a composite reference where columns are relative and rows are absolute.

4. Press F4 again to create a composite reference where columns are absolute and rows are relative.

In addition, you can also view many other excel articles here.