subject: How to assign different passwords to separate ranges in Excel worksheets [print this page]
QUESTION:
I am a finance officer in a bank. My job is to generate data into an excel worksheet and upload it into the intranet for various supervisors to view. Today, my manager ask me whether is it possible to assign a password to various supervisors so that he or she can only view that portion of the excel data cells dedicated for them. Anyone can help me, thanks in advance.
ANSWER:
Since this is a fairly involved procedure, I will guide you to in the Microsoft Knowledge Base that spells out how to do it.
Summary:
Since after the version of Microsoft Excel 2002, we can now use passwords to protect specific ranges in our worksheets. This is a progress from earlier versions of Excel, in which one password applies to the entire worksheet, which might have several protected ranges. In addition, if you use Windows 2000, you can apply group-level passwords and user-level passwords to different ranges.
How to assign separate passwords:
To assign different passwords to separate ranges in a worksheet, follow these steps:
Step 1: Open your Excel file, click the Tools menu, point to Protection, and then click Allow Users to Edit Ranges.
Note If you are running Microsoft Office Excel 2007, click Allow Users to Edit Ranges on the Review menu in the Changes group.
Step 2: In the Allow Users to Edit Ranges dialog box, click New.
Step 3: In the New Range dialog box, click the Collapse Dialog button. Select the range you want to protected and then click the Collapse Dialog button again.
Step 4: In the Range password box, type the password, then type it again in the Confirm Password dialog box, and then click OK.
Step 5: Repeat steps 2 through 4, assign password to the other ranges.
Step 6: In the Allow Users to Edit Ranges dialog box, click Protect sheet. In the Password to unprotect sheet box, type ranger, and then click OK two times. When prompted, retype the password.
From now on, your worksheet's separate ranges are password protected by different passwords. If you select the password protected range and start type the data, then Unlock Range dialog box will appears. Type the password in the Enter the password to change this cell box, and then press ENTER. You can now enter data.
When you apply different passwords to separate ranges in this way, a range that has been unlocked remains unlocked until the workbook is closed. When you unlock another range, you do not relock the first range. Likewise, when you save a worksheet, you do not relock a range.
You can use existing range names to identify cells that are to be protected with passwords, but if you do, Excel converts any relative references in the existing name definitions to absolute references. Because this may not give you the results you intended, it is preferable to use the Collapse Dialog button to select the cells, as described earlier in this article.
Note these aspects of applying passwords to specific ranges:
Passwords have no effect unless the worksheet itself is protected, but if you password protected your worksheet but now you have forgotten the password, what can you doThere are several password recovery softwares available online which can help you in removing the passwords but if you are searching the easy-to-use and quick one, then Excel password recovery 5.0 is your best choice, for further information: http://www.recoverlostpassword.com/products/excelpasswordrecovery.html
How to assign different passwords to separate ranges in Excel worksheets