How to Protect Columns in Excel (6 Easy Methods)

There can be many reasons you may want to protect columns in Excel. Such as, you may need to protect columns so that the viewers cannot change values in them.  Or you simply just need to protect some constant values from accidental changes. In this tutorial, I am going to show you how to protect columns in Excel.


Download Practice Workbook

You can download the workbook with the dataset used for the demonstration below. The results of each demonstration are added in different worksheets. Use “exceldemy” if any password is required.


6 Easy Ways to Protect Columns in Excel

In this tutorial, We will be going over 6 different methods to protect columns in Excel. Most of the methods mainly vary in how you are formatting the cells. The basic idea is to lock the cells in an Excel column and then protect the sheet to avoid changes in those columns. Also, there is another way where you can determine editable columns which will protect the other columns in the spreadsheet. Not to mention, the VBA code you can use to protect columns in Excel.

To illustrate each method, we will be using the following dataset.


1. Use of Context Menu to Protect Columns in Excel

This method focuses on locking columns using the context menu before protecting a spreadsheet in Excel. To open the context menu in Excel you either right-click on a cell or a selection. Or you can press Shift+F10 on your keyboard.

For a more detailed guide, follow these steps.

Steps:

  • Firstly, select all cells in the spreadsheet by clicking on the triangular sign where row headers and column headers meet.

  • Secondly, open up the context menu by right-clicking on the selection or pressing Shift+F10 on your keyboard. Then select Format Cells from it.

  • Consecutively the Format Cells box will open up. After that go to the Protection tab and uncheck the Locked option from it.
  • Now click on OK. This will unlock all the cells in the spreadsheet.

how to protect columns in excel

  • Now go back to your spreadsheet and select the columns you want to lock.

how to protect columns in excel

  • Then open up the context menu again. Select the Format Cells option from it.

how to protect columns in excel

  • At this instant check the Locked option in the Protection tab from the Format Cells box that popped up.
  • After that, click on OK.

how to protect columns in excel

  • Next, select the Review tab from your ribbon.
  • Then select Protect Sheet from the Protect group.

how to protect columns in excel

  • Now, a Protect Sheet box will appear. Here, make sure that the Protect worksheet and contents of locked cells option is checked.

how to protect columns in excel

  • Finally, click on OK.

From now on, a warning box will appear preventing the selected columns from being edited. And you need to unprotect the sheet if you want to edit it further.

how to protect columns in excel

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


2. Using Dialog Box Launcher to Protect Columns in Excel

In this method, we will use the dialog box launcher to format and lock the columns to protect them in an Excel spreadsheet. A dialog box launcher is the downward-right facing button used to avail additional tools for formatting Excel cells. For a detailed guide, follow the steps below.

Steps:

  • First, click on the triangular sign where row headers and column headers meet. This will select all the cells in the spreadsheet.

  • Now use the dialog box launcher by clicking on the downward-right facing arrow on the right of the group names in the Home tab.

how to protect columns in excel

  • As a result, the Format Cells box will open up. Now go to the Protection tab in it and uncheck the Locked
  • Then click on OK.

how to protect columns in excel

  • Now, select the columns you want to protect in the Excel spreadsheet.

  • Again, open up the Format Cells But this time check the Locked option from the Protection tab.
  • Now click on OK.

how to protect columns in excel

  • After that, go to the Review tab on your ribbon.
  • Then, from the Protect group, select Protect Worksheet.

how to protect columns in excel

  • If you want to protect the columns with a password, enter one in the password field.
  • Now, be sure to check the Protect worksheet and contents of locked cells option is checked.
  • After that, click on OK.

how to protect columns in excel

  • If you have entered a password in the previous box, a confirmation box will appear again. Re-enter the password here and click on OK.

From now on, the selected columns will be locked and a warning box will appear preventing it from editing.

how to protect columns in excel

If you want to edit the protected columns again, you have to unprotect the sheet to do so.

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


3. Applying Format Cells Command

There is another way to format columns in order to lock them. It is the built-in format cells option in Excel. In a similar manner, you can then protect the sheet to protect the locked columns with or without a password.

These steps will be helpful as a more detailed guide.

Steps:

  • First of all, select all the cells in the spreadsheet by clicking on the triangle sign where the row headers and column headers meet.

  • Now go to the Home tab in your ribbon.
  • Then click on Format from the Cells
  • Next, select Format Cells from the drop-down menu.

  • As a result, the Format Cells box will appear. In it, go to the Protection tab and uncheck the Locked option.
  • After that, click on OK.

how to protect columns in excel

  • Now select the columns you want to lock.

how to protect columns in excel

  • Then select Format again from the Cells group in the Home tab and select the Format Cells option.

  • At this instant, go to the Protection tab and check the Locked option.
  • After that, click on OK.

how to protect columns in excel

  • Next, go to the Review tab and select Protect Sheet from the Protect group.

how to protect columns in excel

  • Now check the Protect worksheet and contents of locked cells option and enter a password in the protection field if you want to protect the columns with a password.

how to protect columns in excel

  • Finally, click on OK.

Consequently, this will protect all the columns in the Excel spreadsheet. If you want to edit any of the cells from the columns from now on, a warning box will appear preventing the column from being edited.

how to protect columns in excel


4. Using Keyboard Shortcuts

There are also keyboard shortcuts for formatting cells which you can use to protect columns in Excel. In a similar manner to the other ones, you can use these keyboard shortcuts to lock the cells. And then you can protect the sheet to prevent the locked columns from being edited.

Steps:

  • First of all, select all cells by clicking the triangle where all the row headers and column headers meet. You can also do the same by clicking a blank cell in the Excel spreadsheet and pressing Ctrl+A on your keyboard.

  • Now press Ctrl+Shift+F or Ctrl+1 on your keyboard. This will directly open up the Format Cells box.
  • After that, go to the Protection tab in it and uncheck the Locked option.
  • Then click on OK.

how to protect columns in excel

  • Now select the columns you want to protect in the dataset.

how to protect columns in excel

  • Then press on the keyboard shortcuts again. At this instant check the Locked option from the Protection tab in the Formula Cells box.
  • Now click on OK.

how to protect columns in excel

  • Next, go to the Review tab from your ribbon.
  • Then select Protect Sheet from the Protect group.

how to protect columns in excel

  • Now check the Protect worksheet and contents of locked cells option in the Protect Sheet Add a password in the password field if you want to protect the cells with a password.

how to protect columns in excel

  • Finally, click on OK.

Now the locked columns are protected. If you want to edit any of the cells in those columns a warning box will appear preventing changes of those cells of any form.

how to protect columns in excel

In case you want to edit those columns, you need to unprotect the sheets and then edit those columns.


5. Use of Allow Edit Ranges Feature to Protect Columns in Excel

Unlike any other methods where we are directly protecting columns, this method is particularly helpful to single out the columns which you can edit. Selecting the columns which you can edit leave the other columns invulnerable to edits. Thus protecting the columns from edits.

To single out the columns you can edit from the rest of the dataset we use a feature called Allow Edit Ranges. Follow these steps to see how you can use this method to protect columns.

Steps:

  • At first, select the Review tab from your ribbon.
  • Then select Allow Edit Ranges from the Protect group.

how to protect columns in excel

  • Now a box called Allow Users to Edit Ranges will open up. Select New from it.

how to protect columns in excel

  • Then in the New Range box select the range of cells in the Refers to cells Although not necessary, you can also add a title to the selected columns.

Remember, these are the columns you can edit in your spreadsheet.

  • After that click on OK.
  • Now in the Allow Users to Edit Ranges box select the range and then click on Protect Sheet.

how to protect columns in excel

  • Finally check the Protect worksheet and contents of locked cells option and click on OK.

how to protect columns in excel

As a result of all the steps shown above, columns D and E will be editable, leaving all the cells including columns B and C to be protected from edits. If you want to edit anything on the cells of columns B and C, the warning box will appear preventing you from editing the cells in the column.

how to protect columns in excel


6. Embedding VBA Code to Protect Columns in Excel

You can also use Microsoft Visual Basic for Applications (VBA) to protect columns in Excel. But first, you need the Developer tab on your ribbon first. If you don’t have that, then enable the developer tab in the ribbon. Once you have it, follow these simple steps to protect columns in Excel with VBA.

Steps:

  • Firstly, go to the Developer tab on your ribbon.
  • Then select Visual Basic from the Code group.

  • The VBA window will now open up. Next, select the sheet from the left-hand side of the window.

how to protect columns in excel

  • Now write down the following code in the VBA editor.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim j As Integer

If Target.Column = 3 Then
For j = 5 To 9
If Target.Row = j Then
Beep
Cells(Target.Row, Target.Column).Offset(0, 1).Select
End If
Next j
End If

If Target.Column = 5 Then
For j = 5 To 9
If Target.Row = j Then
Beep
Cells(Target.Row, Target.Column).Offset(0, 1).Select
End If
Next j
End If
End Sub
  • Finally, save and close the VBA window.

The code here is selected for columns C and E. We did that using column numbers. So if you want to edit columns C and E there will be a beep sound and the cell highlighter will automatically move to the right of the selection, preventing any edit in those columns.


Conclusion

These are all the methods you can use to protect columns in Excel. Hope you have found this guide helpful and informative. If you have any questions or suggestions, let us know below. For more guides like this visit Exceldemy.com.


Further Reading

Abrar Niloy

Abrar Niloy

Hi! my name is Abrar-ur-Rahman Niloy. I have completed B.Sc. in Naval Architecture and Marine Engineering. I have found my calling, if you like, in Data Science and Machine Learning and in pursuing so, I have realized the importance of Data Analysis. And Excel is one excel-lent tool do so. I am always trying to learn everyday, and trying to share here what I am learning.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo