How to Lock Cells in Microsoft Excel

by
Spread the love

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.

Content

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:

  1. Open the Excel workbook containing the cells you want to lock.
  2. Highlight all cells by clicking on the Select All button at the top left of the Excel sheet.

  1. Right-click on the highlighted area and select “Format Cells.”

  1. In the “Format Cells” window, go to the “Protection” tab, check the box beside “Locked,” and click on “OK.”

  1. Go to the “Review” tab and click on “Protect Sheet” (more information on password protecting your spreadsheet here).

  1. Optionally, add a password to unprotect the sheet and click on “OK.”
  2. Select what users can and can’t do with the locked cells and click on “OK.”

  1. Reenter the unprotect password.

How to Unlock All Cells in Excel

Follow these steps to unlock all locked cells in a sheet:

  1. Go to the “Review” tab and click on “Uprotect Sheet.”

  1. Enter the unprotect sheet password when prompted.
  2. Highlight all cells by clicking on the Select All button at the top left of the Excel sheet.

  1. Right-click on the highlighted area and select “Format Cells.”

  1. 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:

  1. Highlight the cells you want to lock.

  1. Right-click on the highlighted area and select “Format Cells.”

  1. In the “Format Cells” window, go to the “Protection” tab, check the box beside “Locked,” and click on “OK.”

  1. Go to the “Review” tab and click on “Protect Sheet.”

  1. Optionally, add a password to unprotect the sheet and click on “OK.”
  2. Select what users can and can’t do with the locked cells and click on “OK.”

  1. 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:

  1. Go to the “Review” tab and click on “Uprotect Sheet.”

  1. Enter the unprotect sheet password.
  2. Highlight the specific cells you want to unlock.

  1. Right-click on the highlighted area and select “Format Cells.”

  1. 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:

  1. Go to the Review tab and click on “Protect Worksheet.”

  1. Optionally, add a protection password and click on “OK.”

  1. 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

Subscribe to our newsletter!

Our latest tutorials delivered straight to your inbox

Sign up for all newsletters.
By signing up, you agree to our Privacy Policy and European users agree to the data transfer policy. We will not share your data and you can unsubscribe at any time. Subscribe


Natalie dela Vega

Natalie is a writer specializing in tech how-tos and gaming. When she’s not writing, she plays PC games and travels. Here at MakeTechEasier, you will see her write about guides, tips, and solutions for Windows and iOS.

Comments are closed