One of the lesser-known aspects of adding passwords to workbooks is the ability to add a password that places the workbook in read-only mode. Note that this level of password does not encrypt the workbook, and you may use this feature independently of the encrypting password feature. You may also use this feature in conjunction with the encrypting password feature to create two levels of access to a workbook – those users who know the encrypting password would have full access to the workbook, while those users who know the read-only password would only be able to view the workbook.
To add a password to a workbook so that the workbook opens in read-only mode, perform the following three steps.
1.When saving the workbook, in the Save As dialog box, choose Tools, followed by General Options as shown in Figure 1.
Figure 1 - Selecting General Options in the Save as Dialog Box
2.In the General Options dialog box shown in Figure 2, enter your desired password in the Password to modify box.
Figure 2 - Adding a Password Required to Modify the Workbook
3.When prompted, confirm the password and click OK to complete the process.
After you enter this password, Excel presents any user who opens the workbook with the Password dialog box pictured in Figure 3. Upon entering the correct Password to modify in the Password box, the workbook opens with full editing privileges. If the user clicks the Read Only button, the workbook opens but in read-only mode.
Figure 3 - Opening a Workbook with a Password to Modify
If you enable both password options – the encrypting password and the read-only password – when you attempt to open the workbook, Excel will prompt you to enter the encrypting password. If you successfully enter the encrypting password, then Excel will prompt you for the read-only password. Keep in mind that these two passwords can, and likely should, be different. If they are different, then those users who know the encrypting password will have only the ability to open the workbook but not make edits. Those users who know both passwords will have both opening and editing privileges.
Returning the focus to Figure 2, notice the presence of two additional options in the General Options dialog box: Always create backup and Read-only recommended. If you check the box next to Always create backup, Excel will create a backup of your workbook every time you save the file. This backup copy will have the same file name as the original workbook, and Excel will store the backup copy in the same folder as the original workbook. However, Excel saves the backup copy with a .xlk file extension. This backup is in addition to any copies of the file that Excel might create as part of its AutoRecover feature. Additionally, if you check the box next to Read-only recommended, Excel will prompt all users to open the workbook in read-only mode, regardless of whether an encryption or a read-only password exists.
Excel offers a myriad of options for securing a workbook and its’ contents. If you want to allow all users to open a workbook, but also desire to control who can edit the workbook, then consider this method of adding a password to make the workbook read-only to those users who do not know the password.
For a video demonstration of this tip, please visit www.tinyurl.com/k2tips169.