This tutorial explains how to safeguard specific Excel cells from accidental modification or unauthorized editing. It covers locking individual cells, unlocking cells on a protected sheet (with or without a password), and identifying locked/unlocked cells.

Last week's tutorial detailed sheet protection. This guide focuses on a more granular approach, allowing selective cell locking while leaving others editable. For instance, you can protect formula cells while allowing data entry in other cells.

Key Sections:

• Locking Specific Cells in Excel
• Unlocking All Cells (Unprotecting a Sheet)
• Unlocking Individual Cells on a Protected Sheet
• Locking All Cells Except Input Cells
• Locating and Highlighting Locked/Unlocked Cells

Locking Cells in Excel

Locking all cells is simple—protect the sheet. Since the "Locked" attribute is default for all cells, protecting the sheet automatically locks them.

To protect only certain cells, first unlock all cells, then lock the desired ones, and finally protect the sheet.

1. Unlock All Cells:

The "Locked" option is initially enabled for all cells. To selectively lock, unlock everything first:

• Select all cells (Ctrl A or "Select All").
• Open "Format Cells" (Ctrl 1 or right-click > "Format Cells").
• On the "Protection" tab, uncheck "Locked," then click "OK."

2. Select Cells/Ranges/Columns/Rows to Protect:

Select cells using the mouse or keyboard (Shift for adjacent, Ctrl for non-adjacent). Column selection: click the column letter (Ctrl Space for one column, drag for adjacent, Ctrl for non-adjacent). Row selection is similar. To lock all formula cells, use "Go To Special" (Home > Editing > Find & Select > Go To Special) and select "Formulas."

3. Lock Selected Cells:

With cells selected, open "Format Cells" (Ctrl 1 or right-click > "Format Cells"), go to the "Protection" tab, and check "Locked."

4. Protect the Sheet:

Cell locking is ineffective without sheet protection. On the "Review" tab, click "Protect Sheet" (or right-click the sheet tab > "Protect Sheet"). Set a password (optional) and allowed actions, then click "OK."

Unlocking Cells (Unprotecting a Sheet)

To unlock all cells, remove sheet protection: right-click the sheet tab and select "Unprotect Sheet" (or use the "Unprotect Sheet" button on the "Review" tab).

Unlocking Specific Cells on a Protected Sheet

To allow editing of specific cells on a protected sheet (optionally with a password):

1. Select cells to unlock.
2. Go to "Review" > "Changes" > "Allow Users to Edit Ranges." (Unprotect the sheet if this button is grayed out).
3. Click "New…" to add a range. Provide a title, cell reference, and password (optional).
4. Confirm the password.
5. Add more ranges as needed.
6. Click "Protect Sheet" to re-protect, setting a password and allowed actions.

Allowing Specific Users to Edit Without a Password

For frequent editing without password entry, set permissions for specific users:

1. Go to "Review" > "Changes" > "Allow Users to Edit Ranges."
2. Select the range and click "Permissions…"
3. Click "Add…" and enter user names (verify using "Check Names").
4. Set permissions (Allow/Deny) for each user.

Locking Cells Except Input Cells

Modify the built-in "Input" style to unlock input cells:

1. Right-click "Input" style (Home > Styles) > "Modify…"
2. Check "Protection," then click "Format…"
3. Uncheck "Locked" on the "Protection" tab.
4. Apply the modified "Input" style to your input cells.
5. Protect the sheet.

Finding and Highlighting Locked/Unlocked Cells

Use the CELL("protect", cell_address) function (returns 1 for locked, 0 for unlocked). For visual identification, create conditional formatting rules using =CELL("protect",A1)=1 (for locked) and =CELL("protect",A1)=0 (for unlocked), where A1 is the top-left cell of the range. Remember to unprotect the sheet before applying conditional formatting.

This comprehensive guide provides various methods for securing your Excel spreadsheets. Remember to always test your protection settings thoroughly.

The above is the detailed content of How to lock cells in Excel and unlock certain cells on a protected sheet. For more information, please follow other related articles on the PHP Chinese website!