Cell locking in Excel - Cell-by-Cell Formula Trace

Sample Data

This table shows prices and quantities of items. Column C will calculate total cost by multiplying price and quantity.

Cell	Value
A1	Price
B1	Quantity
C1	Total
A2	10
B2	5
C2
A3	20
B3	5
C3

Formula Trace

=A2*$B$2

Step 1: A2

Step 2: $B$2

Step 3: 10 * 5

Cell Reference Map

    A     B     C
1 |Price|Quantity|Total
2 | 10  |  5    |     
3 | 20  |  5    |     

Formula in C2: =A2*$B$2
Arrow from A2 to formula
Arrow from B2 to formula (locked)

Formula in cell C2 uses A2 (price) and locked B2 (quantity). Locking B2 means when copying formula down, B2 reference stays fixed.

Result

    A     B     C
1 |Price|Quantity|Total
2 | 10  |  5    |  50 
3 | 20  |  5    |     

After entering formula =A2*$B$2 in C2, result is 50.

The total in C2 is 50 because 10 (A2) times 5 (locked B2) equals 50.

Sheet Trace Quiz - 3 Questions

What does the $ sign do in the formula =A2*$B$2?

A Locks the reference to cell B2 so it does not change when copied

B Makes the formula calculate faster

C Changes the value in B2

D Adds the values of A2 and B2

Key Result

Use $ before column and/or row to lock that part of the cell reference when copying formulas.

Practice

1. What does locking a cell in Excel do before you protect the sheet?

easy

A. Prevents the cell from being edited after sheet protection is enabled

B. Deletes the cell content automatically

C. Highlights the cell with a color

D. Copies the cell content to another sheet

Solution

1. Step 1: Understand cell locking purpose

   Locking a cell marks it so it cannot be changed once the sheet is protected.

2. Step 2: Effect of protecting the sheet

   Protecting the sheet activates the locking, preventing edits on locked cells.

3. Final Answer:

   Prevents the cell from being edited after sheet protection is enabled -> Option A

4. Quick Check:

   Locking + Protect Sheet = No edits [OK]

Hint: Lock cells then protect sheet to stop edits [OK]

Common Mistakes:

• Thinking locking deletes or copies cells
• Believing locking highlights cells
• Assuming locking works without protecting sheet

2. Which of these is the correct way to unlock a cell before protecting the sheet?

easy

A. Use Data Validation to unlock the cell

B. Double-click cell and press Delete key

C. Right-click cell -> Format Cells -> Protection tab -> Uncheck 'Locked'

D. Insert a comment in the cell

Solution

1. Step 1: Locate cell protection settings

   Right-click the cell and open Format Cells dialog, then go to Protection tab.

2. Step 2: Unlock the cell

   Uncheck the 'Locked' checkbox to make the cell editable after sheet protection.

3. Final Answer:

   Right-click cell -> Format Cells -> Protection tab -> Uncheck 'Locked' -> Option C

4. Quick Check:

   Unlock cells via Format Cells -> Protection [OK]

Hint: Unlock cells via Format Cells -> Protection tab [OK]

Common Mistakes:

• Trying to unlock by deleting content
• Using Data Validation to unlock cells
• Adding comments to unlock cells

3. You locked cells A1:A5 and unlocked B1:B5. After protecting the sheet, which cells can you edit?

medium

A. Only cells A1:A5

B. Only cells B1:B5

C. Both A1:A5 and B1:B5

D. No cells can be edited

Solution

1. Step 1: Understand locked vs unlocked cells

   Locked cells cannot be edited after protection; unlocked cells remain editable.

2. Step 2: Apply to given ranges

   Cells A1:A5 are locked, so protected sheet blocks edits there; B1:B5 unlocked, so editable.

3. Final Answer:

   Only cells B1:B5 -> Option B

4. Quick Check:

   Unlocked cells stay editable after protection [OK]

Hint: Only unlocked cells editable after protection [OK]

Common Mistakes:

• Assuming locked cells are editable
• Thinking protection disables all cells
• Confusing locked and unlocked ranges

4. You locked cells but forgot to protect the sheet. What happens when you try to edit those cells?

medium

A. The sheet becomes read-only

B. Excel shows an error and blocks editing

C. The cells get deleted automatically

D. You can edit the locked cells without restriction

Solution

1. Step 1: Understand locking vs protecting

   Locking marks cells but does not enforce protection until sheet is protected.

2. Step 2: Effect of no protection

   Without protecting the sheet, locked cells behave like normal cells and can be edited freely.

3. Final Answer:

   You can edit the locked cells without restriction -> Option D

4. Quick Check:

   Locking alone does not block edits [OK]

Hint: Protect sheet to activate locking effect [OK]

Common Mistakes:

• Thinking locking alone blocks edits
• Expecting error without protection
• Confusing locking with sheet protection

5. You want to protect a budget sheet so users can only change the 'Notes' column (D) but not the amounts (A:C). How do you set this up?

hard

A. Unlock column D cells, lock columns A:C, then protect the sheet

B. Lock all cells, then protect the sheet without unlocking any cells

C. Unlock columns A:C, lock column D, then protect the sheet

D. Do not lock any cells and protect the sheet

Solution

1. Step 1: Unlock the 'Notes' column (D)

   Make column D cells unlocked so they remain editable after protection.

2. Step 2: Lock columns A:C and protect the sheet

   Lock columns A:C to prevent edits, then enable sheet protection to enforce locking.

3. Final Answer:

   Unlock column D cells, lock columns A:C, then protect the sheet -> Option A

4. Quick Check:

   Unlock editable cells, lock others, then protect [OK]

Hint: Unlock editable cells only, then protect sheet [OK]

Common Mistakes:

• Locking editable column by mistake
• Not protecting sheet after locking
• Unlocking wrong columns