• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Protecting worksheets

LadyBlack

New Member
Please can some let me know an easy way to protect cells within a worksheet?


I am currently designing a spreadsheet which needs to be passed around several people, and it would be easier if there were no password for them to remember. I'm sure that there was a way of locking them without any need to protect the entire Spreadsheet/worksheet.


I am afraid of people accidently over writing the cells or deleting them.


Many thanks
 

Hui

Excel Ninja
Staff member
LadyBlack

quickest way is to have a read of

http://chandoo.org/wp/2009/11/03/make-better-excel-sheets/
 

LadyBlack

New Member
Thank you Hui, unfortunately the most important part, protecting the cells, advises exactly the same thing on Microsoft Help. It says to use a password, which I really don't want to do, to prevent the whole "What was the password again? Oh, she's on holiday and we can't remember it" thing. I have hidden all the stuff that people don't need to see, but unfortunately the formulas are in cells which display the total which everyone wants to see, so there is not the option of hiding them. I could move them to another row and then hide that, but I was trying to find a solution which did not involve adding more rows.
 

Hui

Excel Ninja
Staff member
LadyBlack


There are a few levels of protection in Excel and you may be using the wrong level

From what you've said You should try the following:


1. User Editable cells

Select all the cells that users are allowed to edit and select Format Cells, Unclick Locked and Hidden on the Protection Tab, generally have a colored background of the cells so thatt people are aware that they are input cells and hence editable.


2. User Viewable Cells (But allow to see formulas, without editing)

Select all the cells where users see the result but aren't allowed to edit the formulas

Select Format Cells, Select Locked and Unselect Hidden on the Protection Tab


3. User Viewable (But not see formulas)

Select all the cells where users can see results, but users aren't allowed to edit or see the formulas

Select Format Cells, Select Locked and Select Hidden on the Protection Tab


Now goto Tools, Protection, Protect Sheet (xl2003)


The above changes to Protect and Hidden cells are only applied when you apply this dialogue

Enable Protect worksheet and contents of Locked Cells


Type in a password


All the check boxes below allow you to select what a user can / cant do.

Enable or disable whatever level of protection you need

Definately allow users to select unlocked cells, if you want to allow them to edit data entry areas.


Click Ok and Save the worksheet


Users can now:


Open and edit only the cells you allowed them to in Step 1 above

They can see answers and formulas from step 2, but cant change formulas

They can see answers but not see the formulas from Step 3 above.


From what you've said I would NOT apply a Workbook password.

That stops people opening or allows open as read only, and yes, If your away they will neeed the password.


I Hope the above helps


Hui...
 

TessaES

New Member
Most of the time when I protect a sheet, I do not fill in a password. That means anyone can unlock the sheet and change things if they want to (at least if they try unlocking and don't think "mm, it's is protected and I don't know the password"). It does protect the sheet against accidental errors: people who don't realise they are typing in the wrong cell etc.
 
Top