How To Protect Cells In Excel Without Protecting Sheet – 3 Methods

This article will be helpful if you’re trying to Protect your cells The sheet should not be protected. Let’s get to the point.

Excel Protectors: Protecting Excel Cells without Protecting Sheets

You can use the following methods: Protect a cell This data sheet is not protected.

Method-1 Using Format Cells Option To Protect Cells Without Protecting Sheet

Method-1 Using Format Cells Option To Protect Cells Without Protecting Sheet

This section will only use Format Cells to protect cells in Product columns, and not the entire dataset.

Steps:

  • To select the entire sheet, click on the triangle symbol in the upper left corner. Next, click on the triangle symbol in the top left corner to select the entire sheet. Tab home >> Cells group >> Dropdown >> Format cells option
  • The Format cells dialogue This will be followed by a notification.
  • Click here Security. Check the Locked Choose an option and then select OK.
  • We’ll repeat this process for all cells that we wish to lock.
  • Click on the Product column cells and then click on the Home Tab >> Cells Group >> Format dropdown >> Format Cells Option.
  • The Format cells dialogue Then it will appear again.
  • Click on Security To check the Locked Choose option, then choose OK.
  • This sheet should be secured to ensure that locked cells can be verified.
  • Visit the Tab home >> Cells group >> Dropdown >> Protect Your Sheet Option
  • The Protect sheet wizard Then, it will appear.
  • Enter your password and hit OK.
  • Retype the password and hit “Enter again”. OK.
  • You can modify any cell by Product Column, you will get an error message.

Method-2 Using Allow Edit Ranges Option for Excel to Protect Cells without Protecting Sheet

We will use Edit Ranges Option here to protect cells Product columnThis sheet does not contain the entire sheet.

Steps:

  • Visit the Review Tab >> ProtectGroup Allow editing ranges Option
  • The Users can edit ranges A dialog box will then appear.
  • Select the Neue Option.
  • Then, you will be taken to the New Range dialogue.
  • Choose Range1 in either the Title box, or another name that you like, then enter C4:D11 in the Refers cells box. Click OK.
  • After that, it’s time to Allow users to edit Ranges dialog It will be shown again.
  • Click here Use.
  • Choose the Protect sheet option again.
  • The Protect sheet wizard The website will then be updated.
  • Enter your password and hit OK.
  • Hit the Enter key again to confirm your password OK
  • You will receive an error message if you try to change any cell’s Product Column values

Method-3 Using a VBA Code To Protect Cells Without Protecting Sheets

Method-3 Using a VBA Code To Protect Cells Without Protecting Sheets

Steps:

  • Right-Click to select the name of the sheet
  • Choose the Select code.
  • 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, so we have defined the Procedure as Worksheet_SelectionChange, Worksheet is the Object, and SelectionChange is the Procedure.
  • Two IF/THEN statements are used for defining the cells in Column 

These are the three simple methods to protect cells without exposing Excel sheets

Leave a Comment