Absolute Reference in Excel

To create an absolute reference in Excel, add $ symbols to a cell or range reference. This locks the reference. When you copy a formula, an absolute reference never changes.

Cell

Let's start by locking a reference to a cell.

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

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

3. Lock the reference to cell E2 by placing a $ symbol in front of the column letter and row number like this: $E$2. This creates an absolute reference.

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

Check:

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

Range

Sometimes you need to lock a reference to a range. For example, when using the RANK function to rank numbers in a data set.

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

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

3. Lock the reference to the range B2:B7 by placing $ symbols in front of the column letters and row numbers.

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

Check:

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

Named Range

Instead of using absolute range references, create a named range. This way you can make your formulas easier to understand.

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

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

Check:

Note: visit our page about named ranges to learn more about this topic.

F4

Use the F4 key to quickly toggle between all 4 types of cell references.

1. For example, select cell C2 below, click in the formula bar and move the insertion point in or to the right of E2.

2. Press F4 to create an absolute reference.

3. Press F4 again to create a mixed reference where the column is relative and the row is absolute.

4. Press F4 again to create a mixed reference where the column is absolute and the row is relative.

Note: visit our page about cell references to learn more about mixed reference in Excel.