Chapter 9Protecting Financial Data |
| About This Chapter |
This chapter reviews all of the various options you can use to protect your financial information data and files, and includes the following sections:
F Overview, page 144, explains the need for protection and for higher security levels when using Excel worksheets to store financial data.
F Security, page 145, describes the new Security tab in Excel 2002 and later, as well as how to add a password or digital signature to a file.
F Protecting Workbooks, page 148, describes how to protect a workbook so that its structure cannot be changed, as well as how to hide worksheets.
F Protecting Worksheets/Cells, page 151, describes how to protect the content in cells from being changed, as well as hide the formulas in them from being viewed.
F Protecting Cells That Contain Formulas or Text, page 156, describes how to protect only those cells in the workbook that contain text or formulas.
F Allowing Multiple Users to Edit Ranges, page 159, describes a new functionality in Excel 2002 and later, in which you can enable multiple users to update data in a well‑defined and private area of the worksheet.
Security
A new Security tab, shown below, has been added to the Options window in Excel 2002 and later. It contains a range of options that will help you secure and protect workbooks and the data they contain.
Figure 9‑1: Security Tab
Ø To open the Security tab:
F From the Tools menu, select Options, and then select the Security tab.
Using a Password to Prevent Opening a Workbook
Ø To use a password to prevent opening a workbook:
1. From the File menu, select Save as.
2. In Excel 97, select Options. From Excel version 2000 and later, select Tools and then General Options.
3. Type the password in the Password to open text box and in Password to modify text box, and then click OK.
|
|
NOTE: If you forget your password, do not despair. In exchange for a fee, there are a number of software manufacturers on the Web who can provide you with a password identification program (this will also cancel password protection). Use any Internet search engine to search for the phrase Excel password, and click any of the results that appear. |
Sending Password-protected Excel Files
The procedure below explains how to send a password-protected Excel 2002 or Excel 2003 file to users of earlier Excel versions.
Ø To send a password-protected file:
1. From the Tools menu (in Excel 2002 and later), select Options, and then select the Security tab.
2. Click Advanced, and select Office 97/2000 Compatible.
Using a Digital Signature
Ø To set a digital signature:
F From the Tools menu (in Excel 2002 and later), select Options, and then select the Security tab and add a digital signature.
Protecting Workbooks
Protecting a workbook prevents the structure from being changed. By assigning a password to a workbook, you prevent worksheets from being deleted, new worksheets from being inserted and hidden worksheets from being unhidden.
Ø To protect a workbook:
1. From the Tools menu, select Protection, and then Protect Workbook.
2. Type a password in the Password text box, and click OK. The Confirm Password window appears.
3. Confirm the password in the appropriate text box and click OK.
1. From the Tools menu, select Protection, and then Unprotect Workbook.
2. Type the password in the Password text box, and click OK.
Ø To hide/unhide worksheets:
F Select the worksheet you want to hide/unhide and, from the Format menu, select Sheet, and then Hide or Unhide, as required.
Preventing Hidden Worksheets from Appearing
Ø To prevent hidden worksheets from appearing in the Unhide window:
1. Press <Alt+F11> to open the Visual Basic Editor.
2. Under VBA Project in the top-left pane, select the worksheet for which you want to hide.
3. Press <F4> or click the Properties Window
icon to open the worksheet'sProperties
window.
4. In the Visible row, select xlSheetVeryHidden.
5. Press <Alt+F4> to close the Visual Basic Editor and return to Excel.
Protecting Worksheets/Cells
You can protect the content in the cells from being changed, as well as hide the formulas in them from being viewed.
Ø To lock or hide a cell:
1. Open the Format Cells window by either pressing <Ctrl+1> or by right-clicking the cell and selecting Format Cells from the displayed menu.
2. Select the Protection tab. By default, Excel automatically selects the Locked check box and does not the Hidden check box. If you protect your worksheet, remember to unlock any cells you want to be unprotected.
There are two conditions for protecting cells:
F The cell's Locked check box is selected.
F The worksheet is protected.
There are two conditions for hiding text and formulas in the Formula bar:
F The cell's Hidden check box is selected.
F The worksheet is protected.
Protecting a Worksheet
In Excel 2002 and later, the Protect Sheet window (shown in Figure 9‑3) enables you to select the operations you want users to be able to perform.
Figure 9‑3: Protect Sheet Window
Ø To protect a worksheet:
1. From the Tools menu, select Protection, and then Protect Sheet.
2. In the Allow all users of this worksheet to text box, select the check boxes for the operations you want to leave unprotected.
In previous versions of Excel, all operations in the Protect Sheet window were automatically protected, and you could not select them individually.
|
|
TIP: To move between unprotected cells in a protected worksheet, press <Tab>. |
Protecting Data by Hiding Rows and Columns
The restricted range of movement in the worksheet displayed in Figure 9‑4 is A1:E14. Column F and onward, and row 15 and downward are hidden.
Figure 9‑4: Worksheet with a Restricted Range of Movement
Ø To hide rows and columns:
1. Select column F.
2. Press <Ctrl+Shift+Right Arrow>.
3. Right-click, and select Hide from the displayed menu.
4. Select row 15.
5. Press <Ctrl+Shift+Down Arrow>.
6. Right-click, and select Hide from the displayed menu.
1. Select row 14.
2. Point the cursor at the row number, and then click and drag it slightly downward (this selects the hidden rows).
3. Right-click and select Unhide from the displayed menu.
4. Repeat steps 1 through 3 for the columns, as required.
Preventing Movement in Protected Areas
You can divide the area of a worksheet containing data into two parts:
F An area where movement is unrestricted (the scroll area)
F An area where movement is restricted, that is, protected.
You can set the scroll area in a worksheet either by adding Scroll Area code line to a macro, as described below, or by making a change in the worksheet's Properties window in the Visual Basic Editor, as described below.
Ø To change the scroll area using a macro:
F Use the following statement to set the scroll area so that the user cannot activate any cells outside A1:C15:
Sheets(1).ScrollArea = "A1:C15"
To set scrolling back to normal, use the following statement:
Sheets(1).ScrollArea = ""
Ø To change th e scroll area using the Visual Basic Editor:
1. Press <Alt+F11> to open the Visual Basic Editor.
2. Under VBA Project in the top-left pane, select the worksheet for which you want to change the scroll area.
3.
Press <F4> or click the Properties
Window
icon to open the worksheet'sProperties
window.
4. In the ScrollArea row, type the reference of the range you want to set as the scroll area (for example, $A$1:$C$15), as shown below.
5. Press <Alt+F4> to close the Visual Basic Editor and return to Excel.
You can now perform any action in the range A1:C15. In the rest of the worksheet, however, you can view the cells, but not move or scroll between them.
Protecting Cells That Contain Formulas or Text
Ø To unlock all cells:
1. Select all the cells in the worksheet by pressing <Ctrl+A>.
2. Open the Format Cells window by pressing <Ctrl+1>.
3. Select the Protection tab.
4. Deselect the Locked check box, and then click OK.
Step 2: Selecting Cells with Formulas or Text
Ø To select cells with formulas or text:
1. Press <F5>. The Go To window appears.
2. Click Special. The Go To Specialwindow appears, as shown.
3. To choose cells with formulas, select the Formulas option button; to choose cells with text, select the Constants option button.
4. Click OK.
Step 3: Locking the Cells
Ø To lock the cells:
1. With the cells containing formulas or text only selected (as explained in the previous procedure), open the Format Cells window by pressing <Ctrl+1>.
2. Select the Protection tab.
3. Select the Locked check box, and then click OK.
Step 4: Protecting the Worksheet
Locking cells has no effect unless the worksheet is protected as well.
Ø To protect the worksheet:
1. From the Tools menu, select Protection and then Protect Sheet. The Protect Sheet window appears.
2. Click OK (a password is optional).
Allowing Multiple Users to Edit Ranges
Excel 2002 and later have an advanced option that enables multiple users (when working on a network, for example) to update data in a well‑defined and private area. Each workbook user is allotted a range in the worksheet with a unique password.
Ø To allow multiple users:
F From the Tools menu, select Protection and then Allow Users to Edit Ranges. The following window appears:
