Cell locking in Excel - Step-by-Step Guide

Introduction

Cell locking helps protect specific cells in your Excel sheet so others cannot change them. This is useful when you want to keep important data safe while allowing edits elsewhere.

When you want to prevent accidental changes to formulas in your budget sheet

When sharing a report and you want users to only enter data in certain cells

When creating a form where only input fields should be editable

When protecting headers or labels from being modified

When you want to lock cells but still allow sorting or filtering on the sheet

Steps

Step 1: Select the cells you want to allow users to edit

- Excel worksheet

The chosen cells are highlighted

💡 By default, all cells are locked, so you unlock the editable ones first

Step 2: Right-click the selected cells and choose 'Format Cells'

- Context menu

The Format Cells dialog box opens

Step 3: Click the 'Protection' tab

- Format Cells dialog box

Protection options appear

Step 4: Uncheck the 'Locked' checkbox

- Protection tab

Selected cells are set to be unlocked when sheet protection is applied

Step 5: Click 'OK' to close the dialog

- Format Cells dialog box

Cell formatting is updated

Step 6: Go to the 'Review' tab and click 'Protect Sheet'

- Ribbon on the Review tab

The Protect Sheet dialog box opens

Step 7: Enter a password if you want (optional) and make sure 'Protect worksheet and contents of locked cells' is checked

- Protect Sheet dialog box

Sheet protection settings are ready

Step 8: Click 'OK' to apply protection

- Protect Sheet dialog box

Locked cells cannot be edited; unlocked cells remain editable

Before vs After

Before

All cells are editable, including formulas and headers

After

Only unlocked cells can be edited; locked cells like formulas and headers are protected from changes

Settings Reference

Locked

📍 Format Cells dialog > Protection tab

Determines if a cell is locked when sheet protection is enabled

Default: Checked

Protect Sheet

📍 Review tab > Protect Sheet button

Enables protection on the sheet to enforce locked cell restrictions

Default: No password, all locked cells protected

Common Mistakes

Not unlocking the cells you want editable before protecting the sheet

Because all cells are locked by default, protection will block editing everywhere

First unlock the cells you want users to edit, then protect the sheet

Forgetting to apply sheet protection after unlocking cells

Unlocking cells alone does not prevent editing unless the sheet is protected

After unlocking cells, always use 'Protect Sheet' to enforce locking

Using a password but forgetting it

You cannot unprotect the sheet without the password, risking loss of access

Use a password only if you can remember it or store it safely

Summary

Cell locking protects important cells from being changed by others.

By default, all cells are locked; unlock only the cells you want editable before protecting the sheet.

Sheet protection must be enabled to enforce locking, optionally with a password.

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