'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Lock Additional Columns After Sheet Protection in Excel
Question details
The user needs to lock additional columns in an Excel worksheet that is already protected without permanently exposing previously protected cells to editing.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Reviewing or updating a shared spreadsheet where different columns need to be locked incrementally after specific team reviews.
- Observed behavior
- The locked attribute of a cell or column cannot be changed while worksheet protection is active, preventing the user from locking new columns directly.
Ensure you have the password for the currently protected worksheet, as you will need it to temporarily remove the protection before modifying the locked status of any new columns.
Temporarily Unprotect, Lock Columns, and Reprotect the Sheet
Because Excel separates the 'Locked' cell format from the 'Protection' state, you must temporarily unprotect the worksheet to change any cell's locked status.
In spreadsheet applications, locking and protecting are two distinct concepts. Locking is a cell-level format that dictates what happens when security is enabled, while protection is the worksheet-level switch that actually enforces those rules. You cannot alter cell formats, including the 'Locked' status, while the protection switch is turned on.
Navigate to the 'Review' tab on the top ribbon and click on 'Unprotect Sheet'. If the sheet was protected with a password, you will be prompted to enter it now.
Click on the column headers (e.g., Column D, E) for the additional columns you want to lock so that the entire columns are highlighted.
Right-click the highlighted columns and select 'Format Cells' (or press Ctrl+1). Go to the 'Protection' tab, check the box next to 'Locked', and click 'OK'.
Go back to the 'Review' tab and click 'Protect Sheet'. Re-enter your desired password, confirm the permissions for users, and click 'OK' to re-enable protection.
Easily Manage Sheet Protection with WPS Office
WPS Spreadsheet offers an intuitive and seamless way to manage cell locks and worksheet protection. It allows you to quickly adjust security settings and is fully compatible with all Microsoft Excel protection mechanisms.
- 1. Open your file in WPS Spreadsheet: Launch WPS Office, open your protected workbook, and head to the 'Review' tab on the ribbon.
- 2. Unprotect the sheet: Click on 'Unprotect Sheet' and input your security password if prompted by the system.
- 3. Lock the new columns: Highlight the columns you want to secure, right-click to choose 'Format Cells', navigate to the 'Protection' tab, and check 'Locked'.
- 4. Re-enable protection: Return to the 'Review' tab, click 'Protect Sheet', set your preferences, and apply the password to secure the newly added columns alongside the old ones.
Frequently Asked Questions
Can I lock a column via VBA without unprotecting the sheet first?
No, even when using VBA macros, the code must first unprotect the worksheet, change the Locked property of the target range, and then reprotect the worksheet. You can automate this process in VBA by passing the password within the script.
Why is the 'Format Cells' option grayed out when I right-click?
The 'Format Cells' option is disabled because the worksheet is currently protected. You must go to the Review tab and click 'Unprotect Sheet' before you can change any formatting, including the locked status of cells.
How can I allow certain users to edit locked columns?
You can use the 'Allow Users to Edit Ranges' feature found under the Review tab before you protect the sheet. This allows you to set specific passwords for different ranges or columns, granting access only to those who have the range-specific password.
