How to Lock Cells in Excel Formula

If you have ever copied an Excel formula down a column and watched it produce completely wrong results or worse, return errors — you have experienced what happens when cell references shift in ways you did not intend.

Locking cells in Excel formulas is one of the most essential skills for anyone who works with spreadsheets regularly. It is the difference between formulas that work correctly when copied and formulas that break the moment you move them.

In this guide, we will break down exactly how to lock cells in Excel formulas — what it means, why it matters, how to do it with the F4 key, and when to use absolute, relative, and mixed references.

Why Cell References Matter When Copying Formulas

Before understanding how to lock cells, you need to understand what happens to cell references when you copy a formula.

By default, Excel uses relative references meaning when you copy a formula from one cell to another, Excel automatically adjusts the cell references based on how far you moved.

The Problem Relative References Cause

Imagine you have this setup:

A1: 100    B1: Tax Rate
A2: 200    B2: 0.15
A3: 300
A4: 400

You want to calculate tax for each value in column A using the tax rate in B2.

In C1 you type: =A1*B2

You copy this formula down to C2, C3, C4.

What you expected:

C1: =A1*B2  → 100 * 0.15 = 15
C2: =A2*B2  → 200 * 0.15 = 30
C3: =A3*B2  → 300 * 0.15 = 45
C4: =A4*B2  → 400 * 0.15 = 60

What actually happens:

C1: =A1*B2  → 100 * 0.15 = 15  ✅
C2: =A2*B3  → 200 * (empty) = 0  ❌
C3: =A3*B4  → 300 * (empty) = 0  ❌
C4: =A4*B5  → 400 * (empty) = 0  ❌

As you copy down, both A and B references shift down by one row each time. The A column shifting is correct — you do want A2, A3, A4. But B2 shifting to B3, B4, B5 is wrong — you always want B2.

Locking B2 fixes this entirely.

The Three Types of Cell References

Excel has three types of cell references — each behaves differently when copied.

1. Relative Reference — A1

No dollar signs. Both the row and column adjust when the formula is copied.

=A1*B2

When copied one row down → becomes =A2*B3 When copied one column right → becomes =B1*C2

Use when: You want the reference to adjust as you copy like referencing the cell directly to the left or above.

2. Absolute Reference — $A$1

Dollar signs before both the column letter and row number. Neither the row nor the column adjusts when copied — it always points to the exact same cell.

=A1*$B$2

When copied one row down → becomes =A2*$B$2 (B2 stays locked) When copied one column right → becomes =B1*$B$2 (B2 stays locked)

Use when: You always want the reference to point to the same cell like a tax rate, exchange rate, or company-wide constant stored in one cell.

3. Mixed Reference — $A1 or A$1

Dollar sign before only the column OR only the row. One dimension is locked while the other adjusts.

$A1 — Column locked, row adjusts:

• When copied right → stays in column A
• When copied down → row number increases

A$1 — Row locked, column adjusts:

• When copied right → column letter increases
• When copied down → stays in row 1

Use when: You need to lock only one dimension — most useful in multiplication tables and two-dimensional lookup calculations.

How to Lock Cells

The dollar sign ($) is what locks a cell reference. You can type it manually or use the F4 shortcut.

Typing Dollar Signs Manually

=A1*$B$2    ← B2 is fully locked (absolute)
=A1*$B2     ← B column locked, row adjusts (mixed)
=A1*B$2     ← Row 2 locked, column adjusts (mixed)
=A1*B2      ← Nothing locked (relative)

The F4 Shortcut — The Fastest Way

Click on any cell reference inside a formula and press F4 to cycle through all four reference types:

Press F4 once:   B2 → $B$2  (absolute — both locked)
Press F4 twice:  B2 → B$2   (row locked only)
Press F4 three:  B2 → $B2   (column locked only)
Press F4 four:   B2 → B2    (back to relative)

How to use F4:

1. Click the cell containing your formula (or start typing a new one)
2. Click directly on the cell reference you want to lock inside the formula bar
3. Press F4 to cycle to the reference type you need
4. Press Enter to confirm

This is by far the fastest way to lock references — memorize it and you will use it constantly.

Absolute Reference — Practical Examples

Example 1: Tax Rate Calculation

A1: Product     B1: Price    C1: Tax
A2: Laptop      B2: 1200     C2: =B2*$E$1
A3: Phone       B3: 800      C3: =B3*$E$1
A4: Tablet      B4: 600      C4: =B4*$E$1

E1: 0.15  ← Tax rate stored here

$E$1 locks the tax rate cell. No matter how far down you copy column C, it always references E1 for the tax rate.

Example 2: Currency Conversion

A1: Amount USD    B1: Amount GBP
A2: 1000          B2: =A2*$D$1
A3: 2500          B3: =A3*$D$1
A4: 5000          B4: =A4*$D$1

D1: 0.79  ← Exchange rate

Change D1 to update the exchange rate for all rows instantly.

Example 3: Discount Application

B2:B10  = Product prices
E1      = Discount rate (0.20 for 20%)

Discounted price formula in C2:
=B2*(1-$E$1)

Copy down to C3:C10 — E1 stays locked throughout

Mixed Reference — Practical Examples

Mixed references are the most powerful and the trickiest to master. They shine in two-dimensional tables.

Example 1: Multiplication Table

Build a 5×5 multiplication table where row 1 contains multipliers and column A contains multiplicands.

     B      C      D      E      F
1    1      2      3      4      5
2    =A2*B$1
3
4
5
6

Formula in B2: =A2*B$1

• A2 — relative (adjusts as you copy right and down)
• B$1 — row locked at row 1, column adjusts as you copy right

But wait — A2 should stay in column A when copied right. Fix:

Formula in B2: =$A2*B$1

• $A2 — column A locked, row adjusts as you copy down
• B$1 — row 1 locked, column adjusts as you copy right

Now copy this single formula across the entire 5×5 range and it fills correctly.

     B        C        D        E        F
1    1        2        3        4        5
2    =$A2*B$1 =$A2*C$1 =$A2*D$1 =$A2*E$1 =$A2*F$1
3    =$A3*B$1 =$A3*C$1 ...
4    =$A4*B$1 ...

Example 2: Pricing Matrix

A pricing matrix where rows are quantities and columns are discount tiers.

       B(5%)    C(10%)   D(15%)
1      0.05     0.10     0.15
2 100  =A2*(1-B$1)
3 200
4 500

Formula in B2: =$A2*(1-B$1)

• $A2 — column A (base price) stays locked as you copy right, row adjusts as you copy down
• B$1 — row 1 (discount rate) stays locked as you copy down, column adjusts as you copy right

One formula, copy across the entire matrix.

Locking Entire Rows or Columns

Sometimes you want to lock an entire row or column rather than a specific cell.

Lock an Entire Row

=$A1    ← Column A locked, use for values in the first column
=A$1    ← Row 1 locked, use for headers or rates in the first row

Lock an Entire Column Range

=$A:$A        ← Entire column A, fully locked
=$A$1:$A$100  ← Column A, rows 1-100, fully locked

This is commonly used in VLOOKUP and SUMIF formulas where you want the lookup range to stay fixed.

Locking Ranges in Common Excel Functions

Locking cells becomes especially important inside functions that reference ranges.

VLOOKUP — Lock the Table Array

=VLOOKUP(A2, $E$2:$G$100, 2, FALSE)

$E$2:$G$100 is locked so the lookup table does not shift when you copy the formula down.

If you used E2:G100 without locking, copying down one row gives E3:G101 — your lookup table shifts and returns wrong results.

SUMIF — Lock the Range Arguments

=SUMIF($B$2:$B$100, A2, $C$2:$C$100)

The range being searched ($B$2:$B$100) and the sum range ($C$2:$C$100) are locked. The criteria cell (A2) is relative — it adjusts as you copy down to check different criteria.

COUNTIF — Lock the Range

=COUNTIF($D$2:$D$100, A2)

The count range stays fixed. The criteria cell adjusts as you copy the formula down through different criteria.

INDEX MATCH — Lock the Lookup Ranges

=INDEX($C$2:$C$100, MATCH(A2, $B$2:$B$100, 0))

Both the return range and lookup range are locked. Only the lookup value (A2) adjusts.

Protecting Locked Cells From Editing

Locking cells with dollar signs only fixes formula references, it does not prevent users from editing or overwriting the cells.

To prevent users from changing specific cells:

Step 1: Select cells you want to protect

Select the cells containing important formulas or values — tax rates, exchange rates, constants.

Step 2: Format cells as Locked

Right-click → Format Cells → Protection tab → Check Locked

By default, all cells are marked as locked — but this has no effect until you protect the sheet.

Step 3: Protect the Sheet

Review tab → Protect Sheet → Set a password (optional) → Click OK

Now locked cells cannot be edited. Unlocked cells (those you unchecked in step 2) remain editable — useful for input cells that users should be able to change.

Step 4: Allow certain editing in protected cells

When protecting the sheet, the dialog lets you choose what users CAN still do — select locked cells, select unlocked cells, sort, filter, and so on.

Reference Types

Reference Type	Syntax	Column Adjusts	Row Adjusts	Best For
Relative	A1	Yes	Yes	Most formulas copied in one direction
Absolute	$A$1	No	No	Fixed constants — tax rate, exchange rate
Column locked	$A1	No	Yes	Two-dimensional tables, copy right
Row locked	A$1	Yes	No	Two-dimensional tables, copy down

Real-World Use Cases

Financial Models

Budget models reference a single assumptions table — interest rate, inflation rate, headcount growth — stored in one section of the workbook. Every formula across the model references those cells with absolute references. Change the assumption once and the entire model updates.

Sales Commission Calculators

A commission table has salesperson names in column A and monthly sales in columns B through M. Commission rate lives in a single cell. Every formula uses $rate_cell locked absolutely so changing the rate updates all twelve months instantly.

Grade Calculators

A teacher has assignment scores in columns and student names in rows. Weights for each assignment live in row 1. Mixed references allow one formula to multiply each score by its weight when copied across the entire grid.

Pricing Tools

A pricing matrix with product costs in column A and markup percentages in row 1. Mixed references build the entire price table from a single formula copied across the grid.

Common Mistakes to Avoid

• Forgetting to lock before copying — The most common mistake. Always add dollar signs before copying a formula that references a fixed cell. Fixing references after copying across hundreds of rows is painful
• Locking the wrong dimension — Make sure you lock column, row, or both depending on which direction you are copying. Locking the row when you should lock the column produces incorrect results silently
• Pressing F4 without clicking the reference first — F4 only locks the reference your cursor is currently touching in the formula bar. Click directly on the cell reference letters before pressing F4
• Using absolute references when relative is needed — Not every reference needs locking. If a reference should adjust as you copy — like the row number for each product — keep it relative. Over-locking breaks formula behavior just as much as under-locking
• Confusing sheet protection with formula reference locking — Dollar signs lock formula references. Sheet protection prevents users from editing cells. They are completely separate features that serve different purposes
• Not testing after copying — Always click through several cells after copying a formula to verify the references adjusted (or stayed fixed) as intended

Quick Reference Cheat Sheet

Task	Action
Lock entire cell	Add $$ before column and row: $A$1
Lock column only	Add $ before column letter: $A1
Lock row only	Add $ before row number: A$1
Cycle reference types	Click reference in formula bar → press F4
Lock a range	$A$1:$C$10
Lock in VLOOKUP	=VLOOKUP(A2, $E$2:$G$100, 2, FALSE)
Lock in SUMIF	=SUMIF(B$2: B100, A2, $C$2: C$100)
Protect cells from editing	Format Cells → Locked → Protect Sheet

Locking cells in Excel formulas is one of those skills that seems small but makes an enormous difference in how reliably your spreadsheets work.

Here is the simplest summary:

• Relative (A1) — Both row and column adjust when copied. Default behavior.
• Absolute ($A$1) — Nothing adjusts. Always points to the same cell. Use for fixed constants.
• Mixed ($A1 or A$1) — One dimension locked, one adjusts. Use for two-dimensional tables.
• F4 shortcut — Click the reference, press F4 to cycle through all four types instantly.

Start by mastering absolute references — they solve the most common copying problems immediately. Once those feel natural, learn mixed references for building powerful two-dimensional tables with a single formula.

The moment your formulas copy perfectly every time is the moment Excel stops being frustrating and starts being genuinely powerful.

FAQs