How To Lock Formatting In Excel But Allow Data Entry

In some cases, yous may demand to protect your excel sheet but at the same fourth dimension put data into the sheet. In Microsoft Excel, you tin exercise this work easily. The following article is near how you can protect excel cells but allow data entry in a worksheet.

Download Practice Workbook

Download this exercise workbook to exercise while you are reading this commodity.

2 Unproblematic Methods to Protect Excel Cells But Allow Data Entry

I have shared two elementary methods to protect excel cells but allow data entry in excel.

Suppose nosotros have a dataset of some patients' Ages, Sex, and Blood Groups. At present we are going to protect cells from the excel dataset allowing value or text editing.

1. Protect Excel Cells But Allow Information Entry by Protecting Worksheet

In this following method, with the help of protecting a worksheet, y'all tin can protect excel cells at the same moment enter values or data.

Steps:

Select cells to allow data entry.
Printing Ctrl+i simultaneously to open the format cells window.

In the "Format Cells" window get to "Protection".
Now uncheck the "Locked" selection by clicking on it.
Press OK to keep.

Now the cells are no more locked.
While selecting the cells go to "Review" from the home ribbon and from options click on "Protect Sheet".

A new window volition open asking for a countersign to protect the sheet.
Type a countersign of your option and click OK to continue.

After clicking OK another window will appear to confirm the given password.
Blazon the same password once again and press OK.

At present check the result in the dataset. You volition get your cells protected merely at the aforementioned time, yous volition be enabled with entering data in those cells.

If y'all try to edit data outside the selected range you won't be able to exercise that. The following pedagogy will pop up.

Read More: Excel VBA to Protect Range of Cells (three Examples)

Similar Readings

How to Unlock Cells in Excel When Scrolling (4 Easy Ways)
Excel VBA to Lock Cells without Protecting Sheet (4 Ideal Examples)
How to Protect Cells without Protecting Canvas in Excel (3 Ways)

2. Run a VBA Code to Protect Excel Cells But Permit Data Entry

VBA (Visual Basic Applications) is the programming language of excel and other office programs. VBA is very effective and at the same time, it's very efficient when it comes to repetitive solutions to formatting and correcting problems. In this method, I am describing how yous tin protect excel cells with assuasive entering value or text past running a VBA code.

Steps:

Select your sheet and press Alt+F11 to open the "Microsoft Visual Basic for Applications" window to run a VBA

Select your canvas from the left column past double-clicking.
Enter the lawmaking from the following into the module-

          Sub AllowDataEntryOnly()     ActiveSheet.Protect Userinterfaceonly:=True, AllowFiltering:=True     Range("B4:F14").Locked = Fake         MsgBox "Only allow data entry in range B4:F14" End Sub

Click Run.

You will become a confirmation of your selected sheet which will be protected allowing information entry in the selected range.

Now if you want to add together information in cells other than the B4:F14 range, you will get a alarm. That means, nosotros take protected our cells merely immune information entry.

Read More: Lock a Cell after Data Entry Using Excel VBA with Message Box Notification Before Locking

Things to Recall

Y'all might also need to unprotect the sheet afterwards protecting the file with a password. To practice that go to "Review" and select "Unprotect Sheet". Now blazon the password and your canvass will be unprotected.

Conclusion

In this commodity, I accept tried to cover the simple and quickest ways of protecting excel cells while allowing data entry. Take a tour of the practice workbook and download the file to do past yourself. Promise you observe it useful. Please inform the states in the comment section almost your experience. We, the ExcelDemy team, are ever responsive to your queries. Stay tuned and go along learning.

How to Protect Excel Cells from Deletion (three Handy Methods)
Protect Certain Cells in Excel (two Suitable Ways)
How to Lock Cell Value One time Calculated in Excel (iii Unproblematic Ways)