Spreadsheets often contain important data, so it could be crucial to lock the document before sharing to make sure that it’s not misused. However, there are times when you may want the end user to enter some data in the shared file. Luckily, Excel has built-in features that allows us to fine tune workbook protection just the way we want it.
In this post, we’ll be covering the following options:
- Allow Data Entry Only in Specific Cells in Excel
- Lock and Hide Formulas in Excel
- Protect Structure of an Excel Worksheet
- Mark Excel Workbook as Final
- Password Protect an Entire Excel Workbook File
How to Allow Data Entry Only in Specific Cells in Excel
We’ll start off by learning how to allow editing of specific cells in Excel, while all other cells remain locked and protected. This would also protect formatting of the cells you select while the users can add/edit content in the them.
Steps:
1. With the workbook open, select the cells you want to be editable.
2. Press Ctrl + 1 to open “Format Cells” dialog.
3. Switch to “Protection” tab in the dialog > Uncheck the “Locked” box, and then click the “OK” button.
4. Now go to “Review” tab > “Protect Sheet.”
5. Type-in a password to protect the sheet > Click “OK.” Password is optional.
That’s it. The worksheet is now protected, but the end user can still make modifications in the cells you have allowed them.
The pop-up dialog we saw in Step 3, allows even further fine-tuning of cell protection. For example, you can allow users to format cells without modifying the original content, or perhaps you can allow end users to insert additional rows and columns but not remove the original cells. These options can be achieved by customizing the selection in “Protect Sheet” dialog.
To unprotect a locked sheet, simply go to Review tab and click on “Unprotect Sheet.” If you had locked with a password, re-enter the password as well to remove the protection.
How to Lock and Hide Formulas in Excel
While sharing a spreadsheet, if you do not wish the end user to be able to view or edit the formulas used, you can hide them from appearing in the Formula Bar. Let’s look at how to achieve this.
Steps:
1. With the workbook open, select the cells containing the formula.
2. Press Ctrl+1 to open Format Cells dialog.
3. Switch to Protection tab in the dialog > Check the “Locked” and “Hidden” box, and then click the OK button.
4. Now go to Review tab > Protect Sheet.
5. Type-in a password to protect the sheet > Click OK.
You can allow users to enter data in other cells following the previous instructions.
How to Protect Structure of an Excel Worksheet
Now, let’s look at how we can protect the structure of an Excel workbook. With this enabled, the end user will not be able to add, delete, hide, unhide, or re-arrange sheets inside of the workbook. Follow these steps to enable “Protect Structure and Windows” protection:
Steps:
1. Open the Workbook you wish to protect.
2. Navigate to Review tab > Click on Protect Workbook.
3. Enter a password > Check the box for “Structure” > Click OK.
That’s it. The document is now protected.
To unprotect the workbook, simply click on the Protect Workbook button again and input the password.
How to Mark Excel Workbook as Final
The “Mark as Final” feature allows you to mark an Excel file as the final version. When this is enabled, the file switches to read-only mode, and the end user will have to re-enable editing if they wish to make edits. On opening the file, a soft warning is displayed at the top of the file as seen in the image above.
Steps:
1. Open the Workbook you wish to finalize.
2. Navigate to File tab > Choose Info tab.
3. Click on the Protect Workbook dropdown and choose Mark as Final.
4. You will get a prompt now > Confirm it and save the file.
How to Password Protect an Entire Excel Workbook File
Now, finally let’s look at how to protect an entire spreadsheet with a password to prevent others from opening it.
Steps:
1. Open the Workbook you wish to protect.
2. Navigate to File tab > Choose Info tab.
3. Click on the Protect Workbook dropdown and choose Encrypt with Password.
4. Type-in a password > Re-confirm it and click OK.
That’s all. Hope these tips help you in becoming an advanced Excel user and in protecting your data. 🙂