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.
- Now go back to your spreadsheet and select the columns you want to lock.
- Then open up the context menu again. Select the Format Cells option from it.
- At this instant check the Locked option in the Protection tab from the Format Cells box that popped up.
- After that, click on OK.
- Next, select the Review tab from your ribbon.
- Then select Protect Sheet from the Protect group.
- Now, a Protect Sheet box will appear. Here, make sure that the Protect worksheet and contents of locked cells option is checked.
- 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.
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.
- 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.
- 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.
- After that, go to the Review tab on your ribbon.
- Then, from the Protect group, select Protect Worksheet.
- 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.
- 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.
If you want to edit the protected columns again, you have to unprotect the sheet to do so.
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.
- Now select the columns you want to lock.
- 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.
- Next, go to the Review tab and select Protect Sheet from the Protect group.
- 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.
- 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.
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.
- Now select the columns you want to protect in the dataset.
- 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.
- Next, go to the Review tab from your ribbon.
- Then select Protect Sheet from the Protect group.
- 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.
- 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.
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.
- Now a box called Allow Users to Edit Ranges will open up. Select New from it.
- 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.
- Finally check the Protect worksheet and contents of locked cells option and click on OK.
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.
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.
- 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.
Protect Columns in Excel: Knowledge Hub
<< Go Back to Excel Protect | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
