Protect sheets and files

While Bahadur Singh is still in Himalayas let us ask a question to us…
How confidential are the excel files that you have on your PC? Ask this question to any of our friend and he will reply saying very confidential. What do all of us use excel for – preparation of statement of total income, preparation of final accounts, maintaining contact list, etc. If all these are so important than how many of us ensure that they stay protected from any invasion or information leakage. How many of us use various protection features provided by excel. In this article we will discuss about various ways and means one can protect a particular excel file or some data from an excel file.

Password protect an excel file:
An excel file can be password protected to open or to modify. To do the same go to File >> Save As >> Options… (Button available on the right side). This will provide you with a small window as shown below.

There are two options available as seen above.
1. Password to open: In case one wants to avoid any unknown person from opening this file, this option should be used. Enter the password in first option and press OK. A window to confirm the password will appear. Repeat the same password and press OK. Now save the file. This will provide a password protection to open this file. Henceforth whenever one wants to open this file, the system will prompt for a password request. Only on entering correct password will the file open.

2. Password to modify: This option should be used for files that are generally referred to. For example in case of a practicing CA there would be a file indicating rate table for NSC. Whenever he is preparing a statement of total income he would need to refer this NSC rate table file. However this file is updated rarely. Hence if one wants to protect it from modification by any unknown person, this option will be helpful. To activate this option enter password in the second box above. A window to confirm the password will appear. Repeat the same password and press OK. Henceforth whenever one wants to open this file, the system will prompt for a password request. However unlike option 1 above, even if one does not know the password he will still be able to open the file but only in the read-only mode i.e. he will not be able to modify the file. Hence modification of this file is possible only by a person who know the password.

3. Read-only recommended: The check-box available at the bottom of the window is also very useful. If one does not want to use password, as there is always a probability that one may forget the password, this option should be tried. On activating this option by checking the box whenever this file is opened system will prompt a message as shown below:
Here if one wants to do some modification in this file than press No else Yes. This is important for files that are not confidential but are referred to very often and whenever we open that file there is a probability that we may make some error and save it. To avoid this, reference file like NSC rate table should be activated with this option.

Password protect a worksheet:
In the above example we saw how to protect a file. However at time we would like to protect a sheet and not the entire file. Example, in a file for tax return, one sheet has statement of total income and another sheet has capital account and balance sheet. There could be a need to protect the sheet for statement of total income and keep all other sheet open. In such a case go to Tools >> Protection >> Unprotect Sheet… Window as shown below will pop-up.
Enter the password or leave it blank and press OK. This will make this sheet un-editable and any effort to modify the sheet will give an error message. Hence this will not allow any kind of data entry in the sheet where this setting is activated. If one now wants to remove this protection go to Tools >> Protection >> Unprotect Sheet… the same window will appear. Enter the password that was entered earlier and press OK. This will un-protect the worksheet.

Protecting a cell and a formula!!!
In all the above examples we saw protection of file or sheet, however at times there is a need to protect a few cells or keep open few cells in a particular sheet. Example, while preparing statement of total income one may think of protecting all cells except the tax rate. To do the same first one needs to un-protect the sheet. After un-protecting the worksheet go to the cell that has to be kept open for editing. Tax rate in our example above. Go to Format >> Cells >>Protection (Last Tab). A window as shown below will appear:





By default the Locked option is always checked. Uncheck this option and press OK. Now protect the worksheet as shown earlier. Try editing any cell in the worksheet. All the cells except the cell that was un-locked (tax rate) have been protected. Modification of any cell will not be allowed except tax rate.

In the above window there is one more option for hidden. This is useful to hide the formulas that are written in the cell. This is true in case a complex model is being built in excel and one does not want to share the formula that has been used in a particular cell. In such a case go to Format >> Cell >> Protection and check the Hidden checkbox. Now protect the worksheet with Tools >> Protection >> Protect Worksheet. The formula in the cell that has been formatted will become invisible!!!