How to Lock Cells in Excel: Excel Protection Tutorial

Protecting your data and maintaining the integrity of your files are super important nowadays. It’s only safe to say that one needs to learn how. Better safe than sorry, right? πŸ˜‰

Well, I have good news for you! Microsoft Excel provides different levels of protection for your data or files. You can protect your file at the worksheet level, workbook level, or even at the file level depending on your needs.

In this article, we’ll teach you how to lock cells in Excel πŸ”’ so you can protect your data and maintain the integrity of your files.

Download this free practice workbook so we can get started!

How to lock an entire worksheet

As mentioned above, Excel offers different levels of protection πŸ”’ You can protect your files at the worksheet level so you can prevent accidental or intentional changes in the important data, formula, or formatting of the cells in your Excel worksheet.

Or protect your files at the workbook level or even file level for maximum security by enabling password protection. These flexible options for protection provide a great balance between security and usability.

So, it’s important to know what level of protection you need for your data or files 😊

Say, you want to prevent other users from accidentally or intentionally adding, moving, or deleting data in a worksheet. What you need is worksheet protection.

It’s a two-step process where you need to lock the cells on your Excel worksheet first, and then protect the sheet entirely with or without a password.

But before you lock cells in Excel, you must first unlock it. Say WHAT? 😱

You might find this odd, as you haven’t (yet) locked any cells. But, Excel locks all the cells by default. So, this will always be your first step to protect cells in Excel.

Kasper Langmann, Microsoft Office Specialist

Don’t worry, you can do this in five easy steps πŸ–

  1. Select all the cells in your worksheet.
  2. Right-click and select Format Cells.
how to lock cells in Excel
  1. Click the Protection Tab in the Format cells window.
  2. Uncheck the Locked check box and the Hidden check box.
uncheck the locked
  1. Finally, click OK.
unlocking cells

High-five! You’ve unlocked all the cells in the worksheet πŸ˜€

Lock specific cells in your worksheet

I’m sure your worksheet contains cells you want to lock. You don’t want anyone to change it because it may contain important data, formulas, and formatting you want to maintain.

For example, when you click certain cells in our practice workbook, they follow a formula.

formula cells

You want to lock these cells so you don’t lose the data and calculations in them 😊

Here’s how you can now lock formula cells or specific cells in Excel.

  1. Click the Find & Select button in the Editing group of the Home tab.
  2. Select Go To Special.
lock formula cells
  1. Select Formulas in the “Go To Special” window.
  2. Click OK.
select formulas

This highlights the formula cells in the worksheet.

formula cells

To ensure that these cells can’t be changed or modified, it’s time to lock them πŸ”’

  1. Right-click the highlighted cells.
  2. Click Format Cells.
click format cells

When the Format Cells dialog box pops up…

  1. Go to the Protection Tab.
  2. Check the Locked check box.
protection tab
  1. Click OK.
how to lock cells in Excel

Now that the cells are locked, complete the worksheet protection process by protecting the entire worksheet. And that’s exactly what we’re going to learn next πŸ˜€

Protect your worksheet

Protecting a worksheet is super easy. The fastest way to do that is to…

  1. Right-click the sheet you want to protect in the worksheet tab.
  2. Click Protect Sheet button.
protect sheet
  1. Make sure to check the boxes the same as below.

You can allow users to do certain things in the worksheet by checking and unchecking the boxes. You can also set a password but this is optional. For now, we will just leave it blank.

  1. Click OK.
protect sheet window

It’s time to check the protection of your cells with formulas.

Try to type into those cells or press Delete… You can’t! πŸ˜…

You will get a message box that tells you the cell you are trying to change is protected.

protected worksheet

But other cells that were not a part of the selected range of cells with formulas can still be changed.

Protect an entire workbook

In Excel, when we say workbook, what we are referring to is the Excel file itself. That’s because a workbook is an entire file containing all the worksheets.

So, you can synonymously use the terms Workbook or Excel file to mean an Excel file πŸ‘

But when talking about Excel protection, you should note that there’s workbook-level protection and file-level protection. These are two very different levels of protection.

The workbook level of protection does not restrict users from opening the file. Users just can’t delete, move, hide or rename any of the worksheets in your workbook file.

Let’s learn more about workbook-level protection in this section and focus on file-level protection in the next.

To start…

  1. Go to the Review Tab.
  2. In the Protect group, click Protect Workbook.
protect workbook

Now a β€œProtect Structure and Windows” dialog box appears.

  1. Enter a password. By the way, this is optional. For now, let’s leave it blank.
  2. Check the box next to Structure.
  3. Click OK.
protection options

Now, let’s try to add, delete, move, or rename a worksheet.

No, wait, you can’t! πŸ˜…

Since you protected the structure of the workbook, you can’t add, delete, move or rename any worksheet in this workbook.

protect the sheet

You can also protect your workbook with a password πŸ”

Password protection provides the workbook owner (you) more control over who can make changes to the structure of your workbook.

If you don’t use a password, it serves more as a simple reminder to other users to not make changes. Remember, they can simply unprotect the workbook if they wanted to.

Workbook-level protection should not be confused with file-level protection. Why? Because users could still open the file and make changes to the contents of the worksheets themselves.

So, what is file-level protection? πŸ€” Read on.

Kasper Langmann, Microsoft Office Specialist

Password-protect an Excel file

The highest level of protection that Excel offers is file-level protection.

The file-level protection prevents unauthorized access and changes to your Excel file. If you want to allow only certain users to even open the file, this is the level of protection you want to use.

Locking access to your Excel file with a password encrypts the file. So, let’s look at how to do that!

  1. With the file open, go to the File tab.
  2. Click the Info button on the left sidebar.
  3. Click the drop-down arrow of the Protect Workbook button.
  4. Select Encrypt with Password.
protect workbook
  1. On the Encrypt Document dialog box, type in a password. For example, let’s set the password 12345.
  2. Click OK.
encrypt with password
  1. Re-enter the password to confirm.
  2. Finally, click OK.
lock Excel file

When you save the file and anyone attempts to open it, they will be prompted for a password. Like this πŸ‘‡

enter password

Anyone without a password is restricted from opening the file. This provides maximum protection of your data and maintains the integrity of your file 😊

To reverse this encryption, simply repeat the previous process. Delete the password from the input box for password and click OK.

Kasper Langmann, Microsoft Office Specialist

How to unlock cells

Say, you want to make adjustments to the level of protection of your Excel files. Or you either want to allow other users to make changes or modifications.

Here are the steps on how to unlock your cells or undo any protection you added to your Excel files πŸ‘‡

If the worksheet is protected,

  1. Go to the Review tab.
  2. Click Unprotect Sheet in the Protect group.
unlock the locked cells

These previously locked cells are now unlocked πŸ”“

unlocked cells

Any changes can now be made in the cells of this worksheet.

Unprotect your workbook

If you protected your workbook without a password, all it takes is one click to unprotect it.

When you go to the Review Tab, you’ll see that the Protect Workbook button is highlighted. The dark gray background indicates that the workbook is currently protected.

unprotect sheet

Click the Protect Workbook button to unprotect it.

This would mean that you and anyone can now change the structure of the workbook including adding, moving, and renaming worksheets in the workbook.

To unprotect a workbook that is password protected, the same procedure follows but you’ll now be prompted for the password.

That’s it – Now what?

Nice work! You’ve clearly seen how you can protect your data and maintain the integrity of your Excel files 😊

You know it’s better safe than sorry! So, whether you need to protect your data, formulas, worksheet, or workbook; with a password or without, Excel has you covered.

Do you know what else Excel’s got you covered? Built-in functions and formulas you actually need to make your work done fast and easily. Functions like the IF, SUMIF, and VLOOKUP.

Join my free Excel Intermediate Training to learn these functions and how to effectively clean your data in Excel. Click the link to turbocharge your Excel skills πŸš€

Other resources

There are still a lot of things you can do to your worksheets before you finally protect them.

Whether you want to learn how to create a new worksheet or group your worksheets in Excel, we got you covered.

By the way, have you ever wondered what’s the difference between Excel and Google sheets? πŸ€” Which one is better? Click this link to learn more.

Frequently asked questions

The F4 key is NOT used to lock cells if you mean lock cells to protect them in Excel. What the F4 key does is lock cell references so that you can maintain the original cell reference when you copy it. This shortcut works great when working with formulas.

To lock cells in Excel on Mac, you need to first unlock all the cells since Excel locks them by default. Then, you can go ahead and lock specific cells in your worksheet.

  1. Select the cells that you want to lock.
  2. On the Format menu, click Cells, or press COMMAND + 1.
  3. Click the Protection tab, and then make sure that the Locked check box is selected.

Once the cells are locked, turn on the protection of the sheet.

  1. Click Protect Sheet in the Review Tab.
  2. Type the Password (optional) and select checkboxes on what you only allow users to do in the worksheet.
  3. Finally, click OK.

The Freeze Pane feature in Excel allows you to lock your column or rows or both so that when you scroll down or over to view the rest of your sheet, they will remain on the screen.

To freeze panes in Excel, select the cell below the rows and to the right of the columns you want to keep visible. Then go to the View Tab, and click the Freeze Panes drop-down in the Windows group. Finally, select Freeze Panes.

For a step-by-step tutorial about Freeze Panes, click this link.