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