Protect Workbook in Excel (6 Ways)

In this article, you will learn how to protect a workbook in Excel. Throughout this article, we will discuss a total of 6 effective ways to protect a workbook in Excel. You can protect the whole workbook or specific cells using a password or without a password in Excel.

Sometimes, while working with Microsoft Excel, we must protect our dataset for the convenience of our work. For a confidential workbook, it must be password-protected so that no one can change it and it remains intact. Excel provides several simple methods for performing this. Depending on our needs, we can set various types of file protection. We hope you find this article informative and useful. So, let’s get into the main discussion.

Note: We used Microsoft 365 to prepare this article, but the operations apply to all versions.

Download Practice Workbook

You can download the practice workbook from here:

The password for the file is- 12345.

Protecting Workbook.xlsm

What Does It Mean to Protect a Workbook in Excel?

The “Protect Workbook” feature in Excel prevents unauthorized modifications to the entire workbook. It protects not individual cells or worksheets of a workbook, but its structure and windows. By protecting the workbook, we can specify different editing restrictions.

How to Protect Workbook in Excel? (6 Effective Ways)

Excel has several ways to protect a workbook easily. We can set different kinds of protection in a file according to our requirements. In this article, we will discuss 6 effective ways to protect an Excel workbook.

1. Protect Excel Workbook by Using Mark as Final Option

We can protect an Excel worksheet from alteration by identifying a version as a final copy. So, when someone opens the Excel file, they will get a warning message saying that it’s the final version and they shouldn’t make any changes to it. Follow the below steps:

First, open the desired Excel file to “Mark As Final”.
Then, go to File>> Info>> Protect Workbook>> Mark as Final.

Then, a small dialog box will appear. Just hit OK.

As a result, Excel will automatically mark the Excel workbook as a final version.
Excel will display the following warning box if someone else attempts to open the workbook.
Just hit OK to proceed.

If you want to continue editing the worksheet, just click Edit Anyway.
Excel will then allow you to edit the workbook.

Thus we can protect an Excel workbook by using the “Mark as Final” option.

2. Protect Excel Workbook with a Password

In this method, we will protect our Excel workbook with a password so that others will not be able to access the data unless we provide them with the password. To do so follow the below steps:

First, go to File>> Info>> Protect Workbook>> Encrypt with Password.

Now, insert a password in the Encrypt Document dialog box and click OK.

Then, re-enter the password in the Confirm Password dialog box and click OK.

Try opening your workbook again to see if the method worked.
A password prompt should automatically appear in Excel to enter the password to access data.
Enter password 12345 and click OK.

Thus, we can protect our Excel workbook with a password.

3. Use ‘Open as Read-Only’ Feature to Protect Workbook

We can mark our Excel workbook as a Read-Only file. Thus, when someone else attempts to open the Excel file, it will primarily protect the workbook from editing.

First, go to File>> Info>> Protect Workbook>> Always Open Read-Only.

This will shift the Excel workbook to Read-Only mode and if someone else will try to open the Excel file, it will show the following dialog box.
Click Yes to proceed with the Read-Only mode whereas selecting No will enable editing the workbook.

Thus we can use the ‘Open as Read-Only’ feature to protect our workbook in Excel.

4. Use General Options to Protect Excel File with Password

There is another option in Excel by which we can set passwords to open a file and also to modify a file.

Click on the File tab.

Next, go to Save As>> Browse.

As a result, a dialog box named Save As will open up.
Now, click on Tools>> select General Options from the drop-down.

Now set a password to open the file.
After that, set another password to modify the file. We have entered 12345 in both fields.
Next, click OK.

Then, re-enter the password to proceed and click OK.

Now, re-type the password to modify the file and click OK.

Now if we close the workbook and open it again, it will ask for the password.
Give the password to open the file.

Next, it will ask for the password to modify.
Give the password and click OK.
Thus we can use general options to protect our Excel file with a password.

5. Protect Structure of an Excel Workbook

We can restrict others from deleting current sheets or adding new ones by protecting the structure of our workbook. To know how to protect the structure of an Excel workbook follow the below steps:

First, go to File>> Info>> Protect Workbook>> Protect Workbook Structure.

Now, enter the password in Protect Structure and Windows dialog box and click OK.

Re-enter the password to confirm and proceed.

To verify, double-click on the name of the sheet you want to rename, and Excel will prompt you with the message below.

This is how we can protect the structure of our Excel workbook. By applying this method, anyone can open the Excel file, but cannot add a sheet, change the name, or delete any existing sheets.

6. VBA Code to Protect Excel Workbook with Password

In this method, we will protect the Excel workbook with a password using a VBA code. To know the procedures go through the following steps:

First, press ALT + F11 on your keyboard to open Visual Basic.
Then click Insert and select Module.
Insert the following code in the code editor and press F5 to run the entire code.

If you open the file again after running the VBA code successfully, you will need to enter the password.

Thus, we can use VBA code to protect Excel the workbook with a password.

How to Protect Specific Cells in an Excel Workbook

Suppose, we want to protect some specific cells. To do that, we need to protect the sheet. Follow the below steps to protect an Excel sheet from editing and copying specific cells:

First, select the cell range of the specific worksheet you want to protect.
In our case, we select column B of the SalesRecordOf2021 worksheet.
Then right-click on your mouse and select Format Cells.

Now go to Protection>> check the box next to the Locked option>> then click OK.

Now, go to the Review tab>> click on Protect Sheet.
Enter the password and enable “Protect worksheet and contents of locked cells”.
Click OK.

Re-enter the password to confirm and click OK.

As a result, if you want to make any changes in column B, Excel will show you the warning message like in the image below.

Thus you can protect specific cells in a workbook.

What Are the Benefits of Protecting Workbook in Excel?

Protecting a workbook in Excel offers several benefits, including:

Data Security: By protecting a workbook, we can ensure the privacy and accuracy of your data. Unauthorized users will not be able to access or modify the contents of worksheets or workbook elements that are protected.
Preventing Unintentional Changes: Workbook protection prevents accidental modifications to important formulas, data, and formatting. This is especially helpful when sharing the workbook with others, as it ensures the structure and functionality remain intact.
Presentation Purposes: Protecting a workbook is useful when we want to create a read-only version for presentations or demonstrations. It ensures that the data and formatting remain unchanged, preventing accidental modifications during the presentation.

What Are the Things You Need to Remember?

It is essential to keep a backup copy of the unprotected workbook in case we forget the password or lose the original workbook. Workbook protection should be used carefully, and we should keep a backup copy on hand at all times.

Key Takeaways from the Article

In this article, we have discussed a total of 6 effective ways to protect a workbook.
First, we have discussed what it means to protect a workbook.
We have also explained how to protect Excel workbooks using VBA macro.
Showed a step-by-step procedure of all methods.
Also showed how to protect specific cells in a workbook.
Discussed some benefits of protecting a workbook.
Mentioned a few points that should be remembered regarding protecting a workbook in Excel.
Provide solutions to frequently asked questions by readers.

Conclusion

This article covers every possible way to protect a workbook in Excel with some easy ways. Don’t forget to download the Practice file. We hope this was helpful. Please let us know in the comment section if you have any queries or suggestions.

Frequently Asked Questions

1. What is the difference between Protect Workbook in Excel and Protect Sheet?

Answer: The main difference between “Protect Workbook” and “Protect Sheet” in Excel lies in the scope of protection and the level of control over the workbook’s elements. Workbook protection focuses on protecting the overall structure and windows of the entire workbook, while worksheet protection is designed to protect individual worksheets within the workbook.

2. What happens when you protect a workbook?

Answer: When we protect the workbook, we can specify different editing restrictions. It protects not individual cells or worksheets of a workbook, but its structure and windows. For example, we can allow users to select and format cells but restrict them from making changes to the workbook’s structure, such as adding or deleting worksheets.

3. What are the 3 types of protection in Excel?

Answer: A workbook can be protected at the file or worksheet level in Excel. Excel provides three levels of password protection: password protection for opening files, password protection for changing data, and password protection for adding, deleting, or hiding worksheets.

Protect Workbook in Excel: Knowledge Hub

<< Go Back to Excel Protect | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!