Question

Locked

Protect few columns in the spread sheet ( Excel )

By dwarak4friends ·
Hi Team,

Could you please help me in protecting Few Columns in the spread sheet ( Excel Sheet ).. For example I am doing a project ( sales ) one.. Analysts will enter/modify data which is irrelavant to them ( by not knowing the impact ). Since it is shared i need to protect few Columns.. It would be great if you could answer this question

This conversation is currently closed to new comments.

4 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Answers

Collapse -

Protect columns in the spread sheet of Excel .

Method 1: "Full Protection"
In this scenario, the intent is to protect almost the entire worksheet. The user will be allowed to enter information in only designated (usually colored-coded) cells.

Method 2: "Partial Protection"
The interest here is in protecting the formulas only. The user has the ability to add data to other areas within the worksheet.
Let's step through an example of applying worksheet protection using Method 1. Your workbook must be saved before the protections will take affect.

Select the cells that you want to remain unprotected. To select nonadjacent cells, hold down the CTRL key (Apple key on a Mac) and click the cells that are to remain unprotected.
On the Format menu, choose Cells, and then click the Protection tab. Clear the Locked check box and click OK.
On the Tools menu, choose Protection, Protect Sheet. (At this point, you have the option of entering a password that must be provided before you can unprotect your worksheet.)
In the Allow all users of this worksheet to list, select the elements that you want users to be able to change.
Click OK to enable worksheet protection.
The formulas and other cells in the worksheet are now protected from tampering. If the user tries to enter data into a protected cell, they will receive a warning message:

Now let's step through Method 2. Here the goal is to protect formulas only; changes and additions to the rest of the worksheet are allowed. Your workbook must be saved before the protections will take affect.

Select the entire worksheet by clicking the Select All button (the gray rectangle in the upper-left corner of the worksheet where row 1 and column A headings intersect.) You can also press CTRL+A (Apple+A on a Mac) to select the entire worksheet.

Choose Cells from the Format menu, or Press Ctrl-1 (Apple+1 on a Mac).
Click the Protection tab, clear the Locked check box, and click OK.
Note: This has just unprotected every cell in the worksheet. The next step will be to protect only the cells with formulas in them.
On the Edit menu, choose Go To, or press F5. Click the Special button.
Click the Formulas option, and then make sure the four check boxes below it are selected. Click OK. This forces Excel to select the cells that contain formulas.
Press Ctrl+1 (Apple+1 on a Mac) again and click the Protection tab. This time we'll check the Locked check box. Click OK to complete the operation. Now the cells containing formulas will be locked and the cells containing text and values will be unlocked.

Finally, open the Tools menu and select Protection, Protect Sheet. (At this point, you have the option of entering a password that must be provided before you can unprotect your worksheet.) Click OK to enable worksheet protection.

To remove the protection from your worksheet, choose the Tools, Protection, Unprotect Sheet command. If you have assigned a protection password, you will have to enter it at this point.

Make sure you remember your protection passwords, or write them down and keep them in a secure place. If you lose a password, you cannot gain access to the protected elements on the worksheet.

When several worksheets within a workbook need to be protected, follow the above scenarios on each worksheet and then select Tools, Protection, Protect Workbook.

Please post back if you have any more problems or questions.

Collapse -

Hi peconet

by dwarak4friends In reply to Protect columns in the sp ...

Thats absolutely working fine.. still i have one question to you.. Hence this is protected..I want also the users if they can change anything..or they are not suppose to change that.. I just want that procedure as well

Collapse -

Are you saying.....

by ThumbsUp2 In reply to Hi peconet

You want some users to be able to change the data and others not to be able to change anything?

Collapse -

Protecting sheets?

by dr_evil In reply to Hi peconet

If I rightly think you are saying that you dont wont users to change values this would include formulae?

peconet summed it up but basicly if you dont wont users to alter cells or formulae then un-check the options on protection for users to select cells.
This way they not only cant change the value but they wont be able to read the formulae in the formulae bar when trying to select.

Back to Software Forum
4 total posts (Page 1 of 1)  

Related Discussions

Related Forums