How to Protect Columns in Excel without Protecting Sheet (4 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.

How to Protect Columns in Excel without Protecting Sheet: 4 Ways

To demonstrate 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. 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 columns D and E and columns and columns B and C will remain modifiable.

📌 Steps:

• Press Ctrl+A to select the entire worksheet.
• Right-click on your mouse and select the Format Cells option.

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

• Select the entire range D4:E14.
• Again, right-click on the mouse and select Format Cells.

• Check the Locked option in the Protection tab and click the OK button.

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

• A dialog box called Protect Sheet will appear.
• Write down a password in 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.

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.

📌 Steps:

• Select the entire worksheet by pressing Ctrl+A.
• In the Home tab, select the drop-down arrow of Format > Format Cells option from the Cells group.

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

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

• From the Home tab, go to Cells > Format > Protect Sheet.

• A dialog box entitled Protect Sheet will appear.
• Write down a password in 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.

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

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

3. Use of Allow Edit Ranges Command to Protect Columns

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 and Excel will permit a user to modify only those cells. We will use the same dataset which is used in the previous methods.

📌 Steps:

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

• In this box, select the New option.

• 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.
• Select the Refer to cells option by selecting the range B4:C14.
• Click OK to close the New Range window. Don’t put any value in the Range Password field.

• Select the range and click on Apply.
• Select the Protect Sheet option.

• The Protect Sheet dialog box will appear. Write down a password in 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.

• You will see a warning if you try to modify in columns D or E.

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

📌 Steps:

• Move your mouse cursor on the sheet name titled Sheet1 in the Sheet Name Bar.
• Right-click on your mouse and select the View Code option.

• 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 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 

Download Practice Workbook

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

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 for several Excel-related problems and solutions. Keep learning new methods and keep growing!

Further Reading

• How to Protect Hidden Columns in Excel