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!
Table of Contents
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.
Don’t worry, you can do this in five easy steps 🖐
- Select all the cells in your worksheet.
- Right-click and select Format Cells.
- Click the Protection Tab in the Format cells window.
- Uncheck the Locked check box and the Hidden check box.
- Finally, click OK.
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.
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.
- Click the Find & Select button in the Editing group of the Home tab.
- Select Go To Special.
- Select Formulas in the “Go To Special” window.
- Click OK.
This highlights the formula cells in the worksheet.
To ensure that these cells can’t be changed or modified, it’s time to lock them 🔒
- Right-click the highlighted cells.
- Click Format Cells.
When the Format Cells dialog box pops up…
- Go to the Protection Tab.
- Check the Locked check box.
- Click OK.
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…
- Right-click the sheet you want to protect in the worksheet tab.
- Click Protect Sheet button.
- 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.
- Click OK.
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.
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.
- Go to the Review Tab.
- In the Protect group, click Protect Workbook.
Now a “Protect Structure and Windows” dialog box appears.
- Enter a password. By the way, this is optional. For now, let’s leave it blank.
- Check the box next to Structure.
- Click OK.
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.
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.
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!
- With the file open, go to the File tab.
- Click the Info button on the left sidebar.
- Click the drop-down arrow of the Protect Workbook button.
- Select Encrypt with Password.
- On the Encrypt Document dialog box, type in a password. For example, let’s set the password 12345.
- Click OK.
- Re-enter the password to confirm.
- Finally, click OK.
When you save the file and anyone attempts to open it, they will be prompted for a password. Like this 👇
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.
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,
- Go to the Review tab.
- Click Unprotect Sheet in the Protect group.
These previously locked cells are now unlocked 🔓
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.
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 🚀
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.