How to Lock Cell References in Microsoft Excel Using the F4 Key

When you create a mathematical formula in Microsoft Excel and drag it down a column to copy it, Excel automatically adjusts the cell references. For example, if your formula is =A1+B1, dragging it down will automatically change it to =A2+B2. This is called a “relative reference,” and it is incredibly useful for most tasks. However, if your formula multiplies every cell by a specific tax rate located in a single, specific cell (like Z1), you do not want that reference to change as you drag the formula down. You must lock it in place by converting it into an “absolute reference.”

How to Manually Create an Absolute Reference

To lock a cell reference, you must use the dollar sign symbol ($) directly inside your formula. The dollar sign acts as an anchor.

If you want to multiply the value in cell A1 by the static tax rate in cell Z1, your formula should look like this:

=A1*$Z$1

There are two dollar signs here because you are locking two different dimensions:

• The first dollar sign ($Z) locks the column. If you drag the formula sideways, it will not change to column AA.
• The second dollar sign ($1) locks the row. If you drag the formula downwards, it will not change to row 2.

Now, when you drag this formula down a massive column of 500 rows, every single row will accurately multiply by the exact value stored in cell Z1.

The F4 Shortcut Key

Typing dollar signs manually while editing a long, complex formula is tedious and prone to error. Excel provides a dedicated keyboard shortcut to instantly toggle a cell reference between relative, absolute, and mixed states.

1. Double-click the cell containing your formula to enter editing mode.
2. Use your mouse or arrow keys to place the blinking text cursor directly on the cell reference you want to lock (e.g., click anywhere inside the text Z1).
3. Press the F4 key on your keyboard once. (On some laptops, you may need to press Fn + F4).

Excel will instantly inject both dollar signs, changing Z1 to $Z$1.

Creating Mixed References

Sometimes you only want to lock the row, but allow the column to change (or vice versa). This is called a “mixed reference.” You can cycle through all the possible locking combinations by continuing to press the F4 key.

• Press F4 once: Locks both the column and the row ($Z$1).
• Press F4 twice: Locks only the row (Z$1). The column will change if you drag left or right.
• Press F4 three times: Locks only the column ($Z1). The row will change if you drag up or down.
• Press F4 four times: Removes all locks, returning the reference to a fully relative state (Z1).