How To Lock A Column In Excel

When you share a spreadsheet in Excel, others can make damaging changes to your data. Protect certain areas of your spreadsheet by locking columns.

how to lock a column in excel

Last Updated on

Microsoft Excel has a great feature which allows you to share your spreadsheets with others, but sometimes your co-workers can accidentally make edits to the wrong column. To prevent this, you can lock certain columns on your spreadsheet – but how? 

Check out the below guide to lock columns in Microsoft Excel.


Locking Columns In Microsoft Excel



Unprotect Your Spreadsheet

Before you start locking specific columns in your spreadsheet, you first need to unprotect the worksheet. Otherwise, you cannot edit any of the cells on your sheet and lock certain areas while leaving others unlocked.

So, first, head to the Review menu. You can find the Review tab button at the top ribbon menu of the Excel window. It is between the Data and View menu buttons.

Once you have opened the Review menu, you will need to find the ‘Unprotect Sheet’ button. This will be in the ‘Changes’ section of the menu on the right hand side, and will have an icon of a gold lock and a spreadsheet. Select it, and input the protected password if prompted.

This will unlock the whole spreadsheet and allow you to start making changes.



Select Your Spreadsheet

Now, you need to highlight your entire spreadsheet. Press the triangular arrow in the top left hand corner of your spreadsheet to do this.

Right click anywhere on your spreadsheet and then select ‘Format Cells’ from the menu that appears. Alternatively, you can just hold down the Control, Shift, and F button on your keyboard simultaneously.

This will bring up the Format Cells menu. Under the Protection tab, you will need to make sure that the box next to ‘Lock Cells’ is not ticked. Then, click ‘OK’ to make all the cells in your spreadsheet editable.



Select Your Desired Column

How you will need to highlight the column you want to lock.

To do this, you will need to click and drag your cursor over all of the cells in the column. This will turn them all a gray or light blue color, indicating that they have been selected. 

On your highlighted column, you will need to right click using your cursor and then select ‘Format Cells’ from the drop down menu that appears. Or, you can just press down the Control, Shift, and F buttons on your keyboard. 

Both options will bring up the Format Cells option box. 



Format Cells

Now, you will be back in the Format Cells menu.

This time, you want to make sure that the box next to ‘Lock Cells’ is ticked. Then, you can select the ‘OK’ button and this will lock the entire column of selected cells.

If you want to lock separate columns, then you will have to repeat this process for each one.



Protect Your Spreadsheet

Now that your column is locked, you can leave your spreadsheet – but remember to protect it again!

To do this, open up the Format Cells menu once more and head to the Review tab instead. Select ‘Protect this worksheet’ and write in the password.

Make sure that these two elements are enabled: ‘select lock cells’ and ‘select unlock cells’  as both of these will allow anyone who accesses the sheet to select cells but not make changes to locked cells.


And that is how you can lock an entire column in Microsoft Excel

The same process works for locking rows and single cells as well as columns, so you can share your spreadsheet with ease knowing that others cannot make changes to certain areas.