Tickbox also known as Checkbox widely used to track the given tasks. In Google Sheets, the user can assign custom values to the tickbox and the best part about the tickbox is that user can also apply conditional formatting to the tickbox, which makes it more attractive to track the list of given tasks.
In this tutorial, we will show you how to insert the Tickbox, apply conditional formatting to it, and use with “COUNTIF” and “SUMIF” formulas.
Disclaimer: Google Sheets has updated the name of the “Checkbox” option to the “Tickbox” option with no change in its functionality.
Insert a Tickbox in Google Sheets
- First, go to the cell or select the range of cells in which you want to insert the checkbox.
- After that, go to the “Insert” option and click on the “Tick box” option.
- The moment you click on the “Tick box” option, it will insert the tickbox (aka checkbox) into that cell
To copy the tickbox to other cells, just copy the cell that has the tickbox and then select the cells where you want to insert it and simply paste it there and the tickbox will also get inserted over there.
Add Custom Values to the Checkbox in Google Sheets
By default, the tickbox indicates TRUE and FALSE as values. If the tickbox is ticked, Google Sheets consider it as “TRUE” and unticked as “FALSE”. But there is the ability to customize those values of your own choice and to do so follow the fellow steps:
- First, select the cells with tickboxes and go to the “Data” tab and then click on the “Data validation” option.
- After that, choose the “Tick box” under the criteria field using the drop-down arrow.
- Now, enter the custom value for ticked and unticked “Tickbox” (as we have entered “YES” for ticked and “NO” for unticked) and then click “Done”.
- Once you click “Done”, the custom values will get updated.
Insert Tickbox with Conditional Formatting in Google Sheets
Google Sheets has the option to apply conditional formatting to the tickbox. By applying the conditional formatting, you can highlight the cells that contain the tickbox when gets ticked and also the corresponding cells that have data/task for which the tickbox got ticked.
- First, select the cells that have the tickboxes inserted in them and go to the “Format” tab and then click on the “Conditional formatting” option to open that.
- After that, click on the drop-down arrow in the “Format cells if” field and choose “Test is exactly” and then enter “TRUE” in the field below that.
- In the end, choose the formatting style to highlight the cell if gets ticked and click “Done.
- If you also want to add the same kind of formatting to the un-ticked tickboxes, click on “Add another rule” and this time instead of the value “TRUE” put “FALSE”.
- At this point, the custom formatting is got applied to the tickbox cells.
Note: If you have added your custom values to the tickbox, then you have to enter that values while applying the conditional formatting instead of “TRUE” and “FALSE”.
Highlight Data Using the Checkbox with Conditional Formatting in Google Sheets
- First, selects the corresponding cells to the tickbox cells and then go to the “Format” tab and then click on the “Conditional formatting” option.
- After that, click on the drop-down arrow in the “Format cells if” field and choose “Custom formula is” and then enter “=$B2” in the field below that as we have tickboxes in column B.
- In the end, choose the formatting style to highlight the cell if gets ticked and then click “Done.
- At this point, the custom formatting is got applied to the cells corresponding to the tickbox cells.
In the above example, we had chosen cells to get highlighted as green and strike-through in formatting style when the corresponding cells (Column B) will get ticked.
Use Tickbox to Count with If Formula in Google Sheets
You can count the tickboxes (ticked or unticked) by using them with the “COUNTIF formula.
- First, go to any blank cell and enter the “COUNTIF” formula to count the tickboxes which are ticked or unticked as you want.
- In the “COUNTIF” formula, first, specify the range of cells that have tickboxes in them as we have specified (B2:B7) in our case.
- After that, specify the criteria as “TRUE” to count the ticked cells or “FASLE” to count the unticked cells.
- If you have given the custom values to ticked and unticked tickboxes, enter those values within double quotations instead of “TRUE” and “FALSE”.
In the below image, you can see it has given the resulting count as 3 as we have only three tickboxes that are ticked.
Sum Values if Tickbox is Ticked in Google Sheets
You can sum the values corresponding to the tickbox cells using the “SUMIF” formula.
- First, go to any blank cell and enter the “SUMIF” formula to sum the tickboxes which are ticked or unticked as you want.
- In the “SUMIF” formula, first, specify the range of cells that have tickboxes in them as we have specified (C2:C7) in our case.
- After that, specify the criteria as “TRUE” to sum the values corresponding to the ticked cells only or “FASLE” to sum the unticked cells.
- In the third part, specify the range of cells that have the data to sum based on the condition applied in column C “TRUE” in this case.
- If you have given the custom values to ticked and unticked tickboxes, enter those values within quotations instead of “TRUE” and “FALSE”.
- In the below image, you can see it has given the resulting sum of 149880, which is the sum of 3 cells whose corresponding tickboxes are ticked.
Remove the Tickbox
To remove the tickboxes from the cells, simply select the cells and then press “Delete” from the keyboard.
Insert Tickbox on Android Device
- First, open the spreadsheet on your Android device using the “Google Sheets” app.
- After that, select the cells, in which you want to insert the tickboxes and then click at the top right three dots as shown in the image below.
- Now, click on the “Data validation” option from the drop-down.
- In the end, under the “Criteria” list, choose the “Tick box” and then click on “Save”.
- At this point, the checkbox will get inserted within the selected cells.
Note: If you have any text or formula in a cell and you insert a checkbox in it, Google Sheets will remove that text/formula and replace it with the checkbox.