Protect Excel Cells But Allow Data Entry (2 Quick Methods)

Get FREE Advanced Excel Exercises with Solutions!

In some cases, you may need to protect your Excel sheet but at the same time put data into the sheet. In Microsoft Excel, you can do this work easily. The following article is about how you can protect Excel cells but allow data entry in a worksheet.


How to Protect Excel Cells But Allow Data Entry: 2 Simple Methods

I have shared 2 simple methods to protect Excel cells but allow data entry in Excel.

Suppose we have a dataset of some patients’ Ages, Sex, and Blood Groups. Now we are going to protect cells from the Excel dataset allowing value or text editing.

Protect Excel Cells But Allow Data Entry


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

In this method, with the help of protecting a worksheet, you can protect Excel cells and at the same moment enter values or data.

Steps:

  • Select cells to allow data entry.
  • Press Ctrl+1 simultaneously to open the format cells window.

Protect Excel sheets by protecting worksheet

  • In the “Format Cells” window go to “Protection”.
  • Now uncheck the “Locked” option by clicking on it.
  • Press OK to continue.

 by Protecting Worksheet

  • 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 will open asking for a password to protect the sheet.
  • Type a password of your choice and click OK to continue.

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

  • Now check the result in the dataset. You will get your cells protected but at the same time, you will be enabled with entering data in those cells.

Protect Excel Cells But Allow Data Entry by Protecting Worksheet

  • If you try to edit data outside the selected range you won’t be able to do that. The following instruction will pop up.

Protect Excel Cells But Allow Data Entry by Protecting Worksheet

Read More: How to Protect Excel Cells with Password


2. Run a VBA Code to Protect Excel Cells But Allow 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 you can protect Excel cells with allowing entering value or text by 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 code.

Run a VBA Code to Protect Excel Cells But Allow Data Entry

  • Select your sheet from the left column by double-clicking.
  • Enter the code from the following into the module-
Sub AllowDataEntryOnly()
    ActiveSheet.Protect Userinterfaceonly:=True, AllowFiltering:=True
    Range("B4:F14").Locked = False
        MsgBox "Only allow data entry in range B4:F14"
End Sub
  • Click Run.

Run a VBA Code to Protect Excel Cells But Allow Data Entry

  • You will get a confirmation of your selected sheet which will be protected allowing data entry in the selected range.

  • Now if you want to add data in cells other than the B4:F14 range, you will get a warning. That means, we have protected our cells but allowed data entry.

Protect Excel Cells But Allow Data Entry

Read More: How to Protect Excel Cells from Deletion


Things to Remember

  • You might also need to unprotect the sheet after protecting the file with a password. To do that go to “Review” and select “Unprotect Sheet”. Now type the password and your sheet will be unprotected.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


Conclusion

In this article, I have 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 practice by yourself. Hope you find it useful. Please inform us in the comment section about your experience. We are always responsive to your queries. Stay tuned and keep learning.


Related Articles


<< Go Back to Protect Excel Cells | Excel Protect | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Wasim Akram
Wasim Akram

Wasim Akram holds a BSc degree in Industrial and Production Engineering from Ahsanullah University of Science and Technology. Over the past 2 years, he has been actively contributing to the ExcelDemy project, where he has authored more than 150 articles. Now, he is working as an Excel VBA and Content Developer. He likes learning new things about Microsoft Office, especially Excel VBA, Power Query, Data Analysis, and Excel Statistics. He is also very interested in machine learning and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo