2 Easy Ways to Protect Excel Sheet but Allow Data Entry

In this article, I’ll show you three excellent methods on how to protect Excel sheet but allow data entry in Excel. Furthermore, these strategies will allow you to solve the problem that brought you here. However, you will learn some important Excel features and functions in this post that will come in handy for any Excel job.

📁 Download Excel File

The sample worksheet that was used during the discussion is available to download for free right here.

Learn to Protect Excel Sheet but Allow Data Entry with These 2 Methods

Here in this article, we will learn how to protect Excel sheet but allow data entry in Excel. To be exact, I’ve provided a total of 3 methods down below. We considered a sales data of a supermarket as a dataset. It contains columns like the product, unit price, quantity, and total. We will try to generate an Excel sheet where employees can only input the quantity of the product.

In this method, I will demonstrate how to protect the Excel sheet but allow data entry in Excel by using the Protection tab. The steps are given below.

⬇️⬇️ STEPS ⬇️⬇️

• First, open the workbook and select the entire worksheet by clicking Ctrl+A.

• Following that click, Ctrl+1 to open the Format cell Or Select Home > Orientation > Format Cell Alignment.
• Then go to the Protection window and uncheck the option Locked and click OK.

• Then select the cell that you want to protect.

• And click Ctrl+1 to open the Format cell window again.
• Mark check the Lock option and click OK.

• Now go to the Review tab and Select Protect > Protect Sheet as I showed in the figure.

• Choose a password in the password box and click OK.

• Again confirm the Password by typing again and clicking OK.

• Now the selected cells of your sheet is protected. You can enter data except for these cells.

• If you try to edit the protected cells Excel will show you this notification.

Accordingly, you can secure an Excel file but can input data.

Finally, we will employ a VBA code to accomplish the same task. The steps are given below.

⬇️⬇️ STEPS ⬇️⬇️

• Go to the Developer tab and choose the Visual Basic option.
• Then go the Insert and choose Module.

• Write down the following VBA code:

Sub Prsheet()
ActiveSheet.Protect Userinterfaceonly:=True, AllowFiltering:=True
Range("D6:D9").Locked = False
MsgBox "Protected Zone"
End Sub

• Run the code by pressing F5.
• You will see the sheet is protected but allows data entry only in the Quantity.

Thus you can secure an Excel file but can input data.

How to Lock Formatting in Excel

Here we will learn how to Lock Formatting using the Format Cell Alignment option in Excel 365. We will use the same dataset as above.

⬇️⬇️ STEPS ⬇️⬇️

• Firstly, open the workbook and select the entire worksheet by clicking Ctrl+A.
• Select Home > Orientation > Format Cell Alignment.
• Or simply click Ctrl+1.
• Go to the Protection
• Uncheck the option Locked.

• Then Select the cell of whom you want to keep Formatting.
• Again click Ctrl+1 and go to the Protection option.
• Then check the Locked and click OK.

• Later go to Review > Protect > Protect Sheet.

• Then select a strong password to protect the worksheet.

• Finally, your Formatted cells are protected.

That’s how you can Lock Formatting in Excel 365.

📄 Important Notes

🖊️  Verify that you’ve employed Absolute Reference when it was required. To get an absolute cell reference, press F4.

🖊️  Always look for ways that are easy to use in different situations.

🖊️  Every time you try to use shortcuts on the keyboard.

📝 Takeaways from This Article

📌  You can protect Excel sheets but allow data entry by locking cell range.

📌 Moreover, you can protect Excel sheets but allow data entry using VBA.

📌  In addition, you can Lock Formatting in Excel 365.

Conclusion

First and foremost, I hope you were able to apply the principles I taught in this Excel lesson on how to protect excel sheet but allow data entry. As you can see, there are numerous ways to accomplish this. However, choose the strategy that is best suited to your situation with care. Most essential, if you become confused in any of the processes, I recommend repeating them. After reviewing the Excel file in the practice workbook I shared above, practice on your own since practice makes perfect. Above all, I sincerely hope you can put it to good use. Please share your thoughts in the comments section about how you felt throughout the article and what we should change or add to make things better for you. However, if you have any Excel-related issues, please leave them in the comments section. The Excelden team is available to answer your inquiries at any time. Finally, keep reading to educate yourself. Please visit our website Excelden.com for further Excel-related articles.