Check-Uncheck a Tick Box based on Value of Another Cell -Google Sheets

The interactive tick boxes/checkboxes are part of Google Sheets. Here is an interesting topic regarding checkboxes, i.e., how to check-uncheck a Tick Box based on the value of another cell in Google Sheets. I am going to talk about dependent tick boxes.

I have a checkbox in one cell or a range of cells. Can I get this/these checkboxes dynamically checked/unchecked? I mean toggle the checkbox automatically when a value in another cell changes?

Yes! It’s possible. First, see how the tick boxes got automatically toggled when I change the values in other cells.

Auto Toggling of Checkboxes in Google Sheets

In this live screenshot as you can see, when I type “paid” in column B the corresponding checkboxes in column A get checked automatically. Because the checkboxes contain formulas instead of the Boolean TRUE/FALSE.

This is very simple to do. Let’s go to that cool tip.

How to Check-Uncheck a Tick Box based on Value of Another Cell

First, insert a few tick boxes. Here I am inserting the tick boxes in the range A2:A10.

Steps:

1. Select the cell A2:A10 and from the menu Insert, insert the tick boxes.

So you have a bunch of unchecked tickboxes in the range A2:A10. The default values now in these cells are FALSE (the value of unchecked checkbox). I am assigning formulas to these checkboxes.

Now see how to dynamically check/uncheck a tick box in Google Sheets.

Here what I am doing is controlling the checkbox toggling from a different column. Here I am using Column B for this purpose.

2. In cell A2, yes I mean cell A2 which already contain a tick box, double click and enter this formula.

=if(B2="Paid",TRUE,FALSE)

Then drag this formula down (copy down) that up to the cell A10. You can’t use an ArrayFormula here.

3. Now type “Paid” in B2 or any cell in the range B2:B10. You can see that the checkbox automatically got toggled.

You can use this dynamic behavior of tick boxes in some real-life examples. For example, toggle tick boxes automatically when you input amount in a payment receipt column.

So the checkbox got checked when you receive payments. If the checkbox is in cell A2 and the value (amount) is in B2, enter this formula in A2.

=if(AND(B2>0,ISBLANK(B2)=FALSE),TRUE,FALSE)

Here is one more cool tip in line with the above checkbox tip. This time I am using a drop-down to uncheck or check all checkboxes in Google Sheets dynamically. First, see it in action.

Dynamically Check/Uncheck Checkboxes in Google Sheets Based on Drop-down Value

In cell B2 I have set a drop-down menu. If you select “Paid” all the tick boxes in the range A2:A got checked. The selection of “Unpaid” make the checkboxes, unchecked.

check - uncheck all tick boxes via drop-down

I guess you already know how to set a drop-down as above using data validation in Google Doc sheets.

Data validation is a Data menu option. If you are not familiar, refer to this setting.

data validation rule for dynamic tick box

Now you can use the earlier formula in cell B2. But this time make the cell reference absolute. See the modified formula below.

=if($B$2="Paid",TRUE,FALSE)

Drag this formula down that up to the cell A10.

That’s all about how to check-uncheck a tick box based on the value of another cell in Google Sheets.

Hope you have liked this tutorial on dynamically check checkboxes in Google Sheets. Thanks for the stay. Enjoy!

More Resources:

31 COMMENTS

Missy Jennings May 11, 2023 At 1:27 am

I need a checkbox to auto-check when any cells in a range contain the word “Get”.

Is this doable?

I need the C32 checkbox to check if any cell in A3:A14 contains the word “get” in them.

- Prashanth May 11, 2023 At 3:15 pm
  
  Hi, Missy Jennings,
  
  I’ve keyed in the following solution in cell C32. Please check your Sheet.
  
  =IFNA(XMATCH("*get*",A3:A14,2))>=1
  
Tom April 28, 2023 At 5:45 am

Hi,

This may not be able to be done, but I am hopeful it is.

I want to create a competency form for my trainers to fill out every time a trainee completes a competency, and it will then import the data from the form to a sheet inside the workbook.

From there, I want a check box that will search through the names of the form responses and tick a checkbox on an overview sheet corresponding with the name, and the competency completed.

I would like them to automatically update the checkboxes, whether every time a response is submitted or whenever I don’t mind.

I hope this makes sense. I can try to attach photos of each sheet to make it a little easier to understand if this doesn’t make sense.

- Prashanth April 28, 2023 At 8:24 am
  
  Hi, Tom,
  
  Can you share a copy/sample of your sheet instead of screenshots?
  
  I’ll try then.
  
Lizzy September 8, 2022 At 9:52 pm

This is all really helpful.

Essentially, I want to ensure that only one of the three tick boxes is able to be ticked at a given time.

I’m still trying to figure out my footing when it comes to formulas, so grace is appreciated!

- Prashanth September 10, 2022 At 11:05 am
  
  Hi, Lizzy,
  
  It doesn’t seem possible since the formula applied cell becomes unclickable.
  
  The workaround is to limit entering values in those cells, e.g., if C2, D2, and E2 are the cells, you can use the following formula in Data Validation > Criteria > Custom Formula field.
  
  =COUNTA($C$2:$E$2)<2
  
  Check "Reject input," and the cell range must be C2:E2.
  
  It will make users enter a value (e.g., a ✔ symbol) in only one of the cells in C2:E2.
  
DeeMarie June 20, 2022 At 9:05 pm

I have a checkbox in column Z. I have a date (birthdate) in the H column.

How can I have the checkbox check automatically when the age of the person is below the age of 16?

I can add a column that calculates the date (we’ll say column AA).

- Prashanth June 22, 2022 At 12:15 pm
  
  Hi, DeeMarie,
  
  I assume the data is as follows.
  
  DOB in H2:H
  Tick boxes in Z2:Z.
  
  Then in Z2, insert the following Datediff formula and copy it down.
  
  =datedif(H2,today(),"Y")<16
  
Ela May 4, 2022 At 10:40 pm

Thank you for this.

I’m wondering though, is it possible to have a checkbox be ticked if one or more checkboxes on other sheets get ticked?

For example, if I have three people out of six with their own sheets that check a box, can a box on another sheet get ticked?

- Prashanth May 8, 2022 At 4:02 pm
  
  Hi, Ela,
  
  Are you referring to six workbooks (Sheets) or that many tabs within a single workbook?
  
  In both cases, we might be able to do that. Please elaborate and feel free to share a sample of your Sheet below.
  
  - Ela May 10, 2022 At 3:27 am
    
    It’s one workbook with several tabs inside it.
    
    - Prashanth May 11, 2022 At 9:36 am
      
      Hi, Ela,
      
      Yep! It seems possible.
      
      Example:
      
      Master Sheet:
      
      Sheet1 is the master sheet that contains the following data.
      A2:A – the candidates’ names
      B1, C1, and D1 – tab names Joe, Jan, and Sharon (interviewers).
      
      Other Sheets:
      
      Joe (tab name)
      Jan (tab name)
      Sharon (tab name)
      
      These three sheets contain the following data.
      A1:A – the candidates’ names
      F1:F – tick boxes (interview result column)
      
      In Sheet1!B2, i.e., master sheet, insert the following formula and copy it to C2 and D2.
      =ArrayFormula(ifna(vlookup($A$2:$A,indirect("'"&B1&"'!A1:F"),6,0)))
      Select B2:D and apply Insert >Tick box.
      
Josh February 12, 2022 At 1:53 am

Thank you for these tutorials.

So is it true that it is not possible to make an “Uncheck All” box (or drop-down selection) while allowing the other boxes to be interactive?

- Prashanth February 12, 2022 At 8:43 am
  
  Hi, Josh,
  
  For that, you can get the help of Macros.
  
  Steps:
  
  1. Go to Extensions > Macros > Record Macro.
  2. On the dialog box that appears, check/enable “Use absolute references.”
  3. Select the tick boxes on the sheet.
  4. Tap the Space bar twice.
  5. Save the Macro.
  
  - Josh February 14, 2022 At 10:26 pm
    
    Thank you, Prashanth! I’ve very appreciative of the wealth of knowledge you have here.
    
Tyler January 27, 2022 At 5:59 am

I’m having an issue with the formula when I replace the word “Paid” with a value in another cell (i.e., “24”). Is there a way to correct this?

- Prashanth January 28, 2022 At 8:26 am
  
  Hi, Tyler,
  
  I am not clear about your question. Can you elaborate or share an editable sheet via “Reply” below.
  
Alex June 9, 2021 At 8:58 am

Hi,

How can I make the tick boxes in column A to be checked automatically if the adjacent cell from row B is checked?

For example: If I check B5, then A5 to be checked automatically as well.

Thank you.

- Prashanth June 9, 2021 At 9:56 am
  
  Hi, Alex,
  
  Insert tick box in cells A5 and B5. Then in A5 type the following formula and hit enter.
  
  =B5=TRUE
  
  That’s it!
  
  When you check B5, A5 will be checked automatically. But the problem is A5 won’t be interactive anymore!
  
Jennifer April 22, 2021 At 10:13 pm

I must be missing something. If I type a formula into a cell with a checkbox then the checkbox goes away. How do you get it to stay as a checkbox?

SmilinJack March 12, 2021 At 8:38 am

How could you code this into a script so that when cell C contains the text “Approved” the checkbox in cell D is checked? I enjoy reading your clear descriptions and find them very useful!

- Prashanth March 15, 2021 At 12:32 pm
  
  Hi, SmilinJack,
  
  Empty D1:D. Then insert the below formula (array formula) in D1.
  
  =ArrayFormula(if(len(C1:C),if(C1:C="checked",TRUE,FALSE),))
  
  Select the values in D1:D, click on Insert > Tick box.
  
iowaniklas February 8, 2021 At 11:55 pm

Is it possible to automatically check a tick box if all other tick boxes in a specific range are checked? For example, I want the tick box at A4 to be checked if the tick boxes from A5 to A11 are all checked.

- Prashanth February 10, 2021 At 4:57 am
  
  Hi, iowaniklas,
  
  Yes, it’s possible using the below formula in cell A4.
  
  =if(COUNTIF(A5:A11,TRUE)=7,TRUE,FALSE)
  
Helena M June 3, 2020 At 3:38 am

Thanks for this Prashanth – this was super helpful! However, I need to go one step further with my formula if possible…

Using your example of:

=if(B2="Paid",1,0)

I’ve modified this to take in to account a couple of other factors in my data set. Namely:
– Another worksheet in the same workbook happens to be a ‘form responses’ sheet.
– Full column instead of just one other cell.
– Boolean search modifier * to return a root word/stem/truncation value.

This is the formula I’ve entered in:

=if('OTHERSHEET'!B:B="*DSC*",1,0)

When I enter this, it seems to break the formula so it doesn’t work. Although there is no error message, it always generates a FALSE or 0 value in the checkbox cell even when the criteria should read as TRUE / 0 based on the targetted cell.

Do you have any advice on whether this is possible and if so, how I can modify my formula?

Thank you!
H

- Prashanth June 4, 2020 At 5:26 pm
  
  Hi, Helena M,
  
  As far as I know, it’s not possible. Your formula is not correctly coded. Even if it’s correct, the formula would return #REF! error as it won’t overwrite the tickboxes.
  
  Please note the below two points that would help you advance in Google Sheets.
  
  To use a full column instead of just one other cell, you must enter your formula as an Array Formula. Either wrap your IF formula with the ARRAYFORMULA() function or hit Ctrl+Shift+Enter.
  
  To return a root word/stem/truncation value in IF, do not use the * wildcard character. The said function doesn’t support it. Then?
  
  Please follow this – Partial Match in IF Function in Google Sheets As Wildcard Alternative.
  
azahari August 12, 2019 At 5:32 pm

Is it possible if the tick box is ticked manually or automatically by referencing other calls at the same time?

- Prashanth August 13, 2019 At 8:07 am
  
  Hi, Azahari,
  
  It’s not possible as there is a formula in the tick box cell. So you can’t manually check it.
  
  Best,
  
JD August 9, 2019 At 4:25 am

Thank you for this, but I do have some question for the =if(B2="Paid", TRUE, FALSE) formula

What if I have a drop-down option with “venmo”, “cash”, “paypall”, “other” and want the box to be checked if any option is selected remains unchecked if no value is selected?

I’m having issues “grouping cells” on Google Sheets, and Excel is not really an option for me.

Please help!

- Prashanth August 9, 2019 At 12:26 pm
  
  Hi, JD,
  
  You can use this formula that uses OR in IF.
  
  =if(or(B2="venmo",B2="cash",B2="paypall",B2="other"),TRUE,FALSE)
  
  Best,
  
Jennifer H July 9, 2019 At 12:34 am

Thanks so much for the IF statement modification of the checkbox! It was exactly what I needed to make a list beautiful and matching some other checkboxes.