Password Protecting/Locking Specific Cells in a Spreadsheet (some but not all)

Question

Hello community!

I am stuck on a problem locking some, but not all of the cells (columns) in my excel spreadsheet. It is a tracker and we want the general staff to be able to access the first set of columns so they can input data. For the second part of the spreadsheet, we need to lock access so only the core team members can change the status of the columns. It is a project tracker. I've tried the steps from the link below but keep getting stuck at the end, instead of password protecting just the cells I need the entire workbook keeps asking for a password.

https://support.microsoft.com/en-us/office/lock-or-unlock-specific-areas-of-a-protected-worksheet-75481b72-db8a-4267-8c43-042a5f2cd93a

Is there anyone who knows what I might be doing wrong? I am open to alternative pathways if there is a better way to achieve this result.

Looking forward to collaborating on this with someone out there who may know where I am going wrong. Any assistance would be appreciated.

Gratefully,

Megan

Answer 1

Hi @Megan Pérez, 

Good day! I hope you're doing well. 

Based on your description, this behavior can happen because of the order Excel requires when locking specific cells. By default, all cells are locked, so the key is to unlock first, then protect the worksheet. If the steps are done out of order, Excel may prompt for a password on the entire sheet. 

Based on my testing, the steps below should produce the expected result. 

Scenario 

• General staff can edit columns A:E without a password 
• Core team members can update columns F:J (status fields) and will be prompted for a password before editing   

Step-by-step instructions 

1. Unlock the general staff input columns 

• Select columns A:E 
• Right‑click > Format Cells 

• Go to the Protection tab 
• Uncheck Locked 

• Click OK 

Note: Leave columns F:J locked (this is the default behavior). 

2. Allow restricted editing for the status columns 

• Go to Review > Allow Users to Edit Ranges 

• Select New 
• Set the range to F:J 
• Set a password for the range and share this password only to the core team members.

• Click Apply, then OK 

3. Protect the worksheet 

• Go to Review > Protect Sheet 
• Configure the allowed actions as needed (for example: Select locked cells, Select unlocked cells, Edit objects, Edit scenarios) 

• Enter a sheet protection password (if required) 
• Click OK 

4. Expected outcome 

• Columns A:E can be edited without any password prompts 
• Columns F:J will prompt for a password before allowing edits (when double-click on the cell in this range)

 

Please confirm whether this achieves the behavior you’re looking for. If your definition of “changing the status” requires a different setup, or if this workbook is being used in Excel Online or a shared environment, feel free to reach back and I'll help adjust the approach. 

---

If you have extra questions about this answer, please click "Comment". 

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

To lock only some columns for core team members while leaving others editable for general staff, two key concepts are needed:

1. Cell lock state (Locked vs Unlocked)
2. Sheet protection (with options and/or editable ranges)

If the whole sheet is asking for a password for any edit, it usually means all cells are still marked as Locked when the sheet is protected. Adjust the cell locking first, then re‑protect the sheet.

Below is a concrete path that matches the documentation linked in the context.

Option 1 – Simple: One password for the whole protected area

This lets everyone edit the “staff” columns and nobody edit the “core team” columns (unless they know the sheet password). It does not distinguish users.

1. Remove existing protection
   • Go to Review tab > Unprotect Sheet.
   • Enter the password if prompted.
2. Unlock everything first
   • Select the entire sheet (click the square at the top‑left of the grid or press Ctrl+A until all cells are selected).
   • Press Ctrl+1 (or Ctrl+Shift+F) to open Format Cells.
   • Go to the Protection tab.
   • Clear the Locked checkbox and select OK.
   • Now, when protection is turned on, all cells will be editable by default.
3. Lock only the “core team” columns
   • Select just the columns (or cells) that only the core team should be able to change (for example, status columns).
   • Press Ctrl+1 again, go to Protection.
   • Check Locked and select OK.
4. Protect the sheet
   • Go to Review > Protect Sheet.
   • In Allow all users of this worksheet to, leave Select unlocked cells checked so staff can move through the editable columns. Optionally leave Select locked cells checked if they should be able to see but not change locked cells.
   • Do not check options like Format cells, Insert rows, etc., if those actions should be blocked.
   • Enter a password in Password to unprotect sheet (optional but recommended), then confirm.

Result:

• Staff can edit only the unlocked (first set of) columns.
• Locked “core team” columns cannot be edited unless the sheet is unprotected with the password.

If currently the entire sheet asks for a password for every edit, the missing step is usually step 2 (unlocking all cells before selectively locking the core‑team columns).

Option 2 – Advanced: Different editable ranges for different people

If the environment is on a Windows domain and different named users should have different edit rights (for example, general staff can edit only columns A–D; core team can also edit columns E–H), use Allow Edit Ranges.

1. Make sure the sheet is unprotected
   • Review > Unprotect Sheet.
2. Set up editable ranges
   • Go to Review > Allow Edit Ranges.
   • In the Allow Users to Edit Ranges dialog:
     • Select New to add a range for general staff (for example, Title: StaffInput, Refers to cells: =$A:$D).
     • Optionally set a Range password if access should be password‑based instead of user‑based.
     • To restrict by user, select Permissions > Add, then add the user or group names that should be able to edit that range.
   • Repeat New for the core‑team‑only columns (for example, =$E:$H) and grant permissions only to core team accounts.
3. Protect the sheet
   • After ranges are defined, select Protect Sheet in the same dialog or via Review > Protect Sheet.
   • Set a sheet password if desired.

Result:

• Only users listed in the permissions for each range (or who know the range password) can edit those specific columns while the sheet is protected.

If the goal is simply “some columns editable, others locked” and not user‑specific permissions, Option 1 is usually sufficient and avoids the complexity of Allow Edit Ranges.

---

References:

• Lock or unlock specific areas of a protected worksheet
• Protect a worksheet