Column Lock in Excel

Excel Column Lock

Column Lock feature in excel is used to avoid any mishaps or undesired changes in data done by mistake by any user, this feature can be applied to a single column or multiple columns at once or separately, after selecting the desired column change the formatting of the cells from locked to unlocked and password protect the workbook which will not allow any user to change the locked columns.

Locking a column can be done in two ways. This can be done by using the freeze panes in excel and by protecting the worksheet by using the review functions of excel. In excel we may want to lock a column so that the column does not disappear in case the user scrolls the sheet. This particularly happens in the case where we have a large count of headers in a single sheet and the user needs to scroll the sheet to see the other information that is on the sheet.

When the user scrolls the data then some of the columns may get lapsed and not be visible to the user. So we can lock the columns from getting scrolled out for the visible area.

How to Lock Column in Excel?

#1 – Locking the First Column of Excel

Step #1: Go to the View tab from the ribbon and choose the option of “Freeze panes”

Step #2: Form the freeze panes options, choose the option of “freeze the first column”.

This will freeze the first column of the spreadsheet.

#2 – Lock any other Column of Excel

If the column is to be locked then we need to follow the below steps.

Step #1 – Select the column that needs to be locked. If we want to lock column “D” then we need to select column “E”.

Step #2 – Now from the view tab, choose the option of freeze pane and select the first option to lock the cell.

The output is shown below:

#3 – Using the Protect Sheet Feature to Lock a Column

In this case, the user will not be able to edit the content of the locked column.

Popular Course in this category

All in One Excel VBA Bundle (35 Courses with Projects)4.9 (1,353 ratings) 35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion

View Course

Step #1 Select the complete sheet and change the protection to unlocked cells.

Step #2 – Now select that column that we want to lock and change the property of that cell to “locked”

Step #3 – Go to the review tab and click on the protect sheet and click on “OK”. Now the column will be locked.

Explanation of Column Lock in Excel

Ever wondered what will happen if someone changes the value of a cell or a complete column? Yes, this may happen especially in cases where the sheet is shared with multiple users of the team.

If this happens then this may drastically change the data of the sheet as many other columns may depend upon the values of some another column.

So, if we are sharing our file with other users then we must make sure that the column is protected and no user is able to change the value of that column. This can be done by protecting the columns and hence locking the columns. If we are locking a column then we can choose to have a password to unlock a column or we can choose to continue without a password.

This is about locking the column but sometimes we just want that a specific column does not lapse in case the sheet is scrolled and always visible on the sheet.

In this case, we need to use the freeze panes option that is available in excel. Using the freeze panes option we can actually fix the position of a column and this position does not change even if the sheet is scrolled. By using the freeze panes we can lock the first column, any other column, or even the lock the rows and columns of excel.

Things to Remember

If we are locking a column by using the Freeze panes option than only the column is locked from scrolling and the content of the column can always be changed anytime.
If we are using the Protect Sheet option and Freeze panes option than this is only possible to protect the content of the column and also protect it from scrolling.
If we want that a column that is not the first column that can be freeze than we need to select the next right column and then we need to click on freeze panes. Hence this should be remembered that always next column should be selected for freezing the prior column.
We can choose to have a password if we are protecting a column or we can continue without having a password.