How to Protect Cells Without Protecting Sheet in Excel (3 Ways)

If you are trying to protect cells in Excel without protecting sheet, then you will find this article useful. So, let’s start with the main article.

Here, we have the following dataset containing the sales records of some products of a company. We will try to protect specific cells of this dataset without protecting the whole dataset or sheet by using the following 3 methods.

We have used Microsoft Excel 365 version here, you can use any other versions according to your convenience.

1. Using Format Cells Option to Protect Cells Without Protecting Excel Sheet

In this section, we will use the Format Cells option to protect the cells of the Product column only instead of protecting the whole dataset.

Steps:
➤ Select the whole sheet by clicking on the triangle symbol in the top left corner and then go to the Home Tab >> Cells Group >> Format Dropdown >> Format Cells Option.

Then, the Format Cells dialog box will appear.
➤ Click on Protection, uncheck the Locked option and then select OK.

Now, we will do that process again for the cells which we want to lock.
➤ Select the cells of the Product column and then go to the Home Tab >> Cells Group >> Format Dropdown >> Format Cells Option.

After that, the Format Cells dialog box will appear again.
➤ Click on Protection, check the Locked option, and then select OK.

It’s time to protect this sheet to check if only the selected cells are locked or not.
➤ Go to the Home Tab >> Cells Group >> Format Dropdown >> Protect Sheet Option.

After that, the Protect Sheet wizard will pop up.
➤ Type the password and press OK.

➤ Reenter the password and press OK again.

Now, if you want to change any of the cell’s values of the Product column then you will get the following error message.

But, we have successfully changed the sales value for the Strawberry from $3,914.00 to $4,000.00.

2. Using Allow Edit Ranges Option to Protect Cells in Excel Without Protecting Sheet

Here, we will use the Allow Edit Ranges option to protect the cells of the Product column without locking the other cells of this sheet.

Steps:
➤ Go to the Review Tab >> Protect Group >> Allow Edit Ranges Option.

After that, the Allow Users to Edit Ranges dialog box will open up.
➤ Select the New Option.

Then, you will be taken to the New Range dialog box.
➤ Name the Title box as Range1 or any other thing you want and type the range C4:D11 in the Refers to cells box and press OK.

Afterward, the Allow Users to Edit Ranges dialog box will appear again.
➤ Click on Apply.

➤ Now, select the Protect Sheet option again.

Then, the Protect Sheet wizard will pop up.
➤ Type the password and press OK.

➤ Reenter the password and press OK again.

For changing any of the cell’s values of the Product column then you will get the following error message.

But, we have successfully changed the sales value for the Cherry from $4,316.00 to $3,845.00.

3. Applying a VBA Code to Protect Cells Without Protecting Sheet

In this section, we will use a VBA code to protect the specific cells for the products Cherry, and Apple without protecting the whole sheet.

Steps:
➤ Right-Click on the name of the sheet.

➤ Select the View Code Option.

Then, you will be taken to the Visual Basic Editor window.

➤ Type the following code in the code window.

Sub Worksheet_SelectionChange(ByVal specific_cell As Range)
If specific_cell.Column = 2 Then
If specific_cell.Row = 6 Or specific_cell.Row = 9 Then
Cells(specific_cell.Row, specific_cell.Column).Offset(0, 3).Select
End If
End If
End Sub

This code will execute 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 Row Number 6 or 9.
If these conditions are fulfilled then the selected cell will be 3 cells right to the cells B6 or B9.

Now, try to select the cell containing the product Watermelon, and then our selection will be moved to the 3 cells right.

Practice Section

For doing practice by yourself we have provided a Practice section like below in a sheet named Practice. Please do it by yourself.

Download Workbook

Protect Cells.xlsm

Conclusion

In this article, we tried to cover the ways to protect cells in Excel without protecting sheet. Hope you will find it useful. If you have any suggestions or questions, feel free to share them in the comment section.

<< Go Back to Protect Excel Cells | Excel Protect | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!