Spreadsheets often contain sensitive data. If you share an unprotected spreadsheet with others, they may alter its contents. Sometimes, people may change the content of worksheets without intending to do so.
Both these scenarios will have unintended consequences. To prevent unwanted changes to a worksheet, you should be wise enough to lock it. This article will discuss five methods of protecting worksheet data in MS Excel by locking it.
5 Methods of locking worksheets in Microsoft Excel:
- Make the workbook read-only
- Lock the entire workbook
- Protect workbook structure using the Protect Workbook option
- Protect workbook structure using the Review tab
- Lock cells using the Format cells option
Method #1: Make the Workbook Read-Only
This method does not lock the workbook. Instead, it warns that the workbook is the final version and should not be edited. You should only use this method when you trust the recipient of your workbook to respect your wishes. Follow the steps below to mark your workbook as final.
Step #1: Click on File
Click on the File tab in the top menu bar.
This action will take you back to the main menu of Microsoft Excel.
Step #2: Select Info
Select the Info option from the main menu.
This action will take you to the Protect Workbook window.
Step #3: Click Protect Workbook
Click on Protect Workbook to get a drop-down menu.
Next, select Mark as Final.
This action will put a warning at the top of your spreadsheet. The action will also change the spreadsheet to view only. As a result, it will discourage people from editing the worksheet.
Method #2: Lock the Entire Workbook
This method locks the workbook completely. This method offers complete protection. When used, unauthorized persons cannot view the content of the excel sheet. Follow the steps below to lock the entire workbook.
Step #1: Click on File
Click on the File tab in the top menu bar.
This action will take you back to the main menu of Microsoft Excel.
Step #2: Select Info
Select the Info option from the main menu.
This action will take you to the Protect Workbook window.
Step #3: Click Protect Workbook
Click on Protect Workbook to get a drop-down menu.
Click on the Encrypt With Password option.
Step #4: Enter a password
Enter a password you can easily remember.
After typing the password, Microsoft Excel will ask you to re-enter the password.
Enter it again and click the OK button.
Step #5: Save the file
You will find the Save option in the main menu of the file tab.
Click on it to save the file.
This action will lock the excel file. You will need to enter your password before being able to access the file.
If you share the file with another person, they will also need the password to open it.
Method #3: Protect Workbook Structure Using the Protect Workbook Option
Using this method will prevent people from changing the structure of your worksheets. They will not be able to add, delete, or move any of your worksheets. Follow the methods below to lock the structure of your worksheets.
Step #1: Click on File
Click on the File tab in the top menu bar.
This action will take you back to the main menu of Microsoft Excel.
Step #2: Select Info
Select the Info option from the main menu.
Step #3: Click Protect Workbook
Click on Protect Workbook to get a drop-down menu.
Click on the Protect Workbook Structure option.
This action will take you back to the spreadsheet and will also show a Protect Structure and Windows pop-up window.
Step #4: Enter a password
Enter a password you can easily remember in the pop-up window.
Next, click the OK button.
After typing the password, Microsoft Excel will ask you to re-enter the password. Enter it again and click the OK button.
Method #4: Protect Workbook Structure Using the Review Tab
This method is similar to Method 3 above, but the steps are different and people will be unable to add or delete data. To lock a worksheet using the Review tab, follow the steps below.
Step #1: Go to the Review tab
Click on the Review tab from the top menu bar to change the ribbon and select the Protect Workbook option.
Step #2: Enter a password
Enter a password you can easily remember and click the OK button.
After typing the password, Microsoft Excel will ask you to re-enter the password. Enter it again and click the OK button.
This action will prevent anyone from moving your worksheets. They will also be unable to delete or add data to them.
Method #5: Lock Cells Using the Format Cells Option
This method works best when you want to use a spreadsheet to collect data from people. The recipient of your worksheet will be able to enter data into the unlocked cells. However, they cannot change the rest of the worksheet. Follow the steps below to lock cells in Microsoft Excel.
Step #1: Select the entire sheet
Click the small triangle in the top left corner of the spreadsheet sheet. This action will highlight (select) the entire spreadsheet.
Step #2: Format cells
Right-click anywhere on the highlighted sheet.
A drop-down menu will appear.
Next, click Format Cells… from the drop-down menu.
This action will open the Format Cells window.
Step #3: Lock all cells
Click on the Protection tab in this Format Cells window.
Next, tick the Locked box.
Finally, click the OK button.
Step #4: Select cells to be unlocked
Click, hold, and drag the cursor over the cells you do not want to lock.
Next, right-click these selected cells, and you will get a drop-down menu.
From the drop-down menu, select Format Cells….
Step #5: Unlock the selected cells.
Click on the Protection tab in the Format Cells window.
Next, uncheck the Locked box.
Finally, click OK.
Step #6: Activate the rules
Click on the Review tab in the main menu bar.
Next, select the Protect Sheet icon. You will get a pop-up window.
Enter a password to protect your sheet. Also, ensure to check the Select locked cells and Select unlocked cells options.You will be asked to re-enter the password. Finally, click OK.
Conclusion
By using the above methods, you can lock your worksheet in various ways. The methods will give the recipients of your worksheets different amounts of freedom.
Note: Adding passwords to company documents puts you in a very responsible position. Make sure to record your passwords in a safe place, which can be accessed by a supervisor in case you get sick.