Nagesh A S
asked on
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
ASKER
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
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.
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.
ProfessorJimJam stated my solution also and explained it better haha. Yeah you need to turn off locked on the formatted cells yourself
then protect the workbook with a password then send to your users.
then protect the workbook with a password then send to your users.
ASKER
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
@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
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
ASKER
it worked well
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.