6.4 Protect sheet contents and workbook structure - Video Tutorials & Practice Problems

<v Presenter>So now that we have authored our content,</v> inspected for issues, compatibility and accessibility and collaborated with others review and finalize our model, let's look at some options for protecting our content. The need for implementing such protection can be specific to the use case. However, there are some common scenarios that we would like to highlight. These are, avoiding deliberate or unintentional changes to our content, workbook structures, sheet layout and formatting, protecting copyright or sensitive information for example formula code, implementing access control so only select individuals can read or edit our model. And finally, to ensure consistency and data integrity for our model. So in that context, Excel provides us with three protection levels, worksheet protection to protect content and formatting on a specific worksheet, workbook protection to protect the general workbooks structure and file protection to prevent unauthorized access to content within our file. During this sub lesson, we will review these options in further detail. In order to successfully implement adequate controls, it is important to understand the scenarios where each of these options may be applicable and are reliable. Let's review worksheet protection first. This capability allows us to protect specific worksheets within our model from accidental or intentional changes to cell content, worksheet, layout and formatting. It also allows us to highlight specific formula code that we may not want the end users to see for confidentiality or other reasons. Finally, it provides the ability to selectively allow or prevent editing for specific cells, ranges and objects on an individual worksheet. At this time, we would also like to highlight that sheet protection within Excel is a preventative measure and it is not intended to be a security feature. Someone with the appropriate technical knowledge may be able to work around this protection level with these. So for a better security controls, we should be using one of the other protection levels we'll discuss in this lesson. Now let's review some uses for this capability. For this example, we'll use the prep budget and the report budget sheets from our lesson for content. For the prep sheet, we want to protect all formulas and formatting except for the manually enter data for base metric, percent change and dollar change. To do this, we'll first protect all cells on the sheet and then selectively unprotect the cells required for data entry. To protect all cells, we can select all content on the sheet using the arrow button in the top left corner, we can then launch the format cells dialog box using the control plus one keyboard shortcut and on the protection tab of this dialog box, we will confirm that the locked option is enabled for all cells. Now, if we were to protect our sheet at this point, none of the cells would be editable. So to allow editing for our data entry cells, we need to first select them then under the home tab of the ribbon bar within the format sub menu of the cells group we can toggle their lock option towards the bottom of the list. The last option within the same list, will launch the format cells dialog box that we just looked at. Now if we're were to protect our sheet, only the data entry cells within the revenue section of our prep sheet would be editable. Now another way to allow data entry while protecting other content on our sheet is with the use of allow edit ranges capability. We can access this option under the view tab within the protect group and clicking on the allow edit ranges button. This option can be used to allow certain cells to be unlocked for data entry or editing with or without a password. It also allows the author to enable such access for a selected number of users. However, when setting up the password for such cells or ranges, we'll need to enable the select locked cells option when protecting the sheet. This would mean that all users will be able to see formula code within protected cells by selecting them. So as you can see, some consideration may be warranted when enabling such protection options. Now let's implement the allow edit ranges capability for the operating expenses section. To start with, we'll click on the new button, provide a name for the range using the naming convention for our model and then select the editable range of cells. Next, we can provide a password for this range, let's say test and also modify the corresponding permissions. In our case we'll use the everyone group and then check the deny option so that the password is always required. As we click Okay, the dialog box is confirming that the deny permissions will override the allow permissions in case of a conflict. Now accepting our changes for this range, entering the password again, we will then apply the new configuration and then protect the sheet from the same dialogue box. Here, we will first disable the select locked cells option and then press okay. As you can see, we can now edit the values within our revenue section but cannot select the cells within our operating expenses section. Now let's go back and protect our sheet and then protect it again. And then this time we'll select the locked sales option. With this new configuration, we're able to select the cells within our operating expenses section as well. However, as we try to change values within this section, we'll be prompted for the password that we entered previously. Changing values within the revenue section does not require such password as it is outside the editable range we have configured. Now, if we unprotect the sheet, go back to allowed edit ranges and modify the range we had configured to change the permissions and allow editing without a password. Apply our changes and protect the sheet from the same dialogue box. We'll notice that the cells within the opex section are now also editable without a password. In some cases, we may want to create a single password for the worksheet when protecting its content. We can do so using the protect sheet dialog box and providing a password at the top to unprotect our sheet. This dialog box also provides a few other options for customizing our protection level while some options are self-explanatory and allow users to select locked or unlocked cells, insert rows, columns or hyperlinks, delete columns or rows and so on. We will now review some other options in further detail. To start with the format cells option allows the user to modify cell formats including conditional formatting. These options can be accessed using the control plus one keyboard shortcut we discussed earlier or using the conditional formatting menu under the home tab. Similarly, the format columns or rows option will allow access to the relevant format options within the cells group of the home tab. And the sort option will enable access to the sort and filter group items on the data tab. Additionally, the use auto filter option will allow the user to change filters on ranges and if we wanted to allow our users to create, format, refresh or modify pivot table reports, we can leverage the use pivot table reports option. Finally, the edit objects option allows the users to make changes to graphic objects on our sheet including charts, shapes, text boxes and other interactive controls. For example, this final option can be used for locking the charts on our report page from lesson four. However, before protecting the sheet, we should confirm that the charts when tend to lock have the corresponding option enabled. To do so, we can first select a chart, right click within the white space and select the format chart area option. We can then go to the size and properties tab, expand the property section and review the locked option. We can modify this option for multiple charts by pressing down the control key when selecting the target object. Once selected, we can go back to the size and properties tab and then update the locked option under the property section. Please note that in some cases like interactive reporting scenarios, we may want to allow our users to move or edit certain charts. In such cases, we can either unlock the specific charts or enable the edit objects option before protecting our worksheet. So as you can see, sheet protection offers a variety of options to protect our content. However, these are applied at a worksheet level. To protect our workbook structures and layout by preventing users from adding, modifying, copying or hiding and hiding sheets within a workbook, we can use the workbook protection options. Once we have structured or worksheets in the required sequence and maybe hidden other worksheets that are not required by the end users, we can lock this customized structure using workbook protection. To enable this option for a workbook, we can go to the review tab on the ribbon bar and then click on the protect workbook button, provide an optional password before accepting the protection for our workbook. Once implemented all options related to modifying the structure of our workbook will be disabled in the relevant menus. It is also important note that protecting a workbook will not enable sheet protection for the worksheets would then. This would have to be enabled individually for all worksheets within a workbook. Additionally, both protection levels we have discussed thus far are not intended to be security features. So in scenarios where we need comprehensive protection for our models, we should use Excel file protection instead or in addition to the previous options. Excel file protection provides a reliable security option by preventing other users from accessing the content within our workbook without the correct password. This can be used to restrict access to our models and encrypt the corresponding Excel file. To enable this option, we can go to the file menu then info and then click on the protect workbook button. Here, we can select the encrypt with password option and enter our desired password. Now, similar to workbook protection, protecting the Excel file will not automatically enable the workbook or sheet protection features. These will have to be enabled separately as discussed previously. It is also important note that Microsoft does not provide options to recover Excel file passwords. So we need to securely maintain it within our records. All right, so with that, we have now covered the protection options available within Excel. You should now be able to implement a customized protection level for your next project.