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.

Table of Contents hide

Download Practice Workbook

4 Suitable Ways to Protect Selected Cells in Excel

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

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

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

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

Things to Remember

Conclusion

Download Practice Workbook

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

Protect Selected Cells.xlsm

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.

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.

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.

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.

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.

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.

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

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

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

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

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

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).

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

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

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.

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.

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.