How to Lock Excel Cells Against Editing While Allowing Copying
Question details
The user wants to protect reference content in an Excel worksheet so others can copy the data without modifying the original cells.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Creating a shared worksheet where specific guidance or reference cells need to remain intact but accessible for copying.
- Observed behavior
- Cells need to be locked against accidental edits while preserving the ability for users to select and copy the content.
Before applying worksheet protection, verify which cells should remain editable by users and which should be locked as read-only reference data.
Configure Cell Locking and Apply Worksheet Protection
Unlock the specific cells that require editing, then protect the entire worksheet while granting permission to select locked cells.
In Excel, all cells are locked by default, but this lock only takes effect once the worksheet is protected. To allow users to edit certain cells while locking others, you must format the editable cells as 'Unlocked' before enabling sheet protection.
Highlight all the cells or ranges in your worksheet that users should be allowed to modify.
Press Ctrl+1 to open the Format Cells dialog box. Navigate to the Protection tab, uncheck the 'Locked' checkbox, and click OK.
Navigate to the Review tab on the main ribbon and click on the 'Protect Sheet' button.
In the Protect Sheet dialog, ensure that both 'Select locked cells' and 'Select unlocked cells' are checked. This guarantees users can still highlight and copy your reference data.
Type an optional password to prevent unauthorized removal of the protection, then click OK. If you entered a password, re-enter it to confirm.
Easily Protect Your Spreadsheets with WPS Office
WPS Spreadsheet provides a highly intuitive interface to lock cells and protect worksheets, fully compatible with Microsoft Excel formats. You can effortlessly secure your data while allowing colleagues to copy essential information.
- 1. Open your file in WPS Spreadsheet: Launch WPS Office and open your spreadsheet document.
- 2. Unlock the editable cells: Select the cells meant for user input, right-click and choose 'Format Cells'. Go to the Protection tab and uncheck the 'Locked' option.
- 3. Apply Sheet Protection: Navigate to the 'Review' tab and click 'Protect Sheet'. Ensure 'Select locked cells' is checked to allow copying, set your password, and click OK.
Frequently Asked Questions
Why can't I edit any cells after protecting the sheet?
By default, every cell in a new worksheet has its 'Locked' property enabled. If you protect the sheet without first unlocking specific cells, the entire worksheet becomes read-only.
Can users copy the formulas from my locked cells?
Yes, if users have permission to select the locked cells, they can copy the formulas. To prevent users from seeing or copying the formulas while still viewing the result, you must check the 'Hidden' box in the Format Cells Protection tab before applying sheet protection.
How do I temporarily remove the protection to update my reference data?
Go to the Review tab on the ribbon and click 'Unprotect Sheet'. If a password was set when the protection was applied, you will be prompted to enter it before you can make any edits.
Is worksheet protection secure enough to hide confidential data?
No. Worksheet protection is designed to prevent accidental modifications, not to serve as a strict security measure. Users can still copy data to another workbook, and protection can sometimes be bypassed. It should not be used to secure highly sensitive or confidential information.
