There are so many uses of tick mark aka checkboxes in Google Sheets. Dependent checkboxes are my favorite ones so far. Here is one more awesome tip using checkboxes. You can lock and unlock cells using checkboxes in Google Sheets.
It works like this. Assume I want to lock certain cells in each row and control them with checkboxes. For example, I want to lock/unlock the cells in the range A2:D2 by toggling the tick box in cell E2.
When I check the tick box, I want the cell to lock and when I uncheck, I want the cell to unlock for editing. So I can make sure that the cells are not getting edited by accident.
See what happens when I try to enter a value in a locked cell. The cell B2 is locked using the tick box in E2.
See how to lock and unlock cells as above using checkboxes in Google Sheets.
You can control the locking and unlocking of cells in two ways. You can either use a single checkbox or multiple checkboxes in each row. Let me show you both the tips.
Lock and Unlock Cells Using Multiple Checkboxes
Follow the below steps to configure the cells and tick boxes.
Steps:
- The first thing is to insert checkboxes in the cells (cell range) E2:E5. To do that select these cells and then go to the menu Insert and click on Tick box.
- Then select the range A2:D5 which we are going to lock and unlock using the checkboxes in E2:E5.
- Go to the menu Data and click on Data Validation. Set the data validation rule as shown in the image below.
That’s all that you want to do to lock and unlock cells using Checkboxes in Google Sheets.
When you check a tick box, you are locking the cells in that row. You can not edit any content in that row. To edit, uncheck the tick box.
For example, if I check the tick box in cell E3, the cells in the range A3:D3 will be locked. I can’t edit the content in the range A3:D3. I should uncheck the tick box for editing permission.
In the above example, there are checkboxes in each row in the range. But with a single checkbox, we can lock and unlock an entire range in Google Sheets. Here is that tip.
Lock and Unlock Cells Using One Single Checkbox
You can control an entire range of cells with one single tick box. This time I am again locking the above same data range, i.e. A2:D5.
Here the tick box is only in the cell E1 that controls the entire range.
Here the data validation setting is the same except the custom formula. The formula is as below.
=$E$2=FALSEThis way you can lock and unlock cells using Checkboxes in Google Sheets.
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.
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.
Hi, Dawn Hornaday,
There is no workaround as multiple data validations for the same cell/cell range is not supported.
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)=trueThe “Cell range” in Data Validation should be cell G1.
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.
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.
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.