Chapter 1
In This Chapter
Assigning a password for opening a workbook
Assigning a password for making editing changes in a workbook
Using the Locked and Hidden protection formats
Protecting a worksheet and selecting what actions are allowed
Enabling cell range editing by particular users in a protected sheet
Protecting the structure of a workbook
Protecting and sharing a workbook
Before you start sending out your spreadsheets for review (especially out of house), you need to make them secure. Security in Excel exists on two levels. The first is protecting the workbook file so that only people entrusted with the password can open the file to view, print, or edit the data. The second is protecting the worksheets in a workbook from unwarranted changes so that only people entrusted with that password can make modifications to its contents and design.
When it comes to securing the integrity of your spreadsheets, you can decide which aspects of the sheets in the workbook your users can and cannot change. For example, you might prevent changes to all formulas and headings in a spreadsheet, while still enabling users to make entries in the cells referenced in the formulas themselves.
By password-protecting the workbook, you can prevent unauthorized users from opening the workbook and/or editing the workbook. You set a password for opening the workbook file when you’re dealing with a spreadsheet whose data is of a sufficiently sensitive nature that only a certain group of people in the company should have access to it (such as spreadsheets dealing with personal information and salaries). Of course, after you set the password required in order to open the workbook, you must supply this password to those people who need access in order to make it possible for them to open the workbook file.
You set a password for modifying the workbook when you’re dealing with a spreadsheet whose data needs to be viewed and printed by different users, none of whom are authorized to make changes to any of the entries. For example, you might assign a password for modifying a workbook before distributing it companywide, after the workbook’s been through a complete editing and review cycle and all the suggested changes have been merged. (See Book IV, Chapter 3 for details.)
If you’re dealing with a spreadsheet whose data is of a sensitive nature and should not be modified by anyone who’s not authorized to open it, you need to set both a password for opening and a password for modifying the workbook file. You assign either one or both of these types of passwords to a workbook file at the time you save it with the File ⇒ Save As command (Alt+FA).
When you choose this command (or click the Save button on the Quick Access toolbar or press Ctrl+S for a new file that’s never been saved before), Excel opens the Save As screen where you select the place where you want to save the file. (See Book II, Chapter 1 for details.) After you select the place to save the file on the Start screen, Excel opens the Save As dialog box where you can then set the password to open and/or the password to modify the file by taking these steps:
Click the Tools button in the Save As dialog box and then choose General Options from its drop-down menu.
Doing this opens the General Options dialog box, similar to the one shown in Figure 1-1, where you can enter a password to open and/or a password to modify in the File Sharing section. Your password can be as long as 255 characters, consisting of a combination of letters and numbers with spaces. When adding letters to your passwords, keep in mind that these passwords are case-sensitive. This means that opensesame and OpenSesame are not the same password because of the different use of upper- and lowercase letters.
When entering a password, make sure that you don’t enter something that you can’t easily reproduce or, for heaven’s sake, that you can’t remember. You must be able to immediately reproduce the password in order to assign it, and you must be able to reproduce it later if you want to be able to open or change the darned workbook ever again.
(Optional) If you want to assign a password to open the file, type the password (up to 255 characters maximum) in the Password to Open text box.
As you type the password, Excel masks the actual characters you type by rendering them as dots in the text box.
If you decide to assign a password for opening and modifying the workbook at the same time, proceed to Step 3. Otherwise, skip to Step 4.
When entering the password for modifying the workbook, you want to assign a password that’s different from the one you just assigned for opening the file (if you did assign a password for opening the file in this step).
(Optional) If you want to assign a password for modifying the workbook, click the Password to Modify text box and then type the password for modifying the workbook there.
Before you can assign a password to open the file and/or to modify the file, you must confirm the password by reproducing it in a Confirm Password dialog box exactly as you originally entered it.
Click the OK button.
Doing this closes the General Options dialog box and opens a Confirm Password dialog box, where you need to exactly reproduce the password. If you just entered a password in the Password to Open text box, you need to reenter this password in the Confirm Password dialog box. If you just entered a password in the Password to Modify text box, you need only to reproduce this password in the Confirm Password dialog box.
However, if you entered a password in both the Password to Open text box and the Password to Modify text box, you must reproduce both passwords. In the first Confirm Password dialog box, enter the password you entered in the Password to Open text box. Immediately after you click OK in the first Confirm Password dialog box, the second Confirm Password dialog box appears, where you reproduce the password you entered in the Password to Modify text box.
Type the password exactly as you entered it in the Password to Open text box (or Password to Modify text box, if you didn’t use the Password to Open text box) and then click OK.
If your password does not match exactly (in both characters and case) the one you originally entered, Excel displays an alert dialog box, indicating that the confirmation password is not identical. When you click OK in this alert dialog box, Excel returns you to the original General Options dialog box, where you can do one of two things:
If you assigned both a password to open the workbook and one to modify it, Excel displays a second Confirm Password dialog box as soon as you click OK in the first one and successfully reproduce the password to open the file. You then repeat Step 5, this time exactly reproducing the password to modify the workbook before you click OK.
When you finish confirming the original password(s), you are ready to save the workbook in the Save As dialog box.
Click the Save button to save the workbook with the password to open and/or password to modify.
As soon as you do this, Excel saves the file if this is the first time you’ve saved it. If not, the program displays an alert dialog box indicating that the file you’re saving already exists and asking you whether you want to replace the existing file.
Instead of assigning the password to open your workbook at the time you save changes to it, you can do this as well from Excel 2016’s Info screen in the Backstage view by following these simple steps:
Choose File ⇒ Info or press Alt+FI.
Excel opens the Info screen.
Click the Protect Workbook button to open its drop-down menu and then choose Encrypt with Password.
Excel opens the Encrypt Document dialog box.
Type the password exactly as you entered it in the Password text box and then select OK.
Excel opens the Confirm Password dialog box.
Type the password in the Reenter Password text box exactly as you typed it into the Password text box in the Encrypt Document dialog box and then select OK.
Note that if you don’t replicate the password exactly, Excel displays an alert dialog box indicating that the confirmation password is not identical. After you click OK to close this alert dialog box, you’re returned to the Confirm Password dialog box.
After successfully replicating the password, Excel closes the Confirm Password dialog box and returns you to the Info screen, where “A password is required to open this workbook” status message now appears under the Protect Workbook heading.
Click the Save option on the Info screen.
Excel closes the Backstage and returns you to the regular worksheet window as the program saves your new password to open as part of the workbook file.
After you save a workbook file to which you’ve assigned a password for opening it, you must thereafter be able to faithfully reproduce the password in order to open the file (at least until you change or delete the password). When you next try to open the workbook, Excel opens a Password dialog box like the one shown in Figure 1-2, where you must enter the password exactly as it was assigned to the file.
If you mess up and type the wrong password, Excel displays an alert dialog box letting you know that the password you entered is incorrect. When you click OK to clear the alert, you are returned to the original Excel window where you must repeat the entire file-opening procedure (hoping that this time you’re able to enter the correct password). When you supply the correct password, Excel immediately opens the workbook for viewing and printing (and editing as well, unless you’ve also assigned a password for modifying the file). If you’re unable to successfully reproduce the password, you are unable to open the file and put it to any use!
If you’ve protected your workbook from modifications with the Password to Modify option in the General Options dialog box, as soon as you attempt to open the workbook (and have entered the password to open the file, if one has been assigned), Excel immediately displays the Password dialog box where you must accurately reproduce the password assigned for modifying the file or select the Read Only button to open it as a read-only file.
As when supplying the password to open a protected file, if you type the wrong password, Excel displays the alert dialog box letting you know that the password you entered is incorrect. When you click OK to clear the alert, you are returned to the Password dialog box, where you can try reentering the password in the Password text box.
When you supply the correct password, Excel immediately closes the Password dialog box, and you are free to edit the workbook in any way you wish (unless certain cell ranges or worksheets are protected). If you’re unable to successfully reproduce the password, you can click the Read Only command button, which opens a copy of the workbook file into which you can’t save your changes unless you use the File ⇒ Save As command and then rename the workbook and/or locate the copy in a different folder.
When you click the Read Only button, Excel opens the file with a [Read-Only] indicator appended to the filename as it appears on the Excel title bar. If you then try to save changes with the Save button on the Quick Access toolbar or File ⇒ Save command, the program displays an alert dialog box, indicating that the file is read-only and that you must save a copy by renaming the file in the Save As dialog box. As soon as you click OK to clear the alert dialog box, Excel displays the Save As dialog box, where you can save the copy under a new filename and/or location. Note that the program automatically removes the password for modifying from the copy so that you can modify its contents any way you like.
Before you can change or delete a password for opening a workbook, you must first be able to supply the current password you want to change to get the darned thing open. Assuming you can do this, all you have to do to change or get rid of the password is open the Info screen in the Backstage view (Alt+FI) and then choose the Encrypt with Password option from the Protect Workbook button’s drop-down menu.
Excel opens the Encrypt Document dialog box with your password in the Password text box masked by asterisks. To then delete the password, simply remove all the asterisks from this text box before you click OK.
To reassign the password, replace the current password with the new one you want to assign by typing it over the original one. Then, when you click OK in the Encrypt Document dialog box, reenter the new password in the Confirm Password dialog box and then click its OK button. Finally, after closing the Encrypt Document dialog box, you simply click the Save option on the File menu in the Backstage view to save your changes and return to the regular worksheet window.
After you have the worksheet the way you want it, you often need the help of Excel’s Protection feature to keep it that way. Nothing’s worse than having an inexperienced data entry operator doing major damage to the formulas and functions that you’ve worked so hard to build and validate. To keep the formulas and standard text in a spreadsheet safe from any unwarranted changes, you need to protect the worksheet.
Before you start using the Protect Sheet and Protect Workbook command buttons on the Review tab of the Ribbon, you need to understand how protection works in Excel. All cells in the workbook are either locked or unlocked for editing and hidden or unhidden for viewing.
Whenever you begin a new spreadsheet, all the cells in the workbook have locked as their editing status and unhidden as their display status. However, this default editing and display status in and of itself does nothing until you turn on protection with the Protect Sheet and Protect Workbook command buttons on the Review tab. At that time, you are then prevented from making any editing changes to all cells with a locked status and from viewing the contents of all cells on the Formula bar when they contain the cell cursor with a hidden status.
What this means in practice is that, prior to turning on worksheet protection, you go through the spreadsheet removing the Locked Protection format from all the cell ranges where you or your users need to be able to do data entry and editing even when the worksheet is protected. You also assign the Hidden Protection format to all cell ranges in the spreadsheet where you don’t want the contents of the cell to be displayed when protection is turned on in the worksheet. Then, when that formatting is done, you activate protection for all the remaining Locked cells and block the Formula bar display for all the Hidden cells in the sheet.
To change the status of cells from locked to unlocked or from unhidden to hidden, you use the Locked and Hidden check boxes found on the Protection tab of the Format Cells dialog box (Ctrl+1).
To remove the Locked protection status from a cell range or nonadjacent selection, you follow these two steps:
Select the range or ranges to be unlocked.
To select multiple ranges to create a nonadjacent cell selection, hold down the Ctrl key as you drag through each range.
Excel lets you know that the cells that contain formulas in the selected range are no longer locked by adding tiny green triangles to the upper-left corner of each cell in the range that, when clicked, display an alert drop-down button whose tool tip reads, “This cell contains a formula and is not locked to protect it from being changed inadvertently.” When you click this alert button, a drop-down menu with Lock Cell as one of its menu items appears. Note that as soon as you turn on protection in the sheet, these indicators disappear.
You can also change the protection status of a selected range of cells with the Locked check box on the Protection tab of the Format Cells dialog box. Simply open the Format Cells dialog box (Ctrl+1), click the Protection tab, and then click the Locked check box to remove the check mark before you click OK.
To hide the display of the contents of the cells in the current selection, you click the Hidden check box instead of the Locked check box on the Protection tab of the Format Cells dialog box before you click OK.
When you’ve gotten all cell ranges that you want unlocked and hidden correctly formatted in the worksheet, you’re ready to turn on protection. To do this, you click the Protect Sheet command button on the Ribbon’s Review tab or press Alt+RPS to open the Protect Sheet dialog box, shown in Figure 1-3.
When you first open this dialog box, only the Protect Worksheet and Contents of Locked Cells check box at the very top and the Select Locked Cells and Select Unlocked Cells check boxes in the Allow All Users of This Worksheet To list box are selected. All the other check box options (including some that are not visible without scrolling up the Allow All Users of This Worksheet To list box) are deselected.
This means that if you click OK at this point, the only things that you’ll be permitted to do in the worksheet are edit unlocked cells and select cell ranges (of any type: both locked and unlocked alike).
In addition to enabling users to select locked and unlocked cells in the worksheet, you can enable the following actions in the protected worksheet by selecting their check boxes in the Allow All Users of This Worksheet To list box of the Protect Sheet dialog box:
In addition to enabling particular actions in the protected worksheet, you can also assign a password that’s required in order to remove the protections from the protected worksheet. When entering a password in the Password to Unprotect Sheet text box of the Protect Sheet dialog box, you observe the same guidelines as when assigning a password to open or to make changes in the workbook (255 characters maximum that can consist of a combination of letters, numbers, and spaces, with the letters being case-sensitive).
As with assigning a password to open or make changes to a workbook, when you enter a password (whose characters are masked with asterisks) in the Password to Unprotect Sheet text box and then click OK, Excel displays the Confirm Password dialog box. Here, you must accurately reproduce the password you just entered (including upper- and lowercase letters) before Excel turns on the sheet protection and assigns the password for removing protection.
If you don’t successfully reproduce the password, when you click OK in the Confirm Password dialog box, Excel replaces it with an alert dialog box indicating that the confirmation password is not identical to the one you entered in the Protect Sheet dialog box. When you click OK to clear this alert dialog box, you are returned to the Protect Sheet dialog box, where you may modify the password in the Password to Unprotect Sheet text box before you click OK and try confirming the password again.
As soon as you accurately reproduce the password in the Confirm Password dialog box, Excel closes the Protect Sheet dialog box and enables protection for that sheet, using whatever settings you designated in that dialog box.
When you assign protection to a sheet, your input and editing are restricted solely to unlocked cells in the worksheet, and you can perform only those additional actions that you enabled in the Allow Users of this Worksheet To list box. If you try to replace, delete, or otherwise modify a locked cell in the protected worksheet, Excel displays an alert dialog box with the following message:
The cell or chart you’re trying to change is on a protected sheet.
The message then goes on to tell you
To make changes, click Unprotect Sheet in the Review tab (you might need a password).
If you’ve assigned a password to unprotect the sheet, when you click the Unprotect Sheet button, the program displays the Unprotect Sheet dialog box, where you must enter the password exactly as you assigned it. As soon as you remove the protection by entering the correct password in this dialog box and clicking OK, Excel turns off the protection in the sheet, and you can once again make any kinds of modifications to its structure and contents in both the locked and unlocked cells.
You can use the Allow Users to Edit Ranges command button in the Changes group on the Review tab of the Ribbon to enable the editing of particular ranges in the protected worksheet by certain users. When you use this feature, you give certain users permission to edit particular cell ranges, provided that they can correctly provide the password you assign to that range.
To give access to particular ranges in a protected worksheet, you follow these steps:
Click the Allow Users to Edit Ranges command button on the Ribbon’s Review tab or press Alt+RU.
Note that the Allow Users to Edit Ranges command button is grayed out and unavailable if the worksheet is currently protected. In that case, you must remove protection by clicking the Unprotect Sheet command button on the Review tab before you retry Step 1.
Excel opens the Allow Users to Edit Ranges dialog box, where you can add the ranges you want to assign, as shown in Figure 1-4.
Click the New button.
Doing this opens the New Range dialog box where you give the range a title, define its cell selection, and provide the range password, as shown in Figure 1-5.
If you wish, type a name for the range in the Title text box; otherwise, Excel assigns a name such as Range1, Range2, and so on.
Next, you designate the cell range or nonadjacent cell selection to which access is restricted.
Click the Refers to Cells text box and then type in the address of the cell range (without removing the = sign) or select the range or ranges in the worksheet.
Next, you need to enter a password that’s required to get access to the range. Like all other passwords in Excel, this one can be up to 255 characters long, mixing letters, numbers, and spaces. Pay attention to the use of upper- and lowercase letters because the range password is case-sensitive.
Type in the password for accessing the range in the Range Password dialog box.
You need to use the Permissions button in the New Range dialog box to open the Permissions dialog box for the range you’re setting.
Click the Permissions button in the Range Password dialog box.
Next, you need to add the users who are to have access to this range.
Click the Add button in the Permissions dialog box.
Doing this opens the Select Users or Groups dialog box, where you designate the names of the users to have access to the range.
Click the name of the user in the Enter the Object Names to Select list box at the bottom of the Select Users or Groups dialog box. To select multiple users from this list, hold down the Ctrl key as you click each username.
If this list box is empty, click the Advanced button to expand the Select Users or Groups dialog box and then click the Find Now button to locate all users for your location. You can then click the name or Ctrl+click the names you want to add from this list, and then when you click OK, Excel returns you to the original form of the Select Users or Groups dialog box and adds these names to its Enter the Object Names to Select list box.
Click OK in the Select Users or Groups dialog box.
Doing this returns you to the Permissions dialog box where the names you’ve selected are now listed in the Group or User Names list box. Now you need to set the permissions for each user. When you first add users, each one is permitted to edit the range without a password. To restrict the editing to only those who have the range password, you need to click each name and then select the Deny check box.
Click the name of the first user who must know the password and then select the Deny check box in the Permissions For list box.
You need to repeat this step for each person in the Group or User Names list box that you want to restrict in this manner. (See Figure 1-6.)
Repeat Step 10 for each user who must know the password and then click OK in the Permissions dialog box.
As soon as you click OK, Excel displays a warning alert dialog box, letting you know that you are setting a deny permission that takes precedence over any allowed entries, so that if the person is a member of two groups, one with an Allow entry and the other with a Deny entry, the deny entry permission rules (meaning that the person has to know the range password).
Click the Yes button in the Security alert dialog box.
Doing this closes this dialog box and returns you to the New Range dialog box.
Click OK in the New Range dialog box.
Doing this opens the Confirm Password dialog box where you must accurately reproduce the range password.
Type the range password in the Reenter Password to Proceed text box and then click the OK button.
Doing this returns you to the Allow Users to Edit Ranges dialog box where the title and cell reference of the new range are displayed in the Ranges Unlocked by a Password When Sheet Is Protected list box, as shown in Figure 1-7.
If you need to define other ranges available to other users in the worksheet, you can do so by repeating Steps 2 through 14.
When you finish adding ranges to the Allow Users to Edit Ranges dialog box, you’re ready to protect the worksheet. If you want to retain a record of the ranges you’ve defined, go to Step 15. Otherwise, skip to Step 16.
(Optional) Select the Paste Permissions Information Into a New Workbook check box if you want to create a new workbook that contains all the permissions information.
When you select this check box, Excel creates a new workbook whose first worksheet lists all the ranges you’ve assigned, along with the users who may gain access by providing the range password. You can then save this workbook for your records. Note that the range password is not listed on this worksheet — if you want to add it, be sure that you password-protect the workbook so that only you can open it.
Now, you’re ready to protect the worksheet. If you want to do this from within the Allow Users to Edit Ranges dialog box, you click the Protect Sheet button to open the Protect Sheet dialog box. If you want to protect the worksheet later on, you click OK to close the Allow Users to Edit Ranges dialog box and then click the Protect Sheet command button on the Review tab of the Ribbon (or press Alt+RPS) when you’re ready to activate the worksheet protection.
If you click the Protect Sheet button, Excel opens the Protect Sheet dialog box, where you can set a password to unprotect the sheet. This dialog box is also where you select the actions that you permit all users to perform in the protected worksheet (as outlined earlier in this chapter).
After you turn on protection in the worksheet, only the users you’ve designated are able to edit the cell range or ranges you’ve defined. Of course, you need to supply the range password to all the users allowed to do editing in the range or ranges at the time you distribute the workbook to them.
The best part of protecting a worksheet is that you and your users can jump right to unlocked cells and avoid even dealing with the locked ones (that you can’t change, anyway) by using the Tab and Shift+Tab keys to navigate the worksheet. When you press the Tab key in a protected worksheet, Excel jumps the cell cursor to the next unlocked cell to the right of the current one in that same row. When you reach the last unlocked cell in that row, the program then jumps to the first unlocked cell in the rows below. To move back to a previous unlocked cell, you press Shift+Tab. When Excel reaches the last unlocked cell in the spreadsheet, it automatically jumps back to the very first unlocked cell on the sheet.
Of course, provided that you haven’t changed the behavior of the Enter key in the Editing Options section on the Advanced tab of the Excel Options dialog box (File ⇒ Options or Alt+FI), you can also use the Enter key to move down the columns instead of across the rows. However, pressing the Enter key to progress down a column selects locked cells in that column as well as the unlocked ones, whereas pressing the Tab key skips all those cells with the Locked protection format.
Figure 1-8 illustrates how you can put the Tab key to good use in filling out and navigating a protected worksheet. This figure shows a worksheet created from a Spa Holiday Hot Tubs invoice template. Because this invoice worksheet in the original template is protected, all worksheets generated from the template will be protected as well. The only cells that are unlocked in this sheet are the cells in the following ranges:
All the rest of the cells in this worksheet are locked and off limits.
To fill in the data for this new invoice, you can press the Tab key to complete the data entry in each field such as Invoice No., Date, Customer ID, Name, Company Name, Street Address, and so on. By pressing Tab, you don’t have to waste time moving through the locked cells that contain headings that you can’t modify anyway. If you need to back up and return to the previous field in the invoice, you just press Shift+Tab to go back to the previous unlocked cell.
There is one last level of protection that you can apply to your spreadsheet files, and that is protecting the entire workbook. When you protect the workbook, you ensure that its users can’t change the structure of the file by adding, deleting, or even moving and renaming any of its worksheets. To protect your workbook, you click the Protect Workbook command button on the Ribbon’s Review tab and then select the Protect Structure and Windows option from its drop-down menu (or press Alt+RPW).
Excel displays a Protect Structure and Windows dialog box like the one shown in Figure 1-9. This dialog box contains two check boxes: Structure (which is automatically selected) and Windows (which is not selected). This dialog box also contains a Password (Optional) text box where you can enter a password that must be supplied before you can unprotect the workbook. Like every other password in Excel, the password to unprotect the workbook can be up to 255 characters maximum, consisting of a combination of letters, numbers, and spaces, with all the letters being case-sensitive.
When you protect a workbook with the Structure check box selected, Excel prevents you or your users from doing any of the following tasks to the file:
When you turn on protection for a workbook after selecting the Windows check box in the Protect Structure and Windows dialog box, Excel prevents you from changing the size or position of the workbook’s windows (not usually something you need to control).
After you’ve enabled protection in a workbook, you can then turn it off by choosing the Protect Structure and Windows option on the Unprotect Workbook command button’s drop-down menu or by pressing Alt+RPW again. If you’ve assigned a password to unprotect the workbook, you must accurately reproduce it in the Password text box in the Unprotect Workbook dialog box that then appears.
Many times you will want to protect a workbook that you intend to share on a network. That way, you can allow simultaneous editing of the contents of its worksheets (assuming that you don’t also protect individual sheets), while at the same time preventing anybody but you from removing the Change tracking (and thus deleting the Change History log — see Book IV, Chapter 3).
If the workbook is not currently shared, you can both protect the workbook and share it by clicking the Protect and Share Workbook command button on the Ribbon’s Review tab or by pressing Alt+RO. Note that if the workbook is already shared, you must stop sharing the file before you can use this command. (See Book IV, Chapter 4, for details on how to do this.)
When you click the Protect and Share Workbook command button, Excel opens the Protect Shared Workbook dialog box, similar to the one shown in Figure 1-10. In this dialog box, you select the Sharing with Track Changes check box to enable file sharing and to turn on the Change tracking. As soon as you select this check box, Excel makes available the Password (Optional) text box, where you can enter a password that must be supplied before you can stop sharing the workbook.
If you enter a password in this text box (and you should — otherwise, there’s little reason to use this option, because anyone can remove the protection from the shared workbook and thus stop the file sharing), Excel immediately displays the Confirm Password dialog box, where you must accurately reproduce the password.
When you do this, Excel displays an alert dialog box that informs you that it will now save the workbook, and when you click the Yes button, the program saves the workbook as a shared file and protects it from being made exclusive without the password. The program also adds a [Shared] indicator to the filename at the top of the Excel program window to let you know that the workbook is being shared.
18.218.55.14