How to Protect Certain Cells in Excel (2 Suitable Ways)

Get FREE Advanced Excel Exercises with Solutions!

This tutorial will illustrate 2 suitable ways, how to protect certain cells in an excel worksheet. While working on Microsoft Excel, we may need to protect our dataset for the sake of efficiency. In this article, weâ€™ll look at how to secure specific cells using both protected and unprotected worksheets. To make you understand better we will illustrate the methods with a unique dataset.

2 Suitable Ways to Protect Certain Cells in Excel

In this tutorial, we will explain 2 suitable ways to protect certain cells in Excel. To demonstrate the 2 methods we will use the following dataset. The dataset contains information about the names of students, their subjects, and marks in corresponding subjects. Using this dataset we will explain all the methods of this article.

1. Protect Certain Cells in Excel by Protecting Worksheet

1.1 Protect Certain Cells with Format Cells Option

Suppose, we want to protect certain cells from our dataset that only contains marks. The cells that we will protect are (D5:D10). We will use the Format Cells option for this. So, follow the below steps to perform this action.

STEPS:

• To begin with, select cells D5 to D10.
• In addition, right-click on the selected cell and click on the option Format Cells.

• A new dialogue box named Format Cells will open. We can also press Ctrl + 1 to open that dialogue box.
• Afterward, go to the Protection tab.
• Furthermore, check the option Locked and click on OK.

• Moreover, go to the Review tab. Click on the ribbon option Protect Sheet.

• So, a new dialogue box appears. Enter a password in the input field and click on OK.

• One more dialogue box appears to confirm our password.
• Then, re-enter the password and click on OK.

• Lastly, click on any cell between the range D5 to D10. A message box like the following image appears. The message shows that the cell that we are using to access is protected.

Read More: Excel VBA to Protect Range of Cells (3 Examples)

1.2 Insert a Button in Quick Access Toolbar to Protect Certain Cells

In the second method, we will insert a button in the Quick Access Toolbar to protect certain cells in excel. We will follow the below steps to do this.

STEPS:

• Next, select the option â€˜More Commandsâ€™.

• A dialogue box named Excel options appears.
• Then, from the â€˜Choose commands fromâ€˜ the drop-down list, select All Commands.
• In addition, as indicated in the image, pick the Lock Cell option and then click the Add After that, press OK.

• As a result, we can see an icon to lock the cell in the Quick Access Toolbar.

• After that, select cells D5 to D10. Now, to lock the selected cells just click on the lock icon in the Quick Access Toolbar.

• Finally, the selected cells are locked. Like the previous method, protect the sheet with a password from the Review This will only lock the selected certain cells in our dataset.

Read More: Lock a Cell after Data Entry Using Excel VBA with Message Box Notification Before Locking

1.3 Protect Cells Containing Formulas in Excel

Suppose, our dataset contains different formulas. We need to share our dataset frequently with other users. So, we do not want another user to interrupt the formulas of our dataset. For that reason, we want to protect certain cells in excel that consist of formulas. Letâ€™s see the steps to do this.

STEPS:

• Firstly, go to the Home tab.

• Secondly, click on â€˜Find & Selectâ€™. Select the option â€˜Go To Specialâ€™ from the dropdown menu.

• A new dialogue box will open.
• Check the option Formulas from that box and click on OK.

• So, the above command selects only the cells containing formulas.

• Lastly, the cells youâ€™ve chosen are now locked. Protect the sheet with a password from the Review tab, just like the prior method. This will only lock the cells in our dataset that we specify.

Read More: How to Protect Excel Cells with Formulas (2 Simple Methods)

1.4 Use VBA Code to Protect Particular Cells

In this method, we will use a VBA code to protect certain cells in an excel worksheet To apply the VBA code, we will follow the below steps.

STEPS:

• In the first place, right-click on the sheet VBA-1.
• Next, select the option View Code.

• Then, a blank VBA code window will appear.
• Afterward, type the following code in that blank code window:
``````Sub Protect_Certain_Cells()
Range("D5:D10").Select
Selection.Locked = True
End Sub``````
• Now, click on the Run icon or press the F5 key to run the code.

• Furthermore, a new dialogue box with an input field for a password appears. Type your password in that field and press OK. We are using the value 1234 as the password.

• In the end, click on any cell in the D5 to D10 A message box similar to the one shown below appears. The notice indicates that the cell we are trying to access is password protected.

Read More: How to Protect Excel Cells with Password (4 Suitable Examples)

2. Apply VBA to Protect Certain Cells in Excel without Protecting Sheet

The above methods protect certain cells in an excel sheet by protection sheet. In this method, we will see how we can protect certain cells in an excel sheet without protecting the worksheet. In the following dataset, we will protect cells B5 and B6 from any kind of editing.

Follow the below steps to perform this action.

STEPS:

• To begin with, right-click on the sheet VBA-2 and select the option View Code.

• In addition, a blank VBA code window will appear.
• Furthermore, type the following code in that blank code window:
``````Sub Worksheet_SelectionChange(ByVal specific_cell As Range)
If specific_cell.Column = 2 Then
If specific_cell.Row = 5 Or specific_cell.Row = 6 Then
Cells(specific_cell.Row, specific_cell.Column).Offset(0, 3).Select
End If
End If
End Sub``````
• Now, press the F5 or click on the Run icon to run the code.

• Finally, if we select cell B5, the cursor will automatically move into cell E5.

Note: This code will run only if we select any cell and so we have defined the procedure as Worksheet_SelectionChange, Worksheet is the Object and SelectionChange is the Procedure. Two Ifâ€“Then statements have been used here to define our specific cells with column number 2 and now number 5 or 6.

Read More: How to Protect Cells without Protecting Sheet in Excel (3 Ways)

Related Articles

Mukesh Dipto

Hi there! I am Mukesh Dipto. I graduated with a bachelor's degree in engineering. Currently, I am working as a technical content writer in ExcelDemy. You will find all my articles on Microsoft Excel on this site. Outside of the workplace, my hobbies and interests include sports and meeting new people. I also enjoy sports. My favorite sports are football (to watch and play) and Badminton (play).

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF