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.

Download Workbook


3 Ways to Protect Cells without Protecting Sheet in Excel

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.

how to protect cells in Excel without protecting sheet

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


Method-1: Using Format Cells Option to Protect Cells without Protecting 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.

how to protect cells in Excel without protecting sheet

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.

Format cells option

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

Format cells option

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.

Format cells option

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

how to protect cells in Excel without protecting sheet

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.

Format cells option

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

Format cells option

➤ Reenter the password and press OK again.

Format cells option

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

how to protect cells in Excel without protecting sheet

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

Format cells option

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


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

how to protect cells in Excel without protecting sheet

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

Using Allow Edit Ranges Option

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

how to protect cells in Excel without protecting sheet

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.

Using Allow Edit Ranges Option

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

Using Allow Edit Ranges Option
➤ Now, select the Protect Sheet option again.

how to protect cells in Excel without protecting sheet

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

Using Allow Edit Ranges Option

➤ Reenter the password and press OK again.

Using Allow Edit Ranges Option

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

how to protect cells in Excel without protecting sheet

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

Using Allow Edit Ranges Option

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


Similar Readings


Method-3: Using 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.

how to protect cells in Excel without protecting sheet

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

VBA Code

➤ Select the View Code Option.

how to protect cells in Excel without protecting sheet

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

VBA Code

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

VBA Code

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

VBA Code

Read More: Excel VBA to Lock Cells without Protecting Sheet (4 Ideal Examples)


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.

Practice


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.


Related Articles

Tanjima Hossain

Tanjima Hossain

Hello everyone, This is Tanjima Hossain. I have completed my graduation from BUET. Then I have started working as a technical writer in SOFTEKO. I have grown interest in technical content writing, research topics, numerical analysis related field and so I am here. Besides this I love to interact with different people and I love to spend my spare time by reading, gardening ,cooking etc.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo