How to Protect Columns in Excel without Protecting Sheet (4 Easy Ways)

When we work on a large dataset, we have to keep some entities locked to ensure their retention. Additionally, some cells have to be editable for everyone so that others can input their data into the spreadsheet and be able to use the resource. In this context, we will demonstrate to you 4 different methods of how to protect columns in Excel without protecting the sheet. If you are interested to learn the feature, download our practice workbook and follow us.


Download Practice Workbook

Download this practice workbook for practice while you are reading this article.


4 Easy Ways to Protect Columns in Excel without Protecting Sheet

For demonstrating the approaches, we are considering a delivery dataset of 10 fruit items and their amount. The name of the fruit items is in column B, their quantity is in column C, and their delivery destination is in columns D and E. So, we can say that our dataset is in the range of cells B5:E14. Here, we will protect columns D and E only to ensure the stabilization of their delivery location.


1. Protect Columns Using Context Menu

In this method, we are going to use the Context Menu of Excel to protect the columns in Excel without the protecting sheet. We will lock the columns D and E and columns and columns B and C will remain modifiable. The steps of this process are given below:

📌 Steps:

  • First of all, press ‘Ctrl+A’ to select the entire worksheet.
  • Now, right-click on your mouse and select the Format Cells option.

Protect Columns in Excel Without Protecting Sheet from Context Menu

  • A dialog box called Format Cells will appear.
  • Then, go to the Protection window, and uncheck the Locked option.
  • Click OK to close the Format Cells window.

Protect Columns in Excel Without Protecting Sheet from Context Menu

  • Next, select the entire range of cells D4:E14.
  • Again, right-click on the mouse and select Format Cells > Protection.

Protect Columns in Excel Without Protecting Sheet from Context Menu

  • Check the Locked option and click the OK button.

Protect Columns in Excel Without Protecting Sheet from Context Menu

  • Now, in the Review tab, select Protect Sheet from the drop-down arrow of Protect group.

Protect Columns in Excel Without Protecting Sheet from Context Menu

  • A dialog box called Protect Sheet will appear. Write down a password into the empty box. In our file, we set ‘aa’ as the password.
  • Click OK.

  • Another dialog box titled Confirm Password will come. Re-enter the password here to confirm it and click OK.

  • Now, if you try to change any entity in column D or E, it will show you a warning and remind you to unprotect the columns with the password.

How to Protect Columns in Excel Without Protecting Sheet

Thus, we can say that our method worked perfectly and we are able to protect only columns without protecting the sheet.


2. Using Built-in Format Option

In this following process, we will use the Format option available from the built-in Excel Toolbar to protect the columns instead of the whole worksheet. We will protect only columns D and E. The steps of this approach are given below:

📌 Steps:

  • At first, press ‘Ctrl+A’ to select the entire worksheet.
  • Now, in the Home tab, select the drop-down arrow of Format > Format Cells option from the Cells group.

Using Built-in Format Option to Protect Columns in Excel Without Protecting Sheet

  • A dialog box titled Format Cells will appear.
  • After that, go to the Protection window, and uncheck the Locked option.
  • Click OK to close the window.

Protect Columns in Excel Without Protecting Sheet from Context Menu

  • Now, select the entire range of cells D4:E14.
  • Select the drop-down arrow of Format > Lock Cell from the Cells group.

Using Built-in Format Option to Protect Columns in Excel Without Protecting Sheet

  • Then, in the Review tab, select the drop-down arrow of Protect Sheet from the Protect group. You can also lunch the window from the drop-down arrow of the Format option.

Using Built-in Format Option to Protect Columns in Excel Without Protecting Sheet

  • A dialog box entitled Protect Sheet will appear. Write down a password into the empty box. In our case, we set ‘aa’ as the password.
  • Click OK.

  • Another dialog box called Confirm Password will come. Re-enter the password here to confirm it and click OK.

  • You will find that if you try to change any entity in column D or E, it will show you a warning and remind you to unprotect the columns with the password.

How to Protect Columns in Excel Without Protecting Sheet

Finally, we can say that our procedure worked perfectly and we are able to protect only columns without protecting the sheet.

Read More: How to Protect Columns with Password in Excel (4 Ways)


3. Use of Allow Edit Ranges Command to Protect Columns

In this approach, the Allow Edit Ranges of Protect group will help us to protect columns in Excel. In this case, we will define the editable cell range in Excel and Excel will permit a user to modify only those cells. We will use the same dataset which is used in the previous methods. The process is explained below step by step:

📌 Steps:

  • First, in the Review tab, select the Allow Edit Ranges option from the Protect group.
  • A dialog box called Allow Users to Edit Ranges will appear.

Use of Allow Edit Range Option to Protect Columns in Excel Without Protecting Sheet

  • Now, in this box, select the New option.

  • As a result, another dialog box titled New Range will appear.
  • Write down a suitable title for your range. We set the Modifiable columns as our range title.
  • Then, select the Refer to cells option by selecting the entire range of cells B4:C14.
  • Click OK to close the New Range window. Don’t put any value in the Range Password field.

Use of Allow Edit Range Option to Protect Columns in Excel Without Protecting Sheet

  • After that, select the range and click on Apply.
  • Now, select the Protect Sheet option.

Use of Allow Edit Range Option to Protect Columns in Excel Without Protecting Sheet

  • The Protect Sheet dialog box will appear. Write down a password into the empty box. We set ‘aa’ as the password.
  • Click the OK button.

  • Another dialog box called Confirm Password will come. Re-enter the same password here to confirm it and click OK.

  • Finally, you will see that if you try to modify any entity in column D or E, it will show you a warning and remind you to unprotect the columns with the password.

How to Protect Columns in Excel Without Protecting Sheet

At last, we can say that our procedure worked successfully and we are able to protect only columns without protecting the sheet.


4. Embedding VBA Code

Writing a VBA code will also help you to protect columns in Excel without protecting the sheet. Our dataset is in the range of cells B5:E14. We will protect only columns D and E. The steps of this method are below as follows:

📌 Steps:

  • At the beginning of this process, move your mouse cursor on the sheet name titled Sheet1 in the Sheet Name Bar.
  • Now, right-click on your mouse and select the View Code option.

Embedding VBA Code to Protect Columns in Excel Without Protecting Sheet

  • A dialog box will appear.
  • Then, write down the following visual code in that empty editor box.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim i As Integer

    If Target.Column = 4 Then
    For i = 4 To 14
        If Target.Row = i Then
        Beep
        Cells(Target.Row, Target.Column).Offset(0, 1).Select
        End If
    Next i
    End If

    If Target.Column = 5 Then
    For i = 4 To 14
        If Target.Row = i Then
        Beep
        Cells(Target.Row, Target.Column).Offset(0, 1).Select
        End If
    Next i
    End If
End Sub
  • After that, close the Editor tab.
  • Finally, you will see that you cannot select any cell in the range of cells D4:E14 and if try to select you will hear a Beep sound from your device. Thus, you can consider those columns protected.

Lastly, we can say that our VBA code worked successfully and we are able to protect only columns without protecting the sheet.

Read More: How to Protect Specific Columns Using VBA in Excel 


Conclusion

That’s the end of this article. I hope that this article will be helpful for you and you will be able to protect columns in Excel without protecting the sheet. If you have any further queries or recommendations, please share them with us in the comments section below.

Don’t forget to check our website ExcelDemy for several Excel-related problems and solutions. Keep learning new methods and keep growing!


Further Reading

Soumik Dutta
We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo