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.

Relative Cell Reference

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

Copy Relative Cell Reference

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.

Absolute Cell 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.

Copy Absolute Cell Reference

Check:

Locked Cell Reference

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.

Relative Range Reference

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

Copy Relative Range Reference

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

Absolute Range 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.

Copy Absolute Range Reference

Check:

Locked Range Reference

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.

Create Named Range

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.

Copy Formula

Check:

Named Range

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.

Relative Reference

2. Press F4 to create an absolute reference.

Absolute Reference

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

Absolute Row Reference

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

Absolute Column Reference

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