Save content
Have you found this content useful? Use the button above to save it to your profile.
AIA

Excel Compendium: Using Hide and Protect

by
2nd Nov 2007
Save content
Have you found this content useful? Use the button above to save it to your profile.

Introduction - why and how you should protect Excel workbooks
Protecting worksheets and workbooks can help prevent users inadvertently replacing or changing cells containing formulae or other content whilst allowing them to enter data into appropriate cells. To improve the reliability of any spreadsheet, but particularly one intended for use by anyone apart from the creator, the spreadsheet should be designed to allow all cells to be protected apart from those that contain editable data. This requires the design to ensure that no cell contains a mixture of data and formula since if it does, it is not possible to protect the formula without denying access to the data.

As well as protecting parts of worksheets from being edited, it is also possible to hide the contents of cells. This could be used, for example, to prevent the disclosure of sensitive information used as part of a calculation. Though not part of the Excel protection system, it is also possible to hide rows and columns within a sheet and entire worksheets or workbooks.

It may come as a surprise to learn that, when you create a new workbook, by default all cells on all sheets are locked. Locking doesn't come into effect until you protect each sheet. This is a 'fail safe' approach as, by starting with all cells locked, if you forget to unlock a cell it just means you'll have to unlock it. If things were the other way round, and you had to lock all the cells to be protected, forgetting to lock just a single cell would compromise the integrity of the entire workbook.

Having unlocked the cells to which you want to allow access you then have to protect all the individual sheets in the workbook, allocating passwords to ensure that users can't just unprotect the sheets.

Finally you need to protect the structure of the workbook itself, for example to prevent deletion or insertion of worksheets.

Excel XP introduced a significant enhancement to Excel's protection capabilities. Prior to XP protecting a sheet prevented the user from doing almost anything apart from editing the contents of unlocked cells. From XP onwards, it is possible to specify whether an extensive range of actions are permitted or prohibited on a protected sheet. The ability to allow access to designated ranges of cells by particular users, or groups of users, or by the entry of a password, was also introduced.

Steps to protect a workbook

Step 1 - Design
Before you start going through the mechanics of unlocking cells and protecting sheets and workbooks it is important to design your spreadsheet to ensure protection works effectively. The lengths you go to will depend on the importance of the spreadsheet and your analysis of the risk of error. If it is important that the spreadsheet does give the correct answer then the following logic applies:

  • For the spreadsheet to calculate the correct answer from the data supplied, all formulae must work correctly
  • If a user can change a formula - inadvertently or otherwise - you cannot be sure that all formulae are working as you intended
  • Therefore no user can be allowed to access any cell containing a formula.

This means that the design of your spreadsheet should allow for all cells necessary for the entry of the required data to be unlocked and all formula cells to be locked.

As well as designing for reliability, you should also design for ease of use, so it is sensible to ensure that the user can easily find the data entry cells. To improve the chances of correct data being entered, data cells should be clearly labelled and highlighted - maybe using a different background colour, cell comments should be used and also data validation set up where necessary.

Step 2 - Unlock
Unlock those cells that the user is to be allowed to access. To do this, select the required cells then go to Format-Cells-Protection Excel 2007: Home ribbon, Cells group, Format, Lock Cell or Format Cells to display full dialog as shown below):

This screen allows you to turn cell locking on or off for the selected cells, and also to set the cell contents to 'Hidden' if required. As the text details, locking and hiding has no effect until the worksheet is protected.

Step 3 - Protect each sheet
Go to Tools-Protection-Protect Sheet (Excel 2007: Review ribbon-Changes group-Protect Sheet) to turn on protection, set the options where applicable, and allocate a password. You will need to repeat this for each of the sheets within your workbook:

The two screens below show how the options available were extended from XP onwards:

Excel 2000

Excel XP and beyond

Step 4 - Protect the workbook
Protecting a sheet protects the cells on the sheet but does not protect the sheet itself from deletion, which could obviously have a detrimental effect on the workbook. To protect the structure of the workbook as a whole, go to Tools-Protection-Protect Workbook (Excel 2007: Review ribbon-Changes group-Protect Workbook-Protect Structure and Windows) again you will need to enter a password.

Tip: Give different levels of access and avoid issues with cutting and pasting
If you need to give different users access to different areas within your spreadsheet then from XP onwards you can use the 'Allow Users to Edit Ranges' option of the Tools-Protection sub-menu. (Excel 2007: Review ribbon, Changes group, Allow Users to Edit Ranges). This option allows you to link one or more ranges to a Windows user or group so that they can edit the range without a password, and to set a password that will allow other users to edit the range.

This option may also be the answer to an issue with cutting and pasting on a protected sheet. In this very simple example we have unlocked cells A1 and A2 and entered the simple formula =A1*A2 into cell A5. Even if we protect the sheet, we can click on the unprotected cell A2 and cut the contents and paste them into cell A1. Cutting cell A2 will mean that A5 can no longer refer to it and will return a #REF! error:

As an alternative, if you leave all the cells locked, but use the 'Allow Users to Edit Ranges' option to allow access to the range A1:A2 then users can edit the contents of the cells, but because they are still locked, seem not to be able to 'Cut' either of them.

Excel passwords
For protection to work, it is obviously vital that effective passwords are used. Third party tools are available to try and reveal unknown passwords and while this is useful for the legitimate user who has lost a password or a member of staff, it is obviously of concern with regard to the effectiveness of password control. As with all passwords, the shorter and less complex the passwords you use, the greater the chance of discovery.

Excel objects
If you include interactive objects such as check boxes, lists and combo boxes then, to allow your users to use the objects, you not only need to unlock the objects themselves, but also the cells to which they are linked.

Tip: Use a PivotTable for data analysis
If you need to allow users to select particular items then, rather than using AutoFilter with the existing data, you could set up a PivotTable on a separate sheet linked to the original data. This would allow you to protect the entire sheet containing the data whilst letting the user analyse it how they want to with the PivotTable. See below for further details on how to do this.

We have seen above how to use the 'hidden' setting of cell protection together with Protect-Sheet to hide the contents of individual cells. You can also hide entire rows or columns. Just select cells in the columns or rows in question and choose Format-Column (or Row)-Hide (Excel 2007: Home ribbon-Cells group-Format-Hide & Unhide-Hide Rows/Hide Columns). Alternatively, select the entire columns or rows and choose Hide from the right-click menu.

To unhide hidden columns or rows, select a range that spans the hidden range, then use the Unhide options of the menus described above. To stop users unhiding ranges you will need to protect the worksheet.

You can also hide whole sheets. To hide the active sheet, choose Format-Sheet-Hide (Unhide to choose a sheet to unhide). (Excel 2007: Home ribbon-Cells group-Format-Hide & Unhide-Hide Sheet/Unhide Sheet). You can hide multiple sheets by selecting a group of sheets and then using the above options, but it does not seem possible to unhide sheets - other than one at a time - without resorting to VBA code (see the Further reading section for advice on how to do this.)

To stop users hiding or unhiding sheets you need to protect the workbook structure.

Finally, you can hide the workbook itself. To hide the active workbook choose Window-Hide. To unhide hidden workbooks use Window-Unhide then select the workbook to unhide. (Excel 2007: View ribbon-Window group-Hide/Unhide).

Use a PivotTable to analyse protected data
Unhiding multiple worksheets using VBA code
Using third party add-ins or VBA code to conditionally hide rows
Excel password discussion
Create a password entry box that uses asterisks
ExcelZone Compendium - hide and protect archive

Tags:

Replies (0)

Please login or register to join the discussion.

There are currently no replies, be the first to post a reply.