How to Use Absolute Cell References to Lock Formulas in Excel

One of Microsoft Excel’s most powerful features is its ability to automatically update formulas when you drag or copy them down a column. This is known as “relative referencing.” If you type =A1*B1 and drag it down to the next row, Excel automatically changes the formula to =A2*B2. However, there are times when you do not want Excel to change a cell reference. For example, if you are calculating sales tax for fifty different items, and the tax rate is stored in a single cell at the top of your sheet (e.g., cell D1), you want every single item to multiply by D1, not D2 or D3. To force Excel to lock onto a specific cell, you must use an Absolute Cell Reference.

How to Create an Absolute Cell Reference

To lock a cell reference in Excel, you must insert a dollar sign ($) directly in front of the column letter, the row number, or both.

1. Select the cell where you want to write your formula (e.g., C2).
2. Type the equals sign (=) to begin the formula.
3. Click the cell containing your first variable (e.g., A2, your item price).
4. Type your mathematical operator (e.g., * for multiplication).
5. Click the cell containing your constant variable (e.g., D1, your tax rate). Your formula should now look like =A2*D1.
6. Before pressing Enter, click your mouse cursor directly inside the D1 text in the formula bar.
7. Press the F4 key on your Windows keyboard (or Cmd + T on a Mac).

Excel will automatically add dollar signs to the reference, changing it to =A2*$D$1. You can now press Enter to complete the formula.

Using the Fill Handle with Absolute References

Now that your formula is locked, you can safely copy it to other rows without breaking the math.

1. Click on cell C2, which contains your finished formula (=A2*$D$1).
2. Hover your mouse over the small green square in the bottom-right corner of the cell (the Fill Handle) until your cursor turns into a black plus sign.
3. Click and drag the handle down to the bottom of your column.

If you click on cell C3 and look at the formula bar, you will see the formula has updated to =A3*$D$1. Excel correctly updated the relative item price (A3), but strictly maintained the absolute reference to the tax rate in D1.

Understanding Partial (Mixed) References

While pressing F4 once locks both the column and the row ($D$1), pressing it multiple times cycles through “mixed” references. Mixed references are advanced tools used when dragging formulas across both rows and columns simultaneously.

• $D$1 (Absolute): Locks the exact cell. The formula will never look anywhere else, regardless of where you drag it.
• D$1 (Mixed Row Lock): Locks the row at 1, but allows the column to change (D, E, F) if you drag the formula sideways.
• $D1 (Mixed Column Lock): Locks the column at D, but allows the row to change (1, 2, 3) if you drag the formula downwards.