Cell locking in Excel - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does 'cell locking' mean in Excel?

Cell locking means preventing changes to a cell's content by protecting it. Locked cells cannot be edited when the sheet is protected.

beginner

How do you lock a cell in Excel?

By default, all cells are locked but this only works after you protect the sheet. To lock specific cells, select them, right-click, choose 'Format Cells', go to the 'Protection' tab, and check 'Locked'. Then protect the sheet.

intermediate

What is the difference between 'locking' a cell and 'protecting' a sheet?

Locking a cell marks it as locked but does not prevent editing until the sheet is protected. Protecting the sheet activates the locking and stops changes to locked cells.

intermediate

How can you allow users to edit some cells but lock others?

Unlock the cells you want users to edit by unchecking 'Locked' in Format Cells. Then protect the sheet. Only locked cells will be uneditable.

beginner

What happens if you try to edit a locked cell on a protected sheet?

Excel shows a warning message saying the cell or chart is protected and cannot be changed.

What must you do after locking cells to prevent editing?

A Save the workbook

B Protect the sheet

C Hide the cells

D Change cell color

Locking cells only works after you protect the sheet.

By default, are cells locked or unlocked in Excel?

A Locked

B Hidden

C Unlocked

D Protected

All cells are locked by default but locking has no effect until sheet protection is on.

How do you allow editing only in certain cells?

A Unlock those cells and protect the sheet

B Lock those cells and protect the sheet

C Hide those cells

D Change font color

Unlock cells you want editable, then protect the sheet to lock the rest.

What tab do you use to lock or unlock cells?

A Data tab

B Home tab

C Protection tab in Format Cells

D Review tab

Cell locking is set in the Protection tab inside Format Cells.

What message appears if you try to edit a locked cell on a protected sheet?

A Cell unlocked

B Cell format changed

C File saved

D The cell or chart is protected and cannot be changed

Excel warns you that the cell is protected and cannot be edited.

Explain how to lock specific cells in Excel so others cannot change them.

Think about the two steps: locking cells and protecting the sheet.

Describe the difference between locking cells and protecting a sheet in Excel.

One is a setting on cells, the other is a sheet-wide action.

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