How to Protect Selected Cells in Excel (4 Methods)

While working with Microsoft Excel, sometimes we need to protect our dataset for the convenience of our work. Basically, we may need to protect selected cells for two reasons. One is to lock cells for protecting them from further edits or changes whereas the other reason is to use the locked cells as absolute cell references. Today, in this article, we’ll learn four quick and suitable ways to protect selected cells in Excel effectively with appropriate illustrations.


Download Practice Workbook

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


4 Suitable Ways to Protect Selected Cells in Excel

Let’s say, we have a dataset that contains information about several sales representatives of the Armani group. The delivery and ordered product and the revenue earned by the sales representatives are given in columns D, C, and E respectively. We will protect our dataset by applying the Format Cells command, Quick Access Toolbar, Go To special command, and VBA macros also. Here’s an overview of the dataset for today’s task.

Use the Format Cells Option to Protect Selected Cells in Excel


1. Use the Format Cells Option to Protect Selected Cells in Excel

Undoubtedly, using the Format Cells command to protect selected cells in Excel is an easy task. This is also a time-saving way. Let’s follow the steps below to learn!

Step 1:

  • First of all, select your dataset to protect, then press Ctrl + 1 simultaneously from your keyboard.

Use the Format Cells Option to Protect Selected Cells in Excel

  • Hence, a window named Format Cells will appear in front of you. From that window, firstly select the Locked option from the Protection tab and then press OK.

Step 2:

  • After that, place your cursor on the active sheet name and press right-click on your Mouse. By right-clicking on your Mouse a window pops up. From that window select the Protect Sheet option.

Use the Format Cells Option to Protect Selected Cells in Excel

  • Further, immediately, you’ll see the following dialog box namely Protect Sheet. Here, you need to input the password and press OK.

  • After pressing the OK option, a dialog box named Confirm Password will instantly appear in front of you. Again, you have to reenter your password and press OK as shown in the below screenshot.

Use the Format Cells Option to Protect Selected Cells in Excel

  • Subsequently, your selected cells are locked and protected. For example, if you click on the B5 cell to change or edit, you’ll get an error message which depicts your sheet is “a protected sheet” and you need to enter a password to edit.

Use the Format Cells Option to Protect Selected Cells in Excel

Read More: How to Lock Cells in Excel Formula (2 Easy Ways)


2. Add a Button from Quick Access Toolbar to Protect Selected Cells in Excel

After using the Format Cells command to protect selected cells, we’ll learn here how to protect selected cells by applying the Lock Cell command in the Quick Access Toolbar (QAT). Let’s follow the instructions below.

Steps:

  • For adding the command, click on the icon of Customize Quick Access Toolbar and choose More Commands.

Add a Button from Quick Access Toolbar to Protect Selected Cells in Excel

  • Then, choose All Commands from the drop-down list of the Choose commands from. Besides, select the Lock Cell option as shown in the following picture and click on the Add option. Finally, press OK.

  • Within a short time, you’ll see an icon of the Lock Cell as shown in the following screenshot. After that, select cells from our dataset. Let’s say, we select cells C4 to D14 for our work. Now, just click on the Lock Cell command from the Quick Access Toolbar.

  • After that, from your Preview ribbon, go to,

Preview → Protect Sheet

Add a Button from Quick Access Toolbar to Protect Selected Cells in Excel

  • Hence, a Protect Sheet window pops up. From that window, press OK to protect your dataset.

Add a Button from Quick Access Toolbar to Protect Selected Cells in Excel


Similar Readings


3. Apply the Go To Special Command to Protect Selected Cells in Excel

More importantly, if you may require to lock certain multiple cells that have formulas, you might do that using this method. Let’s follow the steps below.

Steps:

  • First, from your Home tab, go to,

Home → Editing → Find & Select→ Go To Special

Apply the Go To Special Command to Protect Selected Cells in Excel

  • Then, check the circle before the Formulas option and press OK.

  • Shortly, you’ll get the cells having formulas (F5:F14 cell range).

Apply the Go To Special Command to Protect Selected Cells in Excel

  • After that, using the Format Cells discussed in the first method or Lock Cell command discussed in the second method, you may lock the specific cells. Hence, you need to activate the protection of the sheet (step 2 of the first method).

Read More: Select All Cells with Data in Excel (5 Easy Methods)


4. Run a VBA Code to Protect Selected Cells in Excel

Last but not the least, we can protect our selected cells by using the VBA code. For example, we want to lock the cell range B5:E14 in the dataset using the Macro. To do that, follow the instructions below.

Step 1:

  • Firstly, from your Developer tab, go to,

 Developer → Visual Basic

Run a VBA Code to Protect Selected Cells in Excel

  • Secondly, go to,

Insert → Module

Step 2:

  • Now, just copy the following code into the newly created module.
Sub Protect_selected_Cells()
Dim LPassword As String
Range("B5:E14").Select
Selection.Locked = True
LPassword = InputBox("Enter the Password to Protect")
ActiveSheet.Protect Password:=LPassword
End Sub

Run a VBA Code to Protect Selected Cells in Excel

  • After that, run the VBA To do that, go to,

Run → Run Sub/UserForm

Step 3:

  • After running the code, you’ll get a dialog box where you have to enter the password.

Run a VBA Code to Protect Selected Cells in Excel

  • When you enter the password, immediately the selected cells are locked. If you want to check whether the cell is locked or not, just select a cell (e.g. C5 cell) and try to edit. Shortly, you’ll see an error message as depicted in the following screenshot.

Run a VBA Code to Protect Selected Cells in Excel

Read More: How to Lock Certain Cells in Excel (4 Methods)


Things to Remember

👉 If the Preview tab is not visible in your ribbon, then place your cursor on any ribbon and press right-click on your mouse then firstly, select Customize Ribbon, and then select Preview.


Conclusion

I hope all of the suitable methods mentioned above to protect selected cells will now provoke you to apply them in your Excel spreadsheets with more productivity. You are most welcome to feel free to comment if you have any questions or queries.


Related Articles

Tags:

Md. Abdur Rahim Rasel

Md. Abdur Rahim Rasel

Hi! I'm Md. Abdur Rahim Rasel. Welcome to my Profile. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. I have completed my graduation in Naval Architecture and Marine Engineering(NAME) from Bangladesh University of Engineering and Technology(BUET). I have a passion for learning new things with my knowledge through perseverance and hard work.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo