Thanks again to Nick Williams at Acuity Training for this guest post!
When creating an Excel spreadsheet that is going to be sent on, particularly to those less Excel savvy, it can be useful to restrict certain functionality, as well as hide information and formulas. This will stop the spreadsheet from ‘breaking’, where formulas stop working or reference the wrong data because somebody has been in and altered something they shouldn’t have, and also prevent people looking at figures used in calculations that they may not fully understand.
Hiding whole rows or columns
If the spreadsheet contains a row or column of data purely used for calculations, hiding it is the quickest and easiest way of preventing the data from being easily accessible. Simply right click on the row number or column letter, and select Hide from the dropdown menu that appears. To unhide the hidden row or column, highlight the rows or columns either side of the hidden data, right click again and select Unhide from the drop-down menu.
Protecting cells and hiding formulas
To prevent the content of cells being edited, or formulas being visible, it is necessary to set cell protection in the Format Cells menu. To access this, select the cell (or group of cells) that are to be protected and right click within the selection. From the dropdown menu that appears, select Format Cells. A dialog box will appear; select the Protection tab from here.
There are two options here, Locked and Hidden. Ticking the box next to Locked will lock any cells that are selected. This means that it will not be possible to make any changes to their content. Data cannot be changed or deleted. Ticking the box next to hidden will hide any the information in the formula bar, preventing users from seeing the workings behind the data.
Once finished, click OK. So that it is still possible to work on the spreadsheet, neither of these changes will come into effect until the sheet has been protected.
Using Protect Sheet
To protect the sheet, click on the Protect Sheet icon on the Review ribbon. There are lots of options here, and depending on how much functionality the end user is to be left with, it is possible to tick all, none, or some of the available options.
To lock cells and hide formulas, make sure the checkbox above the password field is ticked. This will only be applied to cells where cell protection has been turned on.
It is not necessary to enter a password if the intention is to just prevent the sheet from being accidentally modified, however, if the intention is to stop it from being changed deliberately, entering a password will prevent other users from being able to unprotect the sheet and make changes.
The options below the password field allow for users to have access to certain tools on the worksheet. By default, the only two that are ticked are Select locked cells and Select unlocked cells. This means that users can click on any cells within the sheet, allowing them to do things like copy and paste their content into another sheet or a Word document.
Format cells governs whether the user is able to change the appearance of the font, as well as the cell itself i.e. borders and backgrounds. It also allows access to the Format cells menu where options such as the type of number, or number of decimal places can be set. Format columns and Format rows allows column width and row height to be adjusted, and also lets users hide and unhide columns and rows.
The insert and delete options should be self-explanatory.
If Use AutoFilter is left unchecked, the arrow representing the AutoFilter dropdown will still appear, however, users will be unable to click on it. If it is checked, users will be able to filter the data, but will not be able to use the sort features on AutoFilter, unless the Sort option has also been ticked to allow users access.
Ticking Use PivotTable & Use PivotChart will allow the user to manipulate any PivotTables or PivotCharts in the worksheet. They will still appear if it is left unticked, but they will just function as tables i.e. the user will be unable to manipulate their content.
The Edit objects option controls whether the user is able to make changes to objects embedded into the worksheet, including things like images, charts, buttons and comments. In the case of objects with macros assigned which act as buttons, the user will still be able to click to run the macro, but will be unable to edit or delete the object.
Protecting scenarios by leaving Edit scenarios unticked will prevent changes being made to existing scenarios, but will not prevent new scenarios from being created.
Hope you enjoyed this lesson! Thanks again to Nick who works as a tutor on www.acuitytraining.co.uk’s Excel courses for putting it together.