How To Lock Cells In An Excel Worksheet
Summary
This guide helps you troubleshoot and resolve: How To Lock Cells In An Excel Worksheet. Follow the steps below to fix the issue.
Common Causes
Shared spreadsheets are a common tool for trades and construction businesses to track job costs, material lists, and project schedules. However, when multiple people access the same file, it is easy for someone to accidentally overwrite a formula or delete a critical value. Locking specific cells in Microsoft Excel allows you to protect important data while still permitting edits to designated input areas, such as quantities or dates.
This guide is written for trade businesses using Microsoft 365. For example, you can protect job costing formulas and material price lists while allowing site foremen to edit quantities and project schedule dates.
Quick Fix Steps
- Unlock all cells in the worksheet.
- Select the cells that users should be allowed to edit and ensure they remain unlocked.
- Lock all other cells that should not be edited.
- Protect the worksheet to apply the locking rules.
- Optionally, use Allow Edit Ranges to permit specific users to edit designated cells.
Detailed Instructions
Step 1: Unlock all cells first
- Open your Excel workbook and select the worksheet you want to protect.
- Click the Select All button (the corner where row numbers and column letters meet) or press Ctrl+A to select all cells.
- Right-click the selection and choose Format Cells from the context menu.
- In the Format Cells dialog box, click the Protection tab.
- Clear the Locked checkbox.
- Click OK.
Step 2: Lock the cells that should remain protected
- Highlight the cells or ranges that users should NOT modify (for example, job costing formulas, material price lists, or header rows).
- Right-click the selection and choose Format Cells from the context menu.
- In the Format Cells dialog box, click the Protection tab.
- Select the Locked checkbox.
- Click OK.
Note: By default, all cells in an Excel worksheet are locked. However, locking has no effect until you protect the worksheet.
Step 3: Protect the worksheet
- Go to the Review tab on the Excel ribbon.
- Click Protect Sheet.
- Optionally, enter a password to prevent unauthorised users from unprotecting the sheet.
- Under Allow all users of this worksheet to, select the actions you want to permit, such as Select unlocked cells and Select locked cells.
- Click OK.
- If you entered a password, re-enter it to confirm and click OK.
Warning: Excel worksheet protection prevents accidental changes but is not security. Passwords can be removed with third-party tools. Do not rely on a protected sheet to secure sensitive financial data; store sensitive information in a secure system with proper access controls.
Step 4: Allow specific users to edit designated ranges
- With the worksheet protected, go to the Review tab.
- Click Allow Edit Ranges.
- In the Allow Users to Edit Ranges dialog box, click New.
- Enter a title for the range.
- Click the range selector button and select the cells on the worksheet that specific users should be able to edit (for example, quantity columns or date fields).
- Optionally, enter a password for the range.
- Click Permissions to add specific users who can edit the range without a password.
- Click OK to save the range.
- Click OK to close the Allow Users to Edit Ranges dialog box.
Note: Allow Edit Ranges is useful for shared trade spreadsheets where site foremen need to update quantities or dates while job cost formulas and material prices remain locked.
Step 5: Test the protection
- Click on an unlocked cell and enter data to confirm it can be edited.
- Click on a locked cell. Excel should display a message stating that the cell or chart you are trying to change is protected.
Troubleshooting
I cannot edit cells that should be unlocked
- Check that the Locked checkbox was cleared for those cells before you protected the sheet.
- Ensure you selected the correct range before opening the Format Cells dialog.
- Remember that you must unprotect the sheet (Review > Unprotect Sheet) to change the locking status of any cells.
The Protect Sheet button is greyed out or unavailable
- The file may be stored in OneDrive or SharePoint and opened in co-authoring mode. Protect Sheet may be unavailable while multiple users are editing the file simultaneously. Ask other users to close the file, or open the file in the Excel desktop app and try again.
- The workbook may be shared. Go to Review > Unshare Workbook (if available) and try again.
- The file may be opened in compatibility mode. Save the file as an Excel Workbook (
.xlsx) and try again.
I forgot the worksheet password
- Excel does not provide a way to recover a forgotten sheet protection password.
- If you have an earlier version of the file without protection, you can copy the data into a new workbook and reapply protection.
Allow Edit Ranges is not available
- Ensure the worksheet is protected before configuring Allow Edit Ranges.
- If the file is stored in OneDrive or SharePoint and co-authoring is active, Allow Edit Ranges may be unavailable. Open the file in the Excel desktop app and try again.
If you need further assistance, Open a Ticket.
Still Having Issues?
💻 Open a Ticket