Do you want to lock cells in Excel to prevent unwanted changes in an Excel sheet? Here’s how to lock cells and protect sheets in Microsoft Excel.
Tip: also learn how to use Power Query and Power Pivot in Microsoft Excel.
How to Lock All Cells in a Sheet
When creating a new sheet, all cells are automatically locked. However, there are cases when the last user keeps the cells unlocked manually. Here’s how to lock all cells in a sheet:
- Open the Excel workbook containing the cells you want to lock.
- Highlight all cells by clicking on the Select All button at the top left of the Excel sheet.
- Right-click on the highlighted area and select “Format Cells.”
- In the “Format Cells” window, go to the “Protection” tab, check the box beside “Locked,” and click on “OK.”
- Go to the “Review” tab and click on “Protect Sheet” (more information on password protecting your spreadsheet here).
- Optionally, add a password to unprotect the sheet and click on “OK.”
- Select what users can and can’t do with the locked cells and click on “OK.”
- Reenter the unprotect password.
How to Unlock All Cells in Excel
Follow these steps to unlock all locked cells in a sheet:
- Go to the “Review” tab and click on “Uprotect Sheet.”
- Enter the unprotect sheet password when prompted.
- Highlight all cells by clicking on the Select All button at the top left of the Excel sheet.
- Right-click on the highlighted area and select “Format Cells.”
- In the “Format Cells” window, go to the “Protection” tab, uncheck the box beside “Locked,” and click on “OK.”
Extra: Master VLOOKUP guide for Microsoft Excel here.
How to Lock Specific Cells in Excel
You can lock specific cells in Excel and keep other cells unlocked in the same sheet by following these steps:
- Highlight the cells you want to lock.
- Right-click on the highlighted area and select “Format Cells.”
- In the “Format Cells” window, go to the “Protection” tab, check the box beside “Locked,” and click on “OK.”
- Go to the “Review” tab and click on “Protect Sheet.”
- Optionally, add a password to unprotect the sheet and click on “OK.”
- Select what users can and can’t do with the locked cells and click on “OK.”
- Reenter the unprotect password.
How to Unlock Specific Cells in Excel
If you need to change specific cells that are locked, follow the instructions below to unlock them:
- Go to the “Review” tab and click on “Uprotect Sheet.”
- Enter the unprotect sheet password.
- Highlight the specific cells you want to unlock.
- Right-click on the highlighted area and select “Format Cells.”
- In the “Format Cells” window, go to the “Protection” tab, uncheck the box beside “Locked,” and click on “OK.”
Use these tips to find duplicates and remove them from your spreadsheet.
What You Can and Can’t Do when Locking Cells in Excel
What you can do:
- Lock formulas in cells
- Choose specific cells to lock and leave unselected cells unlocked
- Protect your sheet and workbook with a password
- Lock symbols in cells
What you can’t do:
- Lock cells in the Web version of Microsoft Excel
- Edit locked cells in a protected sheet
How to Protect a Workbook in Excel
You can lock an Excel workbook to protect it from the Insert, Delete, Rename, Move, Copy, Hide, and Unhide commands. Here’s how to protect a workbook:
- Go to the Review tab and click on “Protect Worksheet.”
- Optionally, add a protection password and click on “OK.”
- Reenter the password.
Frequently Asked Questions
What will happen when I unprotect a protected sheet?
Once you enter the password for a protected sheet, you will need to create a new password or use the same password as before to protect it again with a password.
Is it necessary to enable Protect Sheet after locking cells?
Yes, as locked cells can still be edited when unprotected. Therefore, locking cells with unprotected sheet is useless.
All screenshots by Natalie dela Vega
Our latest tutorials delivered straight to your inbox
