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

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.


Download Practice Workbook

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


2 Handy Approaches to Protect Excel Sheet but Allow Data Entry

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 cells 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 with Password (3 Quick Approaches)


Similar Readings


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.

Read More: Excel VBA to Protect Sheet but Allow to Select Locked Cells (2 Examples)


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. You can visit our website ExcelDemy to learn more Excel-related content. Please, drop comments, suggestions, or queries if you have any in the comment section below.


Related Articles

Hafiz Islam
We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo