How to Lock a Column in Google Sheets (Simple Examples)

Today, we will look at how to lock a column in Google Sheets in the most basic way available to us in the application.

As an extra situational example, we will also explore how to lock columns conditionally later in the article. So let’s get started.

How to Lock a Column in Google Sheets

The Basic Way to Lock a Column in Google Sheets

We start with the fundamental way to lock any cell or column in Google Sheets, that is, by using the Protect Range option.

And for our example, we will be using the following worksheet:

base table for how to lock a column in google sheets

Notice that the Grade column contains a formula. Thus, it is crucial to lock this particular column to protect it from alterations.

Step 1: Select the column (we have selected the whole column) and open the Protect range menu. We can navigate to Protect range in two ways:

1. From the Data tab. Data > Protect sheets and ranges

navigating to protect range from the Data tab

2. Right-clicking over the selected cells. Right-click > View more column actions > Protect range

right clicking to navigate to protect range

This should open the Protected sheets & ranges menu

Step 3: Here we have three sections to note:

  1. Naming the protected range [Optional]. You can name your locked cells for better organization.
  2. Range of protected cells which will be locked from further changes. You can change the range again from here.
  3. Click Set permissions to move to the next step.

setting the base conditions to lock a column in google sheets

Step 3: By default, you will be presented with the following Range editing permissions window:

range editing permissions to lock a column in google sheets

For now, we have restricted the permissions only to us. Click Done to apply the lock.

Now, if anyone else goes to change anything within the locked column, they will be hit with the following message:

warning message when editing locked cells

Points to Note:

  1. If the Can edit message appears in the Protected sheets & ranges menu, you have permission to edit the locked cells in this worksheet. Otherwise, you can’t.

can edit locked column in google sheets

  1. You can choose to select Custom permissions in the Range editing permissions window to add more people to edit the locked cells in Google Sheets.
selecting custom range editing permissions

Select Custom Permissions

adding collaborators to edit locked cells in google sheets

Adding more people to collaborate

You can read our How to Lock Cells in Google Sheets article for an in-depth guide.

Read More: Protect Range in Google Sheets (Easy Examples)

How to Conditionally Lock a Column in Google Sheets

We can use the Protect range option to lock any range of cells we desire. But what if we want to lock cells if they meet a particular criterion?

We cannot add conditions to the Protect range option, however, we can use another function in Google Sheets called Data Validation to do so.

The Data validation option allows us to add a custom formula as criteria in Google Sheets.

To show this process, let’s transform our existing worksheets to follow this scenario:

You are a supervisor to teachers. Depending on your permission, you can either Open or Close the range of cells containing the points of students for others to edit. Note that the Grade column is already locked.

So, let’s first add a drop-down for the Open or Closed permissions.

adding a drop-down for options

Steps to Lock Columns with Data Validation in Google Sheets

Now we begin the process of locking cells with conditions using Data Validation.

Step 1: Select the range of cells that is to be locked. For us, it’s C3:E7 as we want to lock the Quiz, Mid, and Final columns.

selecting columns for data validation in google sheets

Step 2: Navigate to Data Validation. Data > Data validation

navigating to data validation from the Data tab

Step 3: Since we are going to define the criteria for locking cells, we will select the Custom formula is option from the Criteria section in the Data validation window.

selecting the custom formula is option

Step 4: Add the custom formula:


Step 5: Check the Reject input radio button in the On invalid data section.

adding custom formula and conditions to data validation to lock columns in google sheets


The Reject input option essentially gives the condition that if cell D10 does not have the value “Open”, you cannot enter or change existing data. We have locked the cell reference D10 with absolutes ($) so that it doesn’t move along with the selected range.

Step 6: Click Save.

how to lock a column in google sheets with conditions using data validation

Now anyone is restricted (also gets an error message) from changing the scores in our table as long as we keep the permission Closed.

lock cells with data validation animated

Additional Step: We have also locked cells C10 and D10 with Protect range so that only we can change the Permission.

Read More: Conditional Locking Of Cells In Google Sheets (Easy Steps)

How to Unlock a Column

Unlocking columns in Google Sheets is as easy as locking them.

With an active locked cell selected, navigate to Protect sheets and ranges from the Data tab. Data > Protect sheets and ranges

navigating to protect sheets and ranges in google sheets

If protected cells exist in your worksheet, the Protected sheets & ranges menu should look like this:

protect sheets & ranges with locked ranges

From this menu, you can now select the locked range of cells that you want to edit or remove by simply clicking on it.

Here, click the trash can icon to remove or unlock the locked column.

removing locked cells from the protected sheets & ranges menu

You will be given a final prompt before removing the locked column.

final prompt before unlocking a column in google sheets

And we are done!

Read More: Protect Sheet From View in a Google Spreadsheet (2 Ways)

Why Do We Need To Lock Cells In Google Sheets?

  • You are sharing your workbook with a large audience. Many workbooks are shared publicly but leaving it on view-only mode may not cut it for every situation. You may need certain sheets or certain cells of your workbook to be restricted from third-party edits.
  • You may need other users to fill up “input” cells. A form for example, where the user may need to fill up certain fields that apply to them, but not change other important cells or fields that have crucial information in them.
  • Specifically, to protect cells that have complex or important formulas. The same applies to references.

Final Words

We hope that the ways of how to lock cells in Google Sheets we have discussed in this article come in handy in your spreadsheet tasks.

Feel free to leave any queries or advice you might have for us in the comment section.

Related Articles

Mehrab Imtiaz

Mehrab Imtiaz

Mehrab Imtiaz is a Technical Writer for specializing in everything Google Sheets. Mehrab has always had a profound passion for writing and with his experience with MS Excel and Google Sheets throughout his career, it seems to be a great match when it comes to dishing out tips and tutorials for all to enjoy.

We will be happy to hear your thoughts

Leave a reply