How to Lock Certain Cells in Excel (4 Methods)

To protect our spreadsheet, we need to know how to lock certain cells in Excel. Locking the whole spreadsheet or certain cells in Excel allows us to protect our data and integrity, and prevent others from deleting formulas and manipulating our data. For your better understanding, we will use a sample dataset containing Date, Product, Price, Quantity, and Sales.

Lock Certain Cells in Excel


Download Practice Workbook


4 Simple Ways to Lock Certain Cells in Excel

In this tutorial, we will see four different methods on how to lock certain cells in Excel. Using our dataset we will show you locking cells containing raw data and formulas.


Method 1: Lock Certain Cells in Excel Using Home Tab

By default, the worksheet is locked. So, first, we need to unlock the entire worksheet then we can lock certain cells as we want.

Steps:

  • First, select the entire worksheet by clicking on the sign as shown in the image.

  • Now, right-click on the mouse button and select Format Cells.

As we told you earlier, all the cells are by default locked, you can see that.

Lock Certain Cells in Excel format cell

  • So, just uncheck the locked options, and click OK.

Lock Certain Cells in Excel

  • Now, select the cells we want to lock (here, we want to lock Product and Quantity columns) and go to Home > Format > Lock Cells.

Lock Certain Cells in Excel using home tab

  • After that, go to the Review tab and select Protect Worksheet.

  •  A dialogue box will pop up. Now, give the password as you like.

Lock Certain Cells in Excel review tab

  • Clicking OK will ask you to Re-enter the password.

Finally, click OK and done. All the cells from the selected columns will be locked.
At this point, click on any of the cells in those selected columns and a message box will pop up.

Lock Certain Cells in Excel proctect sheet

Read More: How to Lock a Group of Cells in Excel (7 Different Methods)


Method 2: Using Format Cells to Lock Certain Cells

Another method to lock certain cells in Excel is the Format Cells option using a shortcut. Here we want o lock all the cells in the Product column.

Steps:

  • First, select the entire worksheet by clicking on the sign as shown in the image.

  • Now, right-click on the mouse button and select Format Cells.

As we told you earlier, all the cells are by default locked, you can see that.

Lock Certain Cells in Excel format cell

  • So, just uncheck the locked options and click OK.

Lock Certain Cells in Excel

  • Now, select the cells in Product column and press the CTRL+1 key. This time , check the Locked box, as shown in the image below.

Lock Certain Cells in Excel by shortcut

  • After that, we have to do all the work from the Review tab. So, from here, we have to follow the methods from Method 1.

Read More: How to Select Cells with Certain Value in Excel (5 Methods)


Similar Readings


Method 3: Lock Certain Cells Containing Formulas

Till now, we have seen locking certain cells. What if, our dataset is huge and contains formulas. There’s an easy way to lock formula cells in Excel.

Steps:

  • First, select the entire worksheet by clicking on the sign as shown in the image.

  • Now, right-click on the mouse button and select Format Cells.

As we told you earlier, all the cells are by default locked, you can see that.

Lock Certain Cells in Excel format cell

  • So, just uncheck the locked options and click OK.

Lock Certain Cells in Excel

  • Now, press CTRL+G and a dialogue box will pop up.

Lock Certain Cells in Excel paste special formula

  • From, the dialogue box, select Special and another dialogue box will pop up this time. We select the Formulas option from there. Like the image below.

Lock formula Cells in Excel

  • After clicking OK, all our formula cells will be selected automatically.

From here, all we need to do is, follow either Method 1 or Method 2.

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


Method 4: VBA to Lock Certain Cells

At the end of this tutorial, we will see a VBA code to lock certain cells in Excel.

Steps:

  • First, right-click on the sheet and go to View Code.

Lock Certain Cells in Excel vba

  • After that, copy and paste the VBA code below.

VBA code:

Sub CertainCellLocker()

Cells.Select

Selection.Locked = False

Range("B5:B13,F5:F13").Select

Selection.Locked = True

ActiveSheet.Unprotect Password:="exceldemy"

ActiveSheet.Range("B5:B13,F5:F13").Cells.Locked = True

ActiveSheet.Protect Password:="exceldemy"

End Sub

Lock Certain Cells in Excel vba macro

To unlock the by default locked cells, we commanded Selection.Locked = False.

Then, we specified our certain cells by Range(“B5:B13,F5:F13”).Select and locked them through the code Selection.Locked = True.

  • After that, press the F5 or play button to run the code.

As you can see, from the above image, we locked our desired cells.

Read More: How to Lock Multiple Cells in Excel (6 Suitable Methods)


Practice Section

The single most crucial aspect in becoming accustomed to these quick approaches is practice. As a result, we’ve attached a practice workbook where you may practice these methods.


Conclusion

These are 4 different methods on How to Lock Certain Cells in Excel. Based on your preferences, you may choose the best alternative. Please leave them in the comments area if you have any questions or feedback.


Related Articles

Tags:

Mahbub
We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo