How to Lock a Cell Reference in Excel

To lock a cell reference in Excel, add dollar signs to its column, row, or both so the reference stays fixed when you copy a formula.

The choice between absolute, mixed, and relative references can save time—and prevent subtle calculation errors—in budgets, schedules, and other spreadsheets.

What does it mean to lock a cell reference?

Excel adjusts relative references when you copy or fill a formula to another cell.

For example, if cell C2 contains =A2*B2 and you copy it to C3, Excel changes the formula to =A3*B3.

That is useful when each row has its own values.

A locked reference stays anchored to the same row, column, or cell as the formula moves.

Excel marks a locked part with a dollar sign ($).

For example, $A$1 always refers to cell A1.

The dollar signs affect how the reference changes when copied; they do not change the value stored in the cell.

How do you lock a cell reference in Excel?

You can type dollar signs into a formula or use the F4 shortcut while editing a reference.

1. Enter a formula, such as =B2*C2.
2. In the formula bar or directly in the cell, place the cursor on the reference you want to lock.
   
   You can select the reference text, such as C2.
3. Press F4 to cycle through reference types, then press Enter to save the formula.

Depending on your keyboard, you may need to press Fn+F4 to send the F4 key.

On some laptops, function keys control brightness or volume by default.

You can also enter the dollar signs manually if the shortcut is unavailable.

For instance, if cell D2 calculates a price in B2 multiplied by a tax rate in F1, enter =B2*$F$1.

When you fill the formula down to D3, it becomes =B3*$F$1: the product reference changes by row, while the tax rate stays fixed.

What do the four reference types mean?

Starting with a reference such as A1, pressing F4 repeatedly cycles through these forms:

• A1 is relative: the row and column can both change when copied.
• $A$1 is absolute: both the column and row are fixed.
• A$1 is mixed: the row is fixed, but the column can change.
• $A1 is mixed: the column is fixed, but the row can change.

The dollar sign belongs immediately before the part it locks.

In $A1, the column letter is fixed; in A$1, the row number is fixed.

When should you use an absolute reference?

Use an absolute reference when many formulas need the same input cell.

Common examples include a tax rate, exchange rate, commission percentage, discount, or fixed multiplier.

Suppose A2:A4 contains quantities and B2:B4 contains unit prices.

Cell F1 contains a discount rate of 10%.

In C2, enter =A2*B2*(1-$F$1) and fill down through C4.

If A2 is 3 and B2 is $20, the expected result is $54.

The row references adjust on each row, but the discount remains tied to F1.

An absolute reference is also useful in a conversion table.

If a conversion factor is in B1, a formula such as =A2*$B$1 lets you copy calculations down a column without shifting the factor reference.

When are mixed references useful?

Mixed references help when formulas will be copied across columns, down rows, or both.

They are especially handy for two-variable tables, such as calculating combinations of quantities and prices.

Imagine row 1 contains prices in B1:D1 and column A contains quantities in A2:A4.

Enter =$A2*B$1 in B2, then fill it across to D2 and down through row 4.

The $A keeps the quantity column fixed as the formula moves across, while B$1 keeps the price row fixed as it moves down.

At D4, the copied formula becomes =$A4*D$1.

If A4 is 5 and D1 is $12, the expected result is $60.

This pattern lets each cell multiply the quantity for its row by the price for its column.

How can you check a formula after copying it?

After filling a formula, select a few cells in the destination range and inspect the formula bar.

Confirm that references meant to move have changed and locked references still point to the intended cells.

A single misplaced dollar sign can produce plausible-looking but incorrect results.

For a clearer audit, select a formula cell and use Formulas > Trace Precedents to show which cells feed into it.

You can also use Formulas > Show Formulas to display formulas throughout the worksheet instead of their calculated values.

These commands are available in current desktop versions of Excel; menu placement can differ slightly between Windows, Mac, and Excel for the web.

Common mistakes and edge cases

• Locking too much: If you change =A2*B2 to =$A$2*$B$2 before filling down, every row uses the same two cells.
  
  Lock only the part that must stay fixed.
• Locking the wrong part: Use A$1 to keep a header row fixed, and $A1 to keep a label column fixed.
  
  Check where the formula will be copied before choosing.
• Forgetting to update a reference: If an input moves to another cell, an absolute reference still points to its original address.
  
  Update the formula or use a named range when a descriptive label would make the input easier to identify.
• Confusing a reference lock with sheet protection: Dollar signs control how formulas adjust when copied.
  
  They do not prevent someone from editing a cell.
  
  To restrict editing, use worksheet protection and configure which cells are locked or unlocked.
• Copying instead of moving: A reference can behave differently when a formula is moved or when rows or columns are inserted.
  
  Review formulas after structural edits, especially in complex workbooks.

Does this work in Excel and Google Sheets?

Absolute and mixed references using dollar signs work in current desktop versions of Microsoft Excel for Windows and Mac, Excel for the web, and Google Sheets.

The F4 shortcut can vary by keyboard, operating system, and browser; typing the dollar signs directly is a reliable alternative when the shortcut does not behave as expected.

For a quick rule: use $A$1 to keep an entire cell fixed, A$1 to keep a row fixed, and $A1 to keep a column fixed.

Combine the reference types in one formula whenever only some inputs should move as you copy it.