Mukesh Dipto
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.
Download Practice Workbook
You can download the practice workbook from here.
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:
- First, click on the icon of ‘Customize Quick Access Toolbar’.
- 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
Similar Readings
- How to Protect Excel Cells from Deletion (3 Handy Methods)
- Protect Excel Cells But Allow Data Entry (2 Quick Methods)
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()
Dim The_Password As String
Range("D5:D10").Select
Selection.Locked = True
The_Password = InputBox("Type the Password:")
ActiveSheet.Protect Password:=The_Password
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)
Conclusion
In conclusion, this article shows how to protect certain cells in an excel worksheet. To put your skills to the test, download the practice workbook that comes with this article. Please leave a comment in the box below if you have any questions. Our team will make every effort to respond to you as quickly as possible. So keep a watch out in the future for more innovative Microsoft Excel solutions.
