Prevent Excel workbooks from opening
To prevent third parties from opening your Excel workbook and seeing sensitive content there, such as a project cost overrun, you should protect the workbook with a password. In this way, you deny other users any access to a workbook. You can use strings of letters, numbers and special characters as passwords in Excel. Just try the code you want. If something is wrong with your password, Excel will immediately alert you to the related errors.
To set up the security mechanism, when saving the workbook, select Save As and in the window of the same name click the Tools button and then the General Options command. In the following dialog, enter a password in the Password to open field. Confirm the secret sequence of digits by clicking on OK. You will be prompted to repeat the password. After typing the secret string again, click OK. Then continue the saving process as usual.
The next time you open the workbook, you will be prompted for the password you used earlier. If you don’t know the password, you won’t be able to open the Excel file later, let alone edit it.
Prevent changing content
If you want third parties to be able to see the contents of a workbook with project data, but want to be sure that they cannot change its contents, proceed as with protection for opening the workbook. Use the Change password field instead of the Open password field. The further procedure does not differ from the previously presented protection variant: You will also be asked to repeat the password here.
You can also use both of the protection variants presented, namely using a password to open and an additional password to change at the same time. This always makes sense if you want to give different permissions to different people.
Analogous procedure: Enter your secret character string in the Change password field of the General options window and confirm it.
Open protected Excel workbook
When you open a protected workbook, you will automatically get the Password window in which to type your secret string. Enter the password and click OK. If you then save and close the file, Excel will retain the password. That means you don’t have to enter the password again.
If you have both an open password and a modify password, users who want to work with the file must know and enter both character codes. Excel asks for the passwords in two consecutive dialogs.
When opening a protected file, you must enter the password and confirm with OK.
Protect workbook structure
When you protect the structure of an Excel workbook, you prevent sheets contained in the workbook from being renamed, moved, deleted, hidden, and/or unhidden. In addition, it is not possible to insert new spreadsheets. This means that although changes can be made to the individual tables, for example entries can be entered, changed or removed, fundamental interventions in the file structure are not possible. This protection variant makes sense, for example, if you plan certain resources for a project and want to prevent colleagues or employees from setting up additional tables for resource planning or deleting them.
For the protection to take effect, activate the Review tab and click on the Protect workbook button in the Changes area. Check the Structure box. You can use a password at this point, but it is not absolutely necessary. If you work without a password, you or an unauthorized person can remove protection by opening the Protect workbook window and unchecking the Structure check box.
If you are concerned that third parties may ignore the protections and remove them without your consent, you should use a password. Enter the secret string in the field provided. Exit the window with the OK button. Here, too, you will be prompted to repeat the password. After you have done this and confirmed the password, the protection takes effect.
On the Review tab, click the Protect Workbook button/Structure check box and set a password to protect the structure of your Excel file.
Protect Excel spreadsheets
In addition to workbook protection, Excel offers the option of protecting tables or cells. To protect the entire contents of a table from being changed, work with sheet protection. This makes sense, for example, if you have worked out a project schedule in a table and you want to be sure that no changes are made to it. Activate the Review tab and click on the Protect sheet button in the Changes area. You can define various exceptions, for example allow formatting or sorting processes. Table protection also works with and without a password.
If you do not set a password, sheet protection can be switched off with a simple click on the Unprotect sheet button. This protection offers correspondingly little security. If you want to be on the safe side, enter a password in the field provided, repeat and confirm it. After clicking on the Unprotect sheet button, you will be asked for the secret character string.
You set up sheet protection using the Protect sheet button on the Review tab.
Define exceptions to cell protection
By protecting individual cells in a worksheet, you prevent individual content such as formulas from being overwritten. This means: Only the input fields are unlocked. All other cells automatically have the status locked and are therefore protected. Mark the cells or cell ranges for which cell protection should not apply. On the Start menu, click the small arrow in the lower-right corner of the Number area. Excel brings up the Format Cells window. There activate the Protection tab and uncheck the Blocked box. Exit the window by clicking the OK button. The cell protection for the non-released cells only works in combination with the sheet protection. As already described, set this up by clicking on the Protect sheet button in the Review menu.
For individual cells or ranges of cells, remove cell protection in the Format Cells window by clearing the Locked check box on the Protection tab.
Protect VBA code
If you work with macros, protect the associated VBA code in the VBA editor. You can reach this via the Developer tools menu. In the Code pane, click the Visual Basic button. Open the context menu of the desired VBA project by clicking with the right mouse button. Choose the VBA Project Properties command. In the window that opens, activate the Protection tab. Place a check mark in the Lock project for viewing check box. Enter your secret string in the Password field. Just below the Password field is another field where you re-enter the secret string. Confirm the password with OK. If you later want to view or change the VBA code, you will be prompted for a password.
You protect VBA code in the VBA editor using the command sequence Properties of VBA project/Protection/Lock project for display and a secret string.
