Microsoft Excel is a popular data storage and process tool with its use ranging from personal, business to organizational. There are many reasons why we would like to protect data including version control, sharing a uniform master copy, provide controlled access, and prevent unintended or malicious modification. Fortunately, Excel offers three different protection level to help control data integrity. This allows lock data from accessing or editing using a password.
Protecting Your Excel Data
In this article, we will discuss these three protection levels in Microsoft Excel and how to open protected excel data.
- Protect your workbook with password
- Password protect your sheets
- Protect cells in a sheet
1. How to Password Protect Excel Workbook?
An excel workbook refers to a collection of more than one spreadsheets, put together in a single file.
- To protect a workbook using a password, open excel workbook and add your data.
- Navigate through “File” to “Info” section. On the right side, select the “Protect Workbook” box, the one with a lock and key icon.
- Choose “Encrypt with Password” from the drop-down list.
- This will lead you to a password text box. Type the password and click “Ok”.
- Re-type the password to confirm and again click the “Ok” button.
- This will highlight the “Protect Workbook” option and display a message that “A password is required…”.
- Another method of protecting a workbook in excel is to save the file with a password.
- From the “File” menu, select the “Save As” option then select the location where you want to save the file.
- Go to “Tools” arrow, next to the “Save” and choose “General Options”.
- You will be prompted to two passwords to open and to modify. Type the passwords and click “Ok”.
- Confirm the two passwords and clicking “Ok” each time.
- Click on “Save”.
- To open the protected workbook, you must have the correct password.
- Double click on the excel file, enter the password when prompted and then click the “Ok” button.
Workbook password protection is ideal, if you want to control the user full-access rights. You can share the workbook and provide the password only to the intended users. For more unauthorized access control, it is advisable not to send the password together with the spreadsheet.
2. Password Protecting an Excel Worksheet
A worksheet in Excel is like a page of the workbook shown by a tab at the bottom of the spreadsheet document.
- To protect a worksheet, Go to the “Review” tab in the main menu and click the “Protect Sheet” from the toolbar or right-click its tab at the bottom of the screen to select the protection option.
- You may allow users to perform some tasks on the dialog box that appears by ticking the boxes against the various access rights.
- Enter a password to unprotect the sheet and click “Ok”.
- Re-enter the password upon prompt, and click “Ok” to confirm.
- If you try to modify any cell on the worksheet, you will get an error message.
- To unprotect the worksheet, go back to “Review” and click on “Unprotect Worksheet” then enter the password and click “Ok” on the prompt dialogue.
Worksheet password protection allows all users to read the data on the spreadsheet, but limits any modification or editing to users with the password. However, you can allow all users to select the cells, sort or filter the data among other things.
3. Protect Excel Sheet Cells
You can further narrow down the protection to the cell level. This prevents modification on the protected cells, but users can edit other parts of the spreadsheet.
- First, highlight all the whole spreadsheet by pressing “Ctrl + A” keys on the keyboard. Right-click on any cell and select “Format Cells” option from the context menu.
- Go to the “Protection” tab, disable the “Locked” box, and then click “Ok”.
- Now select the cell(s) you want to protect, go back to format cells and under “Protection”, tick the “Locked” selection to lock the cell(s) and click “Ok”.
- To protect the cells with a password, you need to lock the worksheet. Repeat the steps in method 2 above. Ensure that the “Select locked cells” option is not ticked.
- You can try to see the difference when you try to modify the protected cells and the unprotected sections.
- To remove the cell protection, click “Unprotect Sheet”, provide the password and click “Ok”.
Related: How to compare two Excel workbooks?
You can control access and modification of data in spreadsheets by protecting either the whole workbook, a worksheet or particular cells using a password. This prevents unauthorized users from viewing the data or changing the data. It is also a great way of maintaining data integrity, as well as preventing accidental editing.