Creating Absolute References and Locking Cells in Excel

Creating Excel formulas can become a tedious task if the user doesn’t understand how cell references work. Each cell reference in a formula has two parts – the letter represents the column and the number represents the row. For example, the formula =B3 will show the value or text in cell B3 (column B and row 3).

When formulas are copied and pasted, Excel will by default treat cell references are “relative” – in other words, the cell references in the pasted formula will change according to the number of columns and rows the pasted cell has moved away from the copied cell:

Step 1

In the example below, cell B5 is set as =B3.

Step 2

If the formula in B5 is copied and pasted into cell C5, the resulting formula will be =C3

If, however, we want to copy cell B5 to C5, but still have the pasted formula refer to B3, we need to create an “absolute” reference in the original formula in cell B5. Absolute cell references will not change when pasted. They are represented by a $ symbol. We can modify the formula in cell B5 to =$B$3. However, instead of manually typing the $, use the F4 key. With the cursor placed on the cell reference (i.e. in edit mode) press the F4 key – this will add an absolute reference to the column and row.

Step 3

In the initial formula, press the F4 key while the cursor is on the cell reference

Step 4

If cell B5 is copied and pasted elsewhere, the resulting formula will still refer to cell B3

In some cases, it is optimal to use partial absolute references to create formulas that are easier to copy and paste. A partial absolute reference is one where only the column or row is absolute, not both. To create partial absolute reference, again with the cursor on the cell reference, keep pressing F4. You will see that the reference will toggle between all the possibilities.