How To Lock Cells In Excel To Prevent Accidental Changes
Summary
This guide helps you troubleshoot and resolve: How To Lock Cells In Excel To Prevent Accidental Changes. Follow the steps below to fix the issue.
Common Causes
Lock Cells in a Worksheet
This guide explains how to configure Microsoft Excel workbooks so financial documents such as supplier invoices, parts quotes, and customer job sheets cannot be altered unintentionally. Automotive workshops can use these settings to maintain accurate pricing records while allowing front-desk staff to update only the required fields.
- Open the Excel workbook you want to protect. For example, open your standard parts quote template.
- Select all cells by clicking the corner button where row and column headers meet, or press
Ctrl + A. - Right-click the selection and choose Format Cells.
- Go to the Protection tab and ensure the Locked checkbox is ticked.
- Click OK.
- Go to the Review tab on the ribbon.
- Click Protect Sheet.
- Enter a password if required, choose the actions users are allowed to perform, and click OK.
Unlock Specific Cells for Editable Fields
If front-desk staff need to update certain fields—such as customer details or job status—while keeping pricing and parts data fixed, unlock those cells first.
- Select the cells or ranges that should remain editable. For example, select the cells where job status updates are entered on a customer job sheet.
- Right-click and choose Format Cells.
- Go to the Protection tab.
- Untick the Locked checkbox.
- Click OK.
You can select multiple non-adjacent ranges by holding down Ctrl while clicking each cell or range before opening Format Cells.
Protect the Worksheet
Once cells are marked as locked or unlocked, enable sheet protection to enforce the settings.
- Go to the Review tab on the ribbon.
- Click Protect Sheet.
- Optionally enter a password to prevent unauthorised users from unprotecting the sheet.
- Under Allow all users of this worksheet to:, tick the actions you want to permit, such as Select locked cells and Select unlocked cells.
- Click OK.
- If you set a password, re-enter it to confirm.
- Test the protection by attempting to edit a locked cell, such as a supplier price cell, and confirming that Excel prevents the change.
Protect Workbook Structure (Optional)
Use this to prevent users from adding, deleting, moving, or hiding worksheets within the file.
- Go to the Review tab.
- Click Protect Workbook.
- Enter a password if desired and click OK.
Protected View for Files from Untrusted Locations
Excel opens files downloaded from the internet or received via email in Protected View to help prevent potentially unsafe content from running. This is useful for supplier invoices or parts catalogues received from external sources.
- Open Excel and go to File > Options.
- Select Trust Center and click Trust Center Settings.
- Choose Protected View.
- Ensure the options for files originating from the internet or other potentially unsafe locations are ticked.
- Click OK to save the settings.
Troubleshooting
Cells can still be edited after protection is enabled
- Check that the cells you want to protect are marked as Locked in Format Cells > Protection.
- Ensure sheet protection is actually turned on via Review > Protect Sheet.
- Test the protection after applying it to verify settings work as intended.
Protect Sheet button is greyed out
- The workbook may be shared or the workbook structure may already be protected. Go to Review > Protect Workbook to check if structure protection is active.
- If the file is a shared workbook, turn off sharing before applying sheet protection.
Forgotten worksheet password
- Excel does not provide a password recovery option for protected sheets.
- If you have an unprotected backup copy, you can re-apply protection with a new password.
- Otherwise, recreate the sheet settings in a new workbook.
Still Having Issues?
💻 Open a Ticket