how to lock cells in excel

excel logo

You’ve worked hard on your spreadsheet. You don’t want anyone to mess it up. Fortunately, Excel provides some pretty good tools for preventing people from editing various parts of a workbook.

Protection in Excel is password-based and happens at three different levels.

  • Workbook: You have a few options for protecting a workbook. You can encrypt it with a password to limit who can even open it. You can make the file open as read-only by default so that people have to opt into editing it. And you protect the structure of a workbook so that anyone can open it, but they need a password to rearrange, rename, delete, or create new worksheets.
  • Worksheet: You can protect the data on individual worksheets from being changed.
  • Cell: You can also protect just specific cells on a worksheet from being changed. Technically this method involves protecting a worksheet and then allowing certain cells to be exempt from that protection.

You can even combine the protection of those different levels for different effects.

tmntag.cmd.push(function(){tmntag.adTag(‘purch_N_C_0_1’, false);});

Protect an Entire Workbook from Editing

You have three choices when it comes to protecting an entire Excel workbook: encrypt the workbook with a password, make the workbook read-only, or protect just the structure of a workbook.

Encrypt a Workbook with a Password

For the best protection, you can encrypt the file with a password. Whenever someone tries to open the document, Excel prompts them for a password first.

To set it up, open your Excel file and head to the File menu. You’ll see the “Info” category by default. Click the “Protect Workbook” button and then choose “Encrypt with Password” from the dropdown menu.

click protect workbook and choose the encrypt with password command

In the Encrypt Document window that opens, type your password and then click “OK.”

type password and click OK

Note: Pay attention to the warning in this window. Excel does not provide any way to recover a forgotten password, so make sure you use one you’ll remember.

Type your password again to confirm and then click “OK.”

confirm password and click ok

You’ll be returned to your Excel sheet. But, after you close it, the next time you open it, Excel will prompt you to enter the password.

type password and click ok

If you ever want to remove the password protection from the file, open it up (which of course requires you to provide the current password), and then follow the same steps you used for assigning the password. Only this time, make the password field blank and then click “OK.”

use a blank password to clear password protection

Make a Workbook Read-Only

Making a workbook open as read-only is super simple. It doesn’t offer any real protection because anyone who opens the file can enable editing, but it can serve as a suggestion to be careful about editing the file.

To set it up, open your Excel file and head to the File menu. You’ll see the “Info” category by default. Click the “Protect Workbook” button and then choose “Encrypt with Password” from the dropdown menu.

click protect workbook and choose the always open read only command

Now, whenever anyone (including you) opens the file, they get a warning stating that the file’s author would prefer they open it as read-only unless they need to make changes.

warning that the file's author wants you to open it read only

To remove the read-only setting, head back to the File menu, click the “Protect Workbook” button again, and toggle the “Always Open Read-Only” setting off.

Protect a Workbook’s Structure

The final way you can add protection at the workbook level is by protecting the workbook’s structure. This type of protection prevents people who don’t have the password from making changes at the workbook level, which means they won’t be able to add, remove, rename, or move worksheets.

To set it up, open your Excel file and head to the File menu. You’ll see the “Info” category by default. Click the “Protect Workbook” button and then choose “Encrypt with Password” from the dropdown menu.

click protect workbook and choose protect workbook structure command

Type your password and click “OK.”

type a password and click ok

Confirm your password and click “OK.”

confirm password and click ok

Anyone can still open the document (assuming you didn’t also encrypt the workbook with a password), but they won’t have access to the structural commands.

structural commands are unavailable

If someone knows the password, they can get access to those commands by switching over to the “Review” tab and clicking the “Protect Workbook” button.

on the review tab, click protect workbook

They can then enter the password.

type your password

And the structural commands become available.

structural commands are now available

It’s important to understand, however, that this action removes the workbook structure protection from the document. To reinstate it, you must go back to the file menu and protect the workbook again.

Protect a Worksheet from Editing

You can also protect individual worksheets from editing. When you protect a worksheet, Excel locks all of the cells from editing. Protecting your worksheet means that no one can edit, reformat, or delete the content.

Click on the “Review” tab on the main Excel ribbon.

switch to the review tab

Click “Protect Sheet.”

click protect sheet button

Enter the password you would like to use to unlock the sheet in the future.

type your password

Select the permissions you would like users to have for the worksheet after it is locked. For example, you might want to allow people to format, but not delete, rows and columns.

choose permissions

Click “OK” when you’re done selecting permissions.

click OK

Re-enter the password you made to confirm that you remember it and then click “OK.”

confirm your password

If you need to remove that protection, head to the “Review” tab and click the “Unprotect Sheet” button.

on review tab, click unprotect sheet

Type your password and then click “OK.”

type your password

Your sheet is now unprotected. Note that the protection is entirely removed and that you’ll need to protect the sheet again if you want.

Protect Specific Cells From Editing

Sometimes, you may only want to protect specific cells from editing in Microsoft Excel. For example, you might have an important formula or instructions that you want to keep safe. Whatever the reason, you can easily lock only certain cells in Microsoft Excel.

Start by selecting the cells you do not want to be locked. It might seem counterintuitive, but hey, that’s Office for you.

select cells you want unlocked

Now, right-click on the selected cells and choose the “Format Cells” command.

right-click selected cells and choose format cells

In the Format Cells window, switch to the “Protection” tab.

switch to the protection tab

Untick the “Locked” checkbox.

Untick the Locked checkbox.

And then click “OK.”

click ok

Now that you’ve selected the cells for which you want to allow editing, you can lock the rest of the worksheet by following the instructions in the previous section.

Note that you can lock a worksheet first and then select the cells you want to unlock, but Excel can be a little flaky about that. This method of selecting the cells you want to stay unlocked and then locking the sheet works better.


body #primary .entry-content ul#nextuplist {list-style-type: none;margin-left:0px;padding-left:0px;}
body #primary .entry-content ul#nextuplist li a {text-decoration:none;color:#1d55a9;}

Leave a Reply

Your email address will not be published. Required fields are marked *