Apply Worksheet and Workbook protection

Whilst you can require a password to open or modify a workbook, applying Worksheet or Workbook protection allows you to password-protect the actual editing of parts of the file. Protecting your worksheets is mainly useful for preventing people from typing into cells or overwriting them, which can protect the integrity of any formulas you have written. It also prevents people from modifying features that you have built into your workbook such as Data validation or Conditional formatting, thereby ensuring that the validation is enforced.

In addition to having one or more worksheets protected within a file, the workbook structure overall can also be protected which prevents people from changing the name of worksheets or adding/removing sheets from the file.

It is also worth bearing in mind that similar protection options also exist in Microsoft Word where you can limit formatting to a selection of styles (useful to enforce consistent use of fonts/style/branding) and can select parts of the document (such as particular words or phrases) which users are allowed to edit freely whilst preventing any other changes from being made.

Applying worksheet protection allows you to restrict specified cell contents from being edited and also to apply other restrictions such as disallowing rows/columns to be inserted or deleted.

Once a worksheet is protected, all locked cells will be un-editable. By default all cells on a worksheet are locked so cannot be edited once the sheet is protected – to allow any cells to be editable on a protected worksheet they must first be specifically marked as unlocked. To do this, highlight the cells you want to be editable, right click and select ‘Format Cells’ (or use the keyboard shortcut Ctrl + 1) and then under the ‘Protection’ tab (screen shot below) deselect the ‘Locked’ checkbox.

Any cells which this has not been done for will remain locked once worksheet protection is applied.

To apply worksheet protection, click on ‘Protect Sheet’ under the Review tab and you will be presented with the dialog box shown below.

Entering a password for the protection is optional though if not applied it is possible for anyone to just unprotect the sheet without a password and make any changes they wish. Ensure that if a password is applied that you make a note of it somewhere safe and accessible as it’s not easy to recover or crack the password at a later date!

By default users are allowed to select all types of cells – both locked and unlocked. Deselecting these means that the user won’t be able to interact with the applicable sections of the worksheet.

Selecting any of the other options will make the action allowable once the worksheet is protected. For instance by default users can’t insert rows or columns which is useful for ensuring that the structure of the worksheet can’t be changed (needed e.g. with 3D Formulas). By selecting this option, users will be still be able to insert rows/and columns when the worksheet is protected, provided that the cells can be selected.

An example of protecting cells in a worksheet is shown in the video clip below.

Excel 2007-2010 allows you to take the concept of protecting cells and ranges even further by allowing cells to be unlocked only for specific users with other users requiring a password to edit the cells when worksheet protection is applied.

To enable this, click on ‘Allow users to edit ranges’ in the Review tab. In the ‘Allow users to Edit Ranges’ dialog box that comes up, select ‘New’ to highlight a new range of cells that you want to apply specific permissions on. In the ‘New Range’ dialog box that appears (screen shot below), enter a name for the range, specify a range you want to be editable, and a password which will be required by people not granted permission to edit that range. Under ‘Permissions’ you can select people (users or groups) who will be able to edit that range without requiring a password.

Click ‘Add’ and under ‘Enter the object names to select’ you can type in a user (this is the users login name – in a corporate environment this is usually linked to something like Active Directory; on a personal computer you can type in a windows username as managed by user accounts in the control panel).

Applying this to a range and then applying worksheet protection allows for richer discrimination of protection rather than just having cells being locked (restricted on worksheet protection) or unlocked (unrestricted on worksheet protection). Under this method, even though cells are locked they can still be edited freely (including copying and pasting) by specified users – either those explicitly identified or those with a password. By specifying a different password here to the worksheet protection one, you can provide people with a password that will only allow them to edit the ranges they are responsible for populating, whilst other users will be unable to edit them because they are locked and the worksheet is protected.

As well as protecting individual worksheets, it is possible to protect the workbook as a whole which restricts people from changing the number or order of worksheets, as well as potentially the way in which the workbook is displayed.

To protect the workbook, click on ‘Protect Workbook’ under the Review tab, which displays the dialog box shown below.

Protecting the structure of the workbook is what will prevent people from inserting, deleting, moving or hiding/un-hiding worksheets. This is useful if you have hidden worksheets (perhaps containing static data which you don’t want people to see or edit), if the order of sheets is important (For example if 3D Formulas are being used) or if the name of sheets needs to be consistent (e.g. because of an INDIRECT formula which is referencing them).

Protecting ‘windows’ in the workbook is probably a less useful feature – it allows you to lock the way the workbook is displayed in the window, which prevents it from being minimized or maximized within the Excel application.

Once workbook protection is applied, if you want to [share the workbook] so that it can be edited by multiple people at the same time then click on ‘Share workbook’ in the Review tab. The option in Excel 2007-2010 of ‘Protect and Share workbook’ sounds like it would apply the same workbook protection combined with workbook sharing, however the workbook protection applied in this case isn’t the same as protecting the structure/windows of a workbook and is instead more designed around change management options in a shared workbook environment.