How to Lock and Unlock Cells Using Checkboxes in Google Sheets

Published on

Checkboxes, also known as tick boxes, have many uses in Google Sheets. Dependent checkboxes are my favorite so far, but here’s another great tip: you can lock and unlock cells using checkboxes in Google Sheets.

How It Works

Imagine you want to lock cells in each row and control them with checkboxes. For example, you want to lock/unlock the cells in the range A2:D2 by toggling the checkbox in cell E2.

Example of a checkbox that locks or unlocks cells in a row in Google Sheets
  • When you check the checkbox, the cells lock.
  • When you uncheck it, the cells unlock for editing.

This helps prevent accidental edits to specific rows.

Lock and Unlock Cells Using Checkboxes in Google Sheets

You can control the locking and unlocking of cells in two ways:

  1. Using multiple checkboxes, each controlling a row.
  2. Using a single checkbox to lock/unlock an entire range.

Let’s explore both methods.

1. Lock and Unlock Cells Using Multiple Checkboxes

Follow these steps to configure the cells and checkboxes:

Use checkboxes to control row protection in Google Sheets

Steps:

  1. Insert checkboxes in the range E2:E5.
    • Select E2:E5.
    • Go to Insert > Tick Box.
  2. Select the range A2:D5 (the cells to be locked/unlocked based on the checkboxes).
  3. Go to Data > Data validation.
  4. Click on + Add rule.
  5. Under Criteria, select Custom formula is.
  6. Enter the following formula:=$E2=FALSE
  7. Under Advanced settings, select Reject input.
  8. Click Done.
Data validation rules to reject input based on tick boxes in Google Sheets

Now, when you check a checkbox, the corresponding row will be locked. You won’t be able to edit any content in that row unless you uncheck the box.

For example, if you check the box in E3, the cells in A3:D3 will be locked. To edit them, you must uncheck the box in E3.

Restrict Checkbox Deletion (Additional Layer of Protection)

If you’re the only one using the sheet, this may not be necessary. However, if you share it with others who have editing rights, they may accidentally override the protection.

For instance, they could uncheck all checkboxes and edit the cells or even delete entire rows or columns. To prevent this:

  1. Select E2:E5 (the checkboxes).
  2. Right-click the sheet tab and select Protect sheet.
  3. In the sidebar, click Range > Set permissions.
  4. Under Restrict who can edit this range, select Only you.
  5. Click Done.
Setting permissions for a protected range in Google Sheets

Now, users can edit only the rows where the checkbox is unchecked. They cannot delete the checkboxes or the rows containing them.

To prevent them from deleting columns in the protected range, protect the header row (A1:D1) using the same method:

  • Repeat steps 1 to 5, but in step 1, select A1:D1 instead of E2:E5.

2. Lock and Unlock Cells Using a Single Checkbox

You can control an entire range of cells with a single checkbox. Here, the checkbox is in cell E2, and it locks/unlocks the range A2:D5.

Use a single checkbox to lock or unlock a range of cells in Google Sheets

Steps:

  1. Insert a checkbox in E2.
  2. Select the range A2:D5.
  3. Go to Data > Data validation.
  4. Click + Add rule.
  5. Under Criteria, select Custom formula is.
  6. Enter the following formula:=$E$1=FALSE
  7. Under Advanced settings, select Reject input.
  8. Click Done.

Now, when you check the box in E2, the entire range A2:D5 will be locked. When unchecked, the range will be editable.

Conclusion

With these methods, you can efficiently lock and unlock cells using checkboxes in Google Sheets. Whether you want row-level control or a single checkbox to lock/unlock an entire range, these techniques provide flexible options to protect your data.

Additional Resources

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Cycle Highlights in Google Sheets – Rotate Highlights Daily

Want to cycle highlights in Google Sheets every day? Whether you're rotating a meal...

Filter Rows Containing Multiple Selected Values in Google Sheets

This tutorial explains how to filter rows in a column containing multiple selected drop-down...

Two-Way Lookup with XLOOKUP in Google Sheets

When you need to look up one search key vertically and another horizontally, you...

How to Filter by Total in Google Sheets Pivot Tables

Google Sheets offers many tools to summarize and analyze data, but Pivot Tables are...

More like this

Cycle Highlights in Google Sheets – Rotate Highlights Daily

Want to cycle highlights in Google Sheets every day? Whether you're rotating a meal...

Filter Rows Containing Multiple Selected Values in Google Sheets

This tutorial explains how to filter rows in a column containing multiple selected drop-down...

Two-Way Lookup with XLOOKUP in Google Sheets

When you need to look up one search key vertically and another horizontally, you...

11 COMMENTS

  1. Hi,

    We are using google sheets at school for registrations. All the teachers have to tick checkboxes, but sometimes they accidentally delete them. I want to protect the sheet, still making it possible to check or uncheck, but they should not be able to delete them (or change the formulas). Do you have a solution to this problem?

    • Hi, Liesann Van Mol,

      You can make then only check or uncheck a tick box by inserting the tickbox from the menu data > data validation > criteria > tick box and “on invalid data” settings to “reject input”. Still, the users will be able to delete the tick box.

      So, instead of giving access to your sheet, let them use Google Forms to feed the sheet.

      You may try using Google Forms to feed your sheet.

  2. Hello Prashanth,

    Thank you for your example. When I apply this information in my checkbox, it deletes the other data validation that I have set in the cells that I want to lock. Is there a workaround for this?

    Example: I have data validations set in columns E, F, G, H and I’m wanting to lock Row 2 when I check the box in N2. When I enter the custom formula in data validation it deletes the data validation in columns E, F, G, and H.

  3. Is there any way to have a cell locked by two or more checkboxes? They all would need to be checked before the cell unlocked.

    • Hi, John,

      As an example, if you want to lock and unlock the cell G1 based on the tick boxes in H1, I1, and J1, use the below formula in Data Validation.

      =and(H1,I1,J1)=true

      The “Cell range” in Data Validation should be cell G1.

  4. While this is OK from accidental entries but if someone intentionally or otherwise copy paste values on the cell data validation goes for a toss.

  5. Hi Prashanth ,

    1. It did not work if I delete cell content, 2. The cells stay marked as wrong content.

    Do you have a workaround for these issues?

    • Hi, Julio,

      Sorry to say I don’t have solutions to both of these as data validation works that way only.

      The data validation will permit users to delete content. If you want complete protection of selected range, use the Data > Protected Sheets and ranges command.

      Thanks.

  6. Dear Prashanth,

    Thanks for this wonderful website about google sheets. On this page, you explained about locking rows with checkboxes. What a great idea!

    I could now put the checkboxes on another sheet and hide the sheet!! great. But would this also be possible on columns instead of rows?

    I use one column to enter a number and with Vlookup fill the other cells. It would be nice to lock all columns instead of the column I use to enter the search value.

    • Hi, Leen Jonkman,

      I fear I didn’t understand your query correctly!

      On this post, I detailed about locking a range of cells (A2:D5), not a single row or column.

      In data validation, if you want, you can select a single column range like A2:A100.

      There are no changes in the steps involved. Just change the “cell range” in the data validation settings.

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.