Check/Uncheck a Tick Box Based on Another Cell: Google Sheets

A checked checkbox holds the value TRUE, while an unchecked checkbox holds the value FALSE in Google Sheets. You can use these TRUE/FALSE boolean values to check or uncheck a tick box based on another cell’s value dynamically. This tutorial will guide you through the steps to check/uncheck a tick box based on another cell.

How to Check or Uncheck a Tick Box Based on Another Cell in Google Sheets

If you want a tick box in cell A2 to be checked based on the value in cell B2, such as “Paid,” you can use this approach:

Enter the formula =B2="Paid" in cell A2.
While keeping A2 selected, go to Insert > Tick Box.

This setup ensures that the tick box in A2 gets checked when the value in B2 is “Paid” and unchecked when B2 contains any other value.

Example of checking or unchecking a tick box based on another cell's value in Google Sheets

Auto-Toggling Multiple Tick Boxes

Can I Apply This to a Range of Tick Boxes?

Yes! You can dynamically check or uncheck multiple tick boxes based on the values in another column. For example, to toggle the tick boxes in A2:A10 based on the values in B2:B10, follow these steps:

Clear any existing data in the range A2:A10.
Enter the following formula in cell A2:=ArrayFormula(B2:B10="Paid")
Select the range A2:A10.
Go to Insert > Tick Box.

This will create a range of tick boxes that automatically toggle based on whether the corresponding cells in B2:B10 contain the value “Paid.”

Real-Life Examples: Check or Uncheck Tick Boxes Based on Cell Values

Example 1: Toggle Tick Boxes for Payments

Imagine you have a column for payment amounts. You want the tick boxes in A2:A10 to be checked when a payment is received (i.e., the amount in B2:B10 is greater than 0). Use this formula in A2:

=ArrayFormula(B2:B10>0)

Then select A2:A10 and go to Insert > Tick Box.

Example 2: Lookup and Toggle Tick Boxes

You can use lookup functions in Google Sheets to toggle tick boxes dynamically. For instance, if you have a list of items in column A and a table in the range D2:E8 that categorizes items (e.g., “Fruits” and “Vegetables”), you can insert tick boxes based on categories.

Example of using a lookup function to toggle tick boxes in Google Sheets

To insert checked tick boxes for all items in A2:A10 that are categorized as “Fruits”:

Enter the following formula in cell B2:=ArrayFormula(XLOOKUP(A2:A10, D2:D8, E2:E8="Fruits"))
Select B2:B10.
Go to Insert > Tick Box.

Where in the formula:

A2:A10 is the search_key range.
D2:D8 is the lookup range.
E2:E8="Fruits" is the result range, which contains TRUE for “Fruits” and FALSE for other items.

For vegetables, modify the formula:

=ArrayFormula(XLOOKUP(A2:A10, D2:D8, E2:E8="Vegetables"))

You can create separate tick box columns for each category if needed. In that case, insert the above formula in cell C2 instead of modifying the formula in cell B2.

Additional 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.