How to Lock Cell Reference in Excel — $B$1 Step-by-Step Guide

If you fill a formula down a column and your divisor keeps “sliding” to the next row, you’re seeing relative references at work. The fix is simple: turn the divisor (for example, B1) into an absolute reference ($B$1) so it never moves when you copy or fill.

Time: 2–5 minutes (Beginner) • What you’ll learn: the F4 toggle, absolute vs mixed references, quick fill methods, and fast checks to avoid #DIV/0!.

Key takeaways

• To lock a divisor, convert B1 to $B$1; use =A2/$B$1 and then fill.

• Press F4 (Windows) or Fn+F4 / Command+T (Mac) with the cursor on a reference to cycle A1 → $A$1 → A$1 → $A1 → A1, per Microsoft’s guidance in the article on switching between relative, absolute, and mixed references.

• Efficient fills: drag the fill handle or select the range and press Ctrl+D (Windows) / Command+D (Mac). See Microsoft’s keyboard shortcuts in Excel.

• Quick fixes: ensure the divisor isn’t blank/zero (avoid #DIV/0!); if F4 “does nothing,” place the caret on the cell reference and check Mac Fn‑key settings.

Absolute, relative, and mixed references (and the F4 cycle)

Before we lock a cell, here’s the idea:

• Relative: A1 — row and column both change when filled or copied.

• Absolute: $A$1 — row and column stay fixed.

• Mixed: $A1 (column fixed) or A$1 (row fixed).

While editing a formula, place the caret on the reference and press:

• Windows: F4

• Mac: Fn+F4 (or Command+T, depending on keyboard settings)

Each press cycles A1 → $A$1 → A$1 → $A1 → A1. Microsoft documents this in Switch between relative, absolute, and mixed references.

Step-by-step: lock a divisor like $B$1 and fill down

Goal: Divide a list in A2:A7 by the fixed divisor in B1.

1. Enter the setup

   • A2:A7: your numbers (e.g., 10, 12, 16, 20, 25, 30)

   • B1: divisor (e.g., 2). Make sure it isn’t blank or zero.

2. Write the first formula in C2

   • Type =A2/B1

3. Lock the divisor

   • With the cursor on B1 in the formula, press F4 (Windows) or Fn+F4 / Command+T (Mac) → it becomes $B$1

   • Your formula should read =A2/$B$1

4. Confirm and fill

   • Press Enter to calculate C2

   • Fill to C7 by dragging the fill handle, or select C2:C7 and press Ctrl+D (Windows) / Command+D (Mac). See Microsoft’s guidance to fill a formula down into adjacent cells.

5. Inspect a couple of filled cells

   • C3 should read =A3/$B$1; C4 should read =A4/$B$1 (A‑part changes; $B$1 stays fixed)

Mini example (ASCII view)

   A      B      C
1         2     (Divisor)
2  10           =A2/$B$1 → 5
3  12           =A3/$B$1 → 6
4  16           =A4/$B$1 → 8
5  20           =A5/$B$1 → 10
6  25           =A6/$B$1 → 12.5
7  30           =A7/$B$1 → 15

Tips

• For long ranges, select C2, then Shift+click C1000 (for example), and press Ctrl+D / Command+D to fill without dragging.

• Many users double‑click the fill handle to auto‑fill down adjacent data. If it doesn’t behave as expected, use drag or the Fill Down shortcut instead. Microsoft’s official docs emphasize drag and fill commands.

Lock cell reference in Excel when filling right (mixed references)

When you fill across a row, you may want a column or a row to stay put. Use mixed references: $B1 locks the column B; B$1 locks row 1. For a tiny 2‑D multiplier table:

     A     B     C     D
1         1.1   1.2   1.3   (column factors)
2  10
3  20
4  30

In B2, enter =$A2*B$1 and fill right and down. Why it works:

• $A2 locks column A for the row factors (A2, A3, A4...)

• B$1 locks row 1 for the column factors (B1, C1, D1)

Microsoft explains how references transform when you copy or fill in Switch between relative, absolute, and mixed references.

Locking ranges inside common functions

You’ll often want fixed ranges inside functions so they don’t drift when you copy formulas:

• COUNTIF: =COUNTIF($B$2:$B$11, "CO")

• SUMIFS: =SUMIFS($D$2:$D$100, $A$2:$A$100, G2, $B$2:$B$100, H2)

• XLOOKUP: =XLOOKUP(F2, $A$2:$A$100, $C$2:$C$100)

Technique: Put the caret in each range token and press F4 to set absolute or mixed references as needed. See Microsoft’s Overview of formulas and the reference toggle article.

Troubleshooting matrix (quick fixes)

Verify your work (audit in 30–60 seconds)

• Spot‑check: Click a few filled cells. You should see =A3/$B$1, =A4/$B$1, and so on. The A‑part increments; $B$1 doesn’t.

• Show Formulas: Press Ctrl+` (grave accent) to show formulas across the sheet and confirm the pattern visually. Microsoft explains this in Show and print formulas.

• Evaluate Formula: Formulas tab > Formula Auditing > Evaluate Formula to step through a sample cell. See Microsoft’s guidance on displaying relationships between formulas and cells.

• Excel for the web: Some shortcuts differ in the browser. If F4 doesn’t toggle references online, edit on desktop or add dollar signs manually.

Named range alternative (readability boost)

If B1 is a long‑lived constant, name it Divisor and use =A2/Divisor. It reads better and is harder to break.

Steps (about 1–2 minutes)

1. Select B1 and click in the Name box (left of the formula bar). Type Divisor and press Enter.

2. In C2, use =A2/Divisor and fill down.

3. Verify in Formulas > Name Manager that Divisor points to $B$1. Microsoft covers the basics in Define and use names in formulas and Use the Name Manager.

References and further reading (Microsoft‑first)

• Switch between relative, absolute, and mixed references

• Keyboard shortcuts in Excel

• Fill a formula down into adjacent cells

• Show and print formulas and Display relationships between formulas and cells

• Using structured references with Excel tables

• How to correct a #DIV/0! error

Next steps

Prefer to analyze data and build charts without writing formulas? hiData can help streamline spreadsheet‑heavy workflows in plain English: hiData.

Practice a few fills with =A2/$B$1 and watch how the A‑part travels while the divisor stays put. Once that clicks, mixed references for 2‑D tables will feel natural—what will you lock next?