Level 2 - Lesson 9 - Protecting and Hiding

Category: [General,Excel] Demo Available

PROTECTING AND HIDING

There are times when using Excel that we do not want other people to make changes to our Workbooks, Formulas, Worksheets or Cells. We can achieve this in many ways, ranging from hiding data to preventing the Workbook from opening at all without a password.

All passwords in Excel are case sensitive.
All passwords in Excel can contain numbers, letters, symbols and spaces.
Passwords in Excel can be from a little as 1, to as many as 255 characters

There is one very important point that we should make very clear from the start and that is that Excel was not designed for high security! Our advice is that if you need to be totally certain that nobody can access your data then Excel is not the correct tool. Excel is primarily a Spreadsheet application and as such offers security that will prevent most users from being able to access what you have protected. As far a security goes in today's world it is most probably considered low level. There are hundreds of web sites that offer password crackers for Excel Workbooks and Worksheets that will be able to gain access to loosely protected Workbooks and Worksheets.

Now, having said the above there are things we can do to make a password very hard, to impossible for a password cracker to crack. To do this we need to use a long password made up from letters (upper and lower case), numbers and symbols. When we protect a Workbook we can use up to 15 characters and when we protect a Worksheet we can supply up to 255 characters. The basics of this is, the more characters we use the harder it is to crack. The more combinations of characters we use also adds much complexity to our password.

One way we can look at this is from an old story you may have heard. Let's say we have a chessboard and we place a one cent coin on the first square and then proceed to move the one cent coin to each square on the chessboard (64 squares in total). Each time we move it though, we double the amount, so the order would be 1, 2, 4, 8, 16, 32, 64, 128 etc. In other words after 8 moves the one cent would now be 128 cents ($1.28). This doesn't sound like much but after another 8 moves we have 32768 ($327.68). By the time we have made 64 moves the one cent is worth 9223372036854780000 (LOTS!).

It is a similar principle with our passwords. By using a long password made up with a combination of numbers, upper and lower case letters and symbols we can increase the possible number of passwords one hell of a lot! Most password crackers use what is know as the brute force method. This means they run through all possible combinations until they stumble on the correct one. This can take days or months for a six character combination and may never happen for anything above this. Many password crackers will state that they do not hold much hope for a long password, so if it is important that another user cannot access your Workbook, use a long combination. One more thing, write it down and put it somewhere safe because if you forget it, you will not be able to get in either!

Let's look at the different types of protection available to us.

Protection

Excel offers us three levels of protection from within the Workbook, these are:

Protect Sheet
Protect Workbook
Protect and Share Workbook

The other method of Protection is protecting the Workbook from within the Save as dialog box.

Protect Sheets

To protect your spreadsheet, Excel proposes the following options:

On the Review tab, in the Changes group, click Protect Sheet:

Enter a password if you want to require a password for "unprotecting" the spreadsheet.

In the Allow all users of this worksheet to list, select the elements that you want users to be able to change.

Select locked cells - Moving the pointer to cells for which the Locked check box is selected on the Protection tab of the Format Cells dialog box. By default, users are allowed to select locked cells

Select unlocked cells - Moving the pointer to cells for which the Locked check box is cleared on the Protection tab of the Format Cells dialog box. By default, users are allowed to select unlocked cells, and they can press the TAB key to move between the unlocked cells on a protected worksheet

Format cells - Changing any of the options in the Format Cells or Conditional Formatting dialog boxes. If you applied conditional formats before you protected the worksheet, the formatting continues to change when a user enters a value that satisfies a different condition

Format columns - Using any of the column formatting commands, including changing column width or hiding columns

Format rows - Using any of the row formatting commands, including changing row height or hiding rows

Insert columns - Inserting columns

Insert rows - Inserting rows

Insert hyperlinks - Inserting new hyperlinks, even in unlocked cells

Delete columns - Deleting columns
Note: If Delete columns is protected and Insert columns is not also protected, a user can insert columns that he or she cannot delete

Delete rows - Deleting rows

Note: If Delete rows is protected and Insert rows is not also protected, a user can insert rows that he or she cannot delete

Sort - Using any commands to sort data
Note: Users can't sort ranges that contain locked cells on a protected worksheet, regardless of this setting

Use AutoFilter - Using the drop-down arrows to change the filter on ranges when AutoFilters are applied
Note: Users cannot apply or remove AutoFilters on a protected worksheet, regardless of this setting

Use PivotTable reports - Formatting, changing the layout, refreshing, or otherwise modifying PivotTable reports, or creating new reports

Edit objects - Doing the any of the following:

Making changes to graphic objects-including maps, embedded charts, shapes, text boxes, and controls-that you did not unlock before you protected the worksheet. For example, if a worksheet has a button that runs a macro, you can click the button to run the macro, but you cannot delete the button.
Making any changes, such as formatting, to an embedded chart. The chart continues to be updated when you change its source data.
Adding or editing comments.

Edit scenarios - Viewing scenarios that you have hidden, making changes to scenarios that you have prevented changes to, and deleting these scenarios. Users can change the values in the changing cells, if the cells are not protected, and add new scenarios

Allow specific users to edit ranges in a protected spreadsheet

Excel offers you an ability to assign user-level permissions to different areas on a protected spreadsheet. You can specify which users can edit a particular range while the spreadsheet is protected. As an option, you can require a password to make changes.

On the Review tab. in the Changes group, click Allow Users to Edit Ranges:

Note: This command is available only when the spreadsheet is not protected.

Do one of the following:

To modify an existing editable range, select it in the Ranges unlocked by a password when sheet is protected box, and then click Modify.

To delete an editable range, select it in the Ranges unlocked by a password when sheet is protected box, and then click Delete.

To add a new editable range, follow next steps:

Click New to open dialog box:

In the Title box, type the name for the range that you want to unlock.

In the Refers to cells box, type an equal sign (=), and then type the reference of the range that you want to unlock. You can also click the Collapse Dialog button, select the range in the spreadsheet, and then click the Collapse Dialog button again to return to the dialog box.

In the Range password box, type a password that allows access to the range. The password is optional. If you don't supply a password, then any user can edit the cells.

Click Permissions to open dialog box:

Add users that you want to be able to edit the ranges or remove them, and then click OK three times.

After all changes, in the Allow Users to Edit Ranges dialog box, click the Protect Sheet... button to open the Protect Sheet dialog box:

In the Allow all users of this worksheet to list, select the elements that you want users to be able to change (see Protect Excel spreadsheet options).

In the Password to unprotect sheet box, type a password for the sheet, click OK, and then retype the password to confirm it.

Note: The password is optional. If you don't supply a password, then any user can unprotect the sheet and change the protected elements. Make sure that you choose a password that is easy to remember, because if you lose the password, you cannot gain access to the protected elements on the spreadsheet.

Protecting workbook elements

Protecting a workbook is different from protecting the Excel file with a password to prevent reading/writing or locking spreadsheet elements to prevent editing. Workbook protection forbids other users to add, move, rename or delete worksheets and defends against viewing hidden worksheets or hiding worksheets.

To protect Excel workbook elements, follow these steps:

On the Review tab, in the Changes group, click Protect Workbook.

Click the Protect Structure and Windows button. It opens the Protect Structure and Windows dialog box.

In the Protect workbook for group, select one of the following options:

To protect the structure of a workbook, check the Structure check box. To prevent users from:

Viewing spreadsheets that you have hidden
Moving, deleting, hiding, or changing the names of spreadsheets
Inserting new spreadsheets or chart sheets (Note: Users will be able to insert an embedded chart to an existing spreadsheet)
Moving or copying spreadsheets to another workbook
In PivotTable reports, displaying the source data for a cell in the data area, or displaying page field pages on separate spreadsheets
For scenarios, creating a scenario summary report
In the Analysis Tool Pack, using the analysis tools that place results on a new spreadsheet
Recording new macros (Note: If you run a macro that includes an operation that can't be performed in a protected workbook, a message appears and the macro stops running)

To keep workbook windows in the same size and position each time the workbook is opened, check the Windows check box.

Changing the size and position of the windows for the workbook when the workbook is open
Moving, resizing, or closing the windows (Note: Users will be able to hide and unhide windows)

To prevent other users from removing workbook protection, in the Password (optional) box, type a password, click OK, and then retype the password to confirm it.

Note: Password is optional. If you don't supply a password, then any user can unprotect the workbook and change the protected elements. Make sure that you choose a password that you can remember, because if you lose the password, you cannot gain access to the protected elements in the workbook.

Workbook protection in Excel isn't a security feature, and you shouldn't use it as a mean of protection of the intellectual property. There are many commercial password-cracking tools for Excel, and it won't take long to crack your password. However, protecting the structure of worksheets and hiding some sheets of analytical reports and surveys protects from “curious” users, and irrelevant questions.

Shared Workbooks and Track Changes

A shared Workbook is a Workbook that resides on a Network and allows more than one person to make changes to it at the same time. All users must be using Excel 97 onwards. There are some considerations that need to be made before sharing a Workbook as there are a lot of Excels features that will not work in a Shared Workbook. The list below is from the help file and should be read if considering sharing a Workbook.

Excel Help - Limitations of shared Workbooks

Some features of Microsoft Excel are not available when you use a shared workbook. If you need to use these features, do so before you share the workbook, or remove the workbook from shared use.

In a shared workbook, you cannot do the following:

Delete worksheets.
Merge cells. You can view cells that were merged before you shared the workbook.
Define or apply conditional formats. However, you can see the effects of conditional formats applied before you shared the workbook.
Set up or change data validation restrictions and messages. However, you can see the effects of restrictions and messages that were set up before you shared the workbook.
Insert or delete blocks of cells. You can insert or delete entire rows and columns.
Insert or change charts, pictures, objects, or hyperlinks.
Use the drawing tools.
Assign a password to protect individual worksheets or the entire workbook. Protection that you applied before sharing the workbook remains in effect after you share the workbook.
Change or remove passwords. Passwords that you assigned before sharing the workbook remain in effect after you share the workbook.
Save, view, or make changes to scenarios.
Group or outline data.
Insert automatic subtotals.
Create data tables.
Create PivotTables or change the layout of existing PivotTables.
Make changes to dialog boxes or menus.
Write, change, view, record, or assign macros. However, you can record operations in a shared workbook into a macro stored in another workbook that isn't shared. In a shared workbook, you can run macros that were created before you shared the workbook; although if you run a macro that includes an unavailable operation, the macro stops running when it reaches the unavailable operation

End of Help

So as you can see there are few features that are not available in a Shared Workbook. When all is said and done a Shared Workbook is best suited to a Workbook that requires other users to only input data into. The main purpose of Shared Workbooks is to distribute them to different users and collect their input. The Shared Workbooks can then be Merged into one. We will look at Merged Workbooks a little later.

Advanced

Once we have checked the Allow changes by more than one user at the same time checkbox we can set up the options under the Advanced page tab.

Track Changes

Keep change history for ........ days

In the box to the right of this simply enter the number of days you want the changed history retained for.

Don't keep change history

Switches off the change history. This means you cannot Merge changes from other copies of the same Workbook or review any changes.

Update Changes

When file is saved

All changes made by yourself, or by other users are updated each time you save the Workbook.

Automatically every ..... minutes

Will automatically show you changes that have been made and saved by other users at the interval you specify in the minutes box.

Save my changes and see others' changes

Works in conjunction with Automatically every ….minutes. Your changes are saved, while changes from other users are updated at the interval you set in the Automatically every ….minutes box

Just see other users' changes

Works in conjunction with Automatically every ….minutes. All others' changes are updated at the time interval specified in the Automatically every ….minutes, but your changes are not saved at the time of update.

Conflicting changes between users

Ask me which changes win

If checked and conflicting changes occur you will be displayed the Resolve Conflicts dialog box. In here you can review the conflicting change and decide which change wins.

The change being saved wins

If checked, any conflicting changes will be automatically replaced with your changes each time you save. The Resolve Conflicts dialog box will not appear.

Include in personal view

Print Settings

Will save all your personal print settings, if you have specified any. Print settings include page breaks, print areas, any settings you make in page break preview, and any settings in the Page Setup dialog box (File menu).

Filter settings

Retains any settings you make using the Auto filter or Advanced filter

Merge Workbooks

Preparing a Workbook for Merging

As mentioned above it is possible to Merge multiple copies of the same Workbook if it is Shared. But before this is possible we must have specified a number of days in the Keep change history for…..days option on the Advanced page tab of the Share Workbook dialog box. This means that if we created our Shared Workbook on the 1-Jan-2005 and had set the Keep change history for…..days to 15 days, we MUST perform the Merge before the 16-Jan-2005 (15 days from 1-Jan-2005). After this date it is no longer possible to Merge the Workbooks. If you are unsure how long it will be before you Merge the copies, then simply enter a large number of days. The Maximum allowed days are 32,500.

In a nutshell, the procedure for preparing a Shared Workbook for Merging would be:

Go to File>Share and ensure the Allow changes by more than one user at the same timecheckbox is checked.
Select the Advancedpage tab
Check the Keep change history forcheckbox and type in any number of days. Remember though that if the number of days you specify expires you will not be able to perform a Merge.
Click OKand save.
To allow distribution of the Workbook to multiple users go to File>Save asand save multiple copies of the Workbook, each with a different name.

Merging the Workbooks

Once the Workbook has been shared for the required time (before Keep change history for… days expires) we can Merge all copies of the Shared Workbook. This is a very straightforward process, all we need to do is ensure all copies we want to Merge are closed. The only copy open should be the Workbook we are going to perform the Merge in.

Go to Tools>Merge Workbooks. If prompted to save, do so.
Select the Workbook(s) to Merge from the Select Files to Merge Into the Current Workbookdialog box. For more than one copy hold down the Ctrl or Shift key while selecting. Do not include the current Workbook!
Click OK.

Excel Help

If a cell contains a comment, the comment includes the name of the person who inserted the comment. When you merge shared workbooks and a cell has comments from more than one person, the comments appear one after another in the comment box for the cell.

End of Help

All copies of the Shared Workbook will now be Merged into one. If you wish you can now go to Tools>Track Changes>Accept or Reject Changes and use the Accept or Reject Changes dialog to overwrite, accept or reject any changes.

Removing a Workbook from Shared Use

When we remove a Workbook from shared use you will be disconnecting all other users of the Shared Workbook and they will lose any unsaved Work. It also means we will be switching off the Change History and erasing any stored history. To top this off it also means the Workbook cannot be Merged with other copies of the Workbook. For these reasons you should be certain that all needed work is completed, then inform all other users of the Shared Workbook of your intentions so they do not lose any unsaved Work.

Ideally the Workbook should only be removed from Shared use when all work has been competed. The Steps for removing a Workbook from Shared use are as below:

Go to Tools>Shared Workbookand select the Editing
Look in the Who has this workbook open now If others do have the Workbook open they will lose unsaved work.
Uncheck the Allow changes by more than one user at the same time
Click OK
Excel will prompt you about the effects this will have on other users.
Click Yes.

Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.

Gallery

stars (0 Reviews)

Microsoft® and Microsoft Excel® are registered trademarks of Microsoft Corporation.
OzGrid is in no way associated with Microsoft.

Website designed by Astrava Solutions Ltd.