Protect Excel Sheet but Allow Data Entry (2 Handy Approaches)

Get FREE Advanced Excel Exercises with Solutions!

If you wish to protect your Excel worksheet but at the same time allow data entry into your Worksheet, MS Excel provides this facility to do this. In this article, you will learn 2 handy approaches to protect an Excel sheet but allow data entry.


Protect Excel Sheet but Allow Data Entry: 2 Handy Approaches

Let’s introduce our dataset first. We have data on some people’s first names, last names, states, and salaries. Our goal is to protect this Excel sheet but allow data entry.

2 Handy Approaches to Protect Excel Sheet But Allow Data Entry


1. Utilizing Protection Tab from Format Cells Option

To protect the Excel sheet but allow data entry, you have to unlock the cells first, then select the cells to be locked, and then you need to protect the worksheet. Please follow the steps below.

📌 Steps:

  • First, press CTRL+A to select the entire worksheet.

Protect Excel Sheet But Allow Data Entry Utilizing Protection Tab from Format Cells Option

  • Now, press CTRL+1, and a Format Cells window will appear. Under Protect section, unmark the Locked checkbox, and finally, press OK.

Protect Excel Sheet But Allow Data Entry Utilizing Protection Tab from Format Cells Option

  • Again, select the cells you want to protect by password. Then, again press CTRL+1. A Format Cells window will appear.

Protect Excel Sheet But Allow Data Entry Utilizing Protection Tab from Format Cells Option

  • This time, check the Locked option and then click on OK.

  • Then, go to the Review tab. Under Protect group, click on Protect Sheet. A Protect window will pop up.

Protect Excel Sheet But Allow Data Entry Utilizing Protection Tab from Format Cells Option

  • In the Password box, type your convenient password, and without doing anything more, press OK.

  • A Confirm Password dialog box will appear. Reenter your password here. Then, press OK.

  • Now, we can enter data into the cells except C4:C12 and D4:D12. For example, you can easily edit the cell F7.

  • The cells C4:C12 and D4:D12 are password protected. Now, if you want to enter data in these cells, you will need to enter the password first.

Protect Excel Sheet But Allow Data Entry Utilizing Protection Tab from Format Cells Option

Here is the gif for your better understanding of the result of this method.

Protect Excel Sheet But Allow Data Entry Utilizing Protection Tab from Format Cells Option

Read More: How to Protect Excel Sheet from Deleting


2. Apply an Excel VBA Code

VBA (Visual Basic Applications) is a quite effective tool in case of repetitive solutions to various Excel problems. You can protect your Excel sheets but allow entering data in the cells by running a VBA code. Just follow the steps below to apply this amazing tool.

Steps:

  • Select your worksheet and press the hotkey ALT+F11. It will open the “Microsoft Visual Basic for Applications” window.

Apply an Excel VBA Code

  • Or, manually click on Developer tab > Visual Basic > Insert > Module. A module dialog box will appear.

Apply an Excel VBA Code

  • Now, copy the following code and paste it into the module box. Finally, click on the Run button as shown below.
Sub ProtectSheetButAllowDataEntry()
 ActiveSheet.Protect Userinterfaceonly:=True, AllowFiltering:=True
Range("B4:F12").Locked = False
MsgBox "Sheet is Protected but data entry is allowed"
End Sub

Apply an Excel VBA Code

  • Now your worksheet is protected. However, you can enter data in the cell range B4:F12.


Download Practice Workbook

Download the following Excel file for your practice. Password: 2022.


Conclusion

In this tutorial, I have discussed 2 handy approaches to protect Excel sheet but allow data entry. I hope you found this article helpful. Please, drop comments, suggestions, or queries if you have any in the comment section below.


Related Articles


<< Go Back to Protect Excel Sheet | 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.
Hafizul Islam
Hafizul Islam

Hafizul Islam is an excellent marine engineer who loves working with Excel and diving into VBA programming. For him, programming is like a superhero tool that saves time when dealing with data, files, and the internet. His skills go beyond the basics, including Rhino3D, Maxsurf C++, AutoCAD, Deep Neural Networks, and Machine Learning. He got his B.Sc in Naval Architecture & Marine Engineering from BUET, and now he's switched gears, working as a content developer. In this role,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo