How to protect cells in Excel with password

Lock All Cells | Lock Specific Cells | Lock Formula Cells

You can lock cells in Excel if you want to protect cells from being edited.

Lock All Cells

By default, all cells are locked. However, locking cells has no effect until you protect the worksheet.

1. Select all cells.

2. Right click, and then click Format Cells (or press CTRL + 1).

3. On the Protection tab, you can verify that all cells are locked by default.

4. Click OK or Cancel.

5. Protect the sheet.

All cells are locked now. To unprotect a worksheet, right click on the worksheet tab and click Unprotect Sheet. The password for the downloadable Excel file is "easy".

Lock Specific Cells

To lock specific cells in Excel, first unlock all cells. Next, lock specific cells. Finally, protect the sheet.

1. Select all cells.

2. Right click, and then click Format Cells (or press CTRL + 1).

3. On the Protection tab, uncheck the Locked check box and click OK.

4. For example, select cell A1 and cell A2.

5. Right click, and then click Format Cells (or press CTRL + 1).

6. On the Protection tab, check the Locked check box and click OK.

Again, locking cells has no effect until you protect the worksheet.

7. Protect the sheet.

Cell A1 and cell A2 are locked now. To edit these cells, you have to unprotect the sheet. The password for the downloadable Excel file is "easy". You can still edit all other cells.

To lock all cells that contain formulas, first unlock all cells. Next, lock all formula cells. Finally, protect the sheet.

1. Select all cells.

2. Right click, and then click Format Cells (or press CTRL + 1).

3. On the Protection tab, uncheck the Locked check box and click OK.

4. On the Home tab, in the Editing group, click Find & Select.

5. Click Go To Special.

6. Select Formulas and click OK.

Excel selects all formula cells.

7. Press CTRL + 1.

8. On the Protection tab, check the Locked check box and click OK.

Note: if you also check the Hidden check box, users cannot see the formula in the formula bar when they select cell A2, B2, C2 or D2.

Again, locking cells has no effect until you protect the worksheet.

9. Protect the sheet.

All formula cells are locked now. To edit these cells, you have to unprotect the sheet. The password for the downloadable Excel file is "easy". You can still edit all other cells.

Microsoft Excel is one of the most popular and easy-to-use software. It was created by Microsoft and it allows users to organize and format data in rows and columns format. In this article, we will see how we can lock cells in Excel and protect them with a password.

Here are the steps you can follow to lock cells in a excel:

• Click on the cell(s) you wish to lock.
• To open the Format Cells pop-up window, navigate to the "Home" tab in your Excel spreadsheet.
• To do so, select "Format" from the right-hand menu.
• From the drop-down menu, select "Lock Cell."

By default, it locks all the cells in a worksheet. But you have to protect your worksheet with a password. 

• Right-click on the worksheet tab.
• Click Protect Sheet.

• Enter a password of your choice.

• Check mark the actions you want to allow the user to perform on your worksheet
• Click OK.

You have to protect your sheet with a password after locking the cell. 

If you want to lock specific cells in Excel, first you have to unlock all the cells.

• Right-click and then click format cells.
• On the Protection tab, uncheck the locked checkbox and then click OK.

• Now, select the cells you want to lock.

• Right-click and then click format cells.
• On the protection tab, check the locked checkbox and then click OK.

• Select all cells.
• Right-click and then click format cells.
• On the Protection tab, uncheck the locked checkbox and then click OK.

• On the Home tab, click find and select.
• Click Go To Special.

• Select Formulas and Click OK. Excel will select all the formulas in the sheet.

1. Go back to the Protection tab and checkmark the locked check box.

Gain expertise in the latest Business analytics tools and techniques with the Business Analyst Master's Program. Enroll now!

Conclusion

By locking cells in an Excel worksheet and protecting the worksheet, you can protect your work from unwanted tampering from other users.

Boost your analytics career with powerful new Microsoft Excel skills by taking the Business Analytics with Excel course, which includes Power BI training

This Business Analytics certification course teaches you the basic concepts of data analysis and statistics to help data-driven decision making. This training introduces you to Power BI and delves into the statistical concepts that will help you devise insights from data in order to present your findings using executive-level dashboards.

If you have any questions, please feel free to ask them in our comments section, and our experts will promptly answer them for you. 

Mary Lou wonders if there is a way she can password protect certain columns in a shared workbook. The workbook has only a single worksheet, and she needs to protect columns E and J so they cannot be changed, unless the user knows a particular password.

The traditional way to approach this challenge is to follow these steps:

1. Select all the cells in your worksheet. (Pressing Ctrl+A will do the trick.)
2. Press Ctrl+Shift+F. Excel displays the Format Cells dialog box.
3. Make sure the Protection tab is displayed. (See Figure 1.)


Figure 1. The Protection tab of the Format Cells dialog box.

4. Make sure the Locked check box is cleared. (You may need to click it a few times to get it actually cleared.) This removes protection from all the cells in the worksheet.
5. Click OK to close the Format Cells dialog box.
6. Select column E.
7. Press Ctrl+Shift+F. Excel again displays the Format Cells dialog box.
8. Again make sure the Protection tab is displayed.
9. Make sure the Locked check box is selected. This protects the cells that are currently selected.
10. Click OK to close the Format Cells dialog box.
11. Select column J and repeat steps 7 through 10.
12. Display the Review tab of the ribbon.
13. Click the Protect Sheet tool. Excel displays the Protect Sheet dialog box. (See Figure 2.)


Figure 2. The Protect Sheet dialog box.

14. Make sure the top check box (Protect Worksheet and Contents of Locked Cells) is selected.
15. Enter a password in the appropriate place.
16. Use the check boxes to fine tune how you want protection applied.
17. Click OK. You are again asked to enter the password.
18. Enter the password you used in step 15 a second time. Your worksheet is now protected.

The result of going through all these steps is that the cells in columns E and J cannot be changed. If someone knows the password you used in step 15, however, they can unprotect the worksheet (the proper control is on the Review tab of the ribbon) and make any changes they want. If someone does make changes in this way, they will need to reapply the protection (steps 12 through 17) prior to saving the workbook. If they don't, then the next time the workbook is open, the worksheet remains unprotected and anyone can change the contents of columns E and J.

As I said, the above represents the traditional way to approach the problem. There are non-traditional ways you can use, as well. For instance, you might rethink how your data is put together and, perhaps, move the contents of columns E and J to another worksheet or even to another workbook. You can then protect that information and simply reference it within the current worksheet.

A third approach is to use a tool introduced with the release of Excel 2010. This new tool, which allows you to password-protect ranges of cells, requires a variation on the steps presented earlier in this tip. The steps will be many, but it does provide for a more flexible way of dealing with the protection issue. Here is the variation that includes use of the new tool:

1. Select all the cells in your worksheet. (Pressing Ctrl+A will do the trick.)
2. Press Ctrl+Shift+F. Excel displays the Format Cells dialog box.
3. Make sure the Protection tab is displayed.
4. Make sure the Locked check box is cleared. (You may need to click it a few times to get it actually cleared.) This removes protection from all the cells in the worksheet.
5. Click OK to close the Format Cells dialog box.
6. Select column E.
7. Press Ctrl+Shift+F. Excel again displays the Format Cells dialog box.
8. Again make sure the Protection tab is displayed.
9. Make sure the Locked check box is selected. This protects the cells that are currently selected.
10. Click OK to close the Format Cells dialog box.
11. Select column J and repeat steps 7 through 10.
12. Display the Review tab of the ribbon.
13. Click Allow Users to Edit Ranges. (Click Allow Edit Ranges if you are using Excel in Office 365 or Excel 2019.) Excel displays the Allow Users to Edit Ranges dialog box. (See Figure 3.)


Figure 3. The Allow Users to Edit Ranges dialog box.

14. Click the New button. Excel displays the New Range dialog box. (See Figure 4.)


Figure 4. The New Range dialog box.

15. In the Title box, enter the name you want to use for this range. (The title isn't particularly important. Use something that is meaningful to you.)
16. In the Refers to Cells box, enter the following: =$E:$E,$J:$J
17. In the Range Password box, enter the password you want to give to those who should be able to edit columns E and J.
18. Click on OK. You are again asked to enter the password.
19. Enter the password you used in step 17 a second time. The range now appears in the Allow Users to Edit Ranges dialog box.
20. Click OK to close the Allow Users to Edit Ranges dialog box.
21. Click the Protect Sheet tool. Excel displays the Protect Sheet dialog box.
22. Make sure the top check box (Protect Worksheet and Contents of Locked Cells) is selected.
23. Enter a password in the appropriate place. This should not be the same password you used in step 17 and step 19; it should be different.
24. Use the check boxes to fine tune how you want protection applied.
25. Click OK. You are again asked to enter the password.
26. Enter the password you used in step 23 a second time. Your worksheet is now protected.

The advantage to this approach is that you have two levels of passwords—one for the range (columns E and J) and one for the worksheet as a whole. As someone is using a worksheet protected in this manner, when they try to modify a cell in columns E or J, they are asked for a password. If they supply the correct password (this is the one that you specified in steps 17 and 19), then they can edit anything they want in columns E and J. When the workbook is saved, exited, and reopened, then the protection is automatically reset and columns E and J can again only be edited if the password is known—there is no need for the user to explicitly "reprotect" the worksheet before saving. Plus, you never have to give out the password that you used to protect the entire worksheet.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (8388) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365.