Controlling Data Entry in Cells

Hello Narinda,

Hope you enjoyed your Microsoft Excel Advanced course with Best STL.

Thank you for your question regarding controlling data entry into cells by using sheet protection.

Please know that all cells in a worksheet are locked by default, but can be used by anyone if the sheet is NOT protected. So, what you have to decide is this:

Do I have more cells for the users to work on and only a few for the main editor or is it the other way around. The answer to this is important because there are two ways you can go about adding protection.

1) Let's assume that you only want the main editor to edit formulas and all other data can be accessed by other users. Then you must select the entire worksheet and unlock all cells by going to the Home ribbon and opening the Format Cells dialogue box, Protection tab and deselect the Locked option.

After that from the Home ribbon in the Editing group click Find & Select then Go to Special. In the Go to Special dialogue box select Formulas and click OK. (You can of course include some other cells too such as headings etc.)Then you must Lock all these selected cells using the Format Cell > Protection tab.

Now the final step is to go to the Review ribbon and select Protect sheet. In this dialogue box you will be required to provide your users with permissions. After you have done this enter a password click OK then confirm the password and OK that.

The sheet is now protected and only the person who has access to the password may edit the whole sheet.

2) The second option would be used if there were only a few cells which you want to give access to a some users to edit. In this case only select those cells and unlock them. Then protect the sheet as described above.

Try the above suggestions to see which will be suitable for your needs.

I hope this resolves your question. If it has, please mark this question as resolved.

If you require further assistance, please reply to this post. Or perhaps you have another Microsoft Office question?

Have a great day.
Regards,

Rodney
Microsoft Office Specialist Trainer