How to lock formating of excel Cell and Allow only data entry or copy

I would like to enable standard format in excel but users should not allow them to change the format, But Users able to copy the data into the cells or enter the data into the cell. how can define this in excel
Nagesh A SSCCM AdministratorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Danny RobinsonIT Support ConsultantCommented:
Hi ,

You can protect the sheet by allowing only actions you set.

Go to  
Review > Protect sheet > 
here you have the options you require, which you can lock/unlock with a password.  You also need to go to the cells in question right click, format cells, then protection and uncheck locked.

Hope this helps.
0
Nagesh A SSCCM AdministratorAuthor Commented:
I tried that, entire sheet is getting protected. i want to allow users to enter the data but they should not change the format of the cell
0
ProfessorJimJamCommented:
First  1-   click on the top corner of the worksheet to select all cells in worksheet  see the second image below.

2 while all cells are selected right click and  click on "Format Cells"  a new small dilogbox opens

3-  then on "protection" tab click and then unmark the "Locked" and click ok

4- then  on the "Review" Tab of Excel Ribbon and then click "Protect Sheet" as small window will open and then tick mark all of the options and only leave un-ticked Format Cells and Format Columns and Format Rows then also put a password and then reconfirm the password and that is it.

2015-05-26-17-50-00-Microsoft-Excel---Bo2015-05-26-17-50-38-Microsoft-Excel---Bo
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

Danny RobinsonIT Support ConsultantCommented:
ProfessorJimJam stated my solution also and explained it better haha. Yeah you need to turn off locked on the formatted cells yourself

Capture.PNG
then protect the workbook with a password then send to your users.
0
Nagesh A SSCCM AdministratorAuthor Commented:
I did the same, I set the A1 to A10 cells should accept only numbers, B1 to B12 accepts only Dates, C1 to C12 accepts only Time. Now if enter text in A1 to A10 it accepts, It should not be the case. If I enter the number in B1 to B12 it changes to the date format but it still accepts the text. How can fix the cells only to accept the number or text only
0
Danny RobinsonIT Support ConsultantCommented:
Hi ,

you need to use data validation for this.
You can even put custom error message
Thanks

Capture.PNG
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ProfessorJimJamCommented:
@asnagesh

now this is totally a different question you are asking now.  

your question at start refers how to lock cells from being formatted.  now you ask a different question which is completely different.

just for the sake of the question having the title, "How to lock formating of excel Cell and Allow only data entry or copy"  i would have preferred closing this question and opening a new one,. but here you go anyway.


what you are referring now is a Data Validation select the range which you want to put validation, then go to Data and Data validation select Date for , time for time and whole number for numbers see the screenshot  2015-05-26-18-16-05-Microsoft-Excel---Bo
0
Nagesh A SSCCM AdministratorAuthor Commented:
it worked well
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.