Tick Boxes can help you in different ways to organize your data. We can use Tick (✓) Boxes to indicate the status of activities in a list. You can find below 10 useful Tick Box tips and tricks in Google Sheets that I’ve come across. I hope you will find it useful.
The Tick Box in the Google Sheets is interactive. When checked it indicates TRUE, which means “Yes” the activity is completed or the answer is correct. By default it’s FALSE.
Here are my 10 useful Tick Box tips and tricks in Google Sheets for all the passionate Google Sheets users out there.
Some of the tips are already detailed in my own earlier posts. In such cases, I will give you only a brief description here and provide you the link to my earlier detailed tutorial.
10 Must Learn Tick Box Tips and Tricks in Google Sheets
Here are the possibly best Tick Box Tips and Tricks in Google Sheets. Hope this can improve your productivity.
1. Insert Tick Box in Google Doc Spreadsheets
It’s a straightforward approach. Just select the cells where you want the Tick Boxes to be inserted. Then go to the menu Insert and click on “Tick box”. That’s all that you want to do to insert the interactive tix boxes.
2. Count Checked and Unchecked Cells
It’s so simple. Suppose I have few Tick Boxes in the range A1: A. I can use the following COUNTIF formula to count the checked boxes.
=countif(A1:A,TRUE)
Just change the TRUE to FALSE in this formula to count the unchecked boxes.
3. Conditionally Change The Tick Box Color
From my pick of the best 10 Tick Box tips and tricks in Google Sheets, this conditional formatting is my favorite one.
By default, the color of the Tick Box is dark grey. You can change that color hassle-free.
The Tick box is actually a Character. You can select it and change the color just like any font. But here I am going to selectively change the Tick Box color.
I mean I am going to change the color of the checked Tick Boxes as above. Let’s see how to change the color of the Tick Box.
1. Select the cells with the Tick Boxes. Here, for example, I am selecting the range D4: D8.
2. Go to the menu, Format > Conditional formatting.
3. Set the conditional formatting rule as below.
Similarly, you can change the color of the Tick Boxes in the range E4: E8 and F4: F8. Here is a more detailed tutorial on this.
Must Read: Change the Tick Box Color While Toggling in Google Sheets.
4. Extract the Rows Containing Ticked/Checked Tick Boxes
I have a list of student names in Google Sheets. The first column contains the student names and the second column contains the Tick Boxes.
How to extract the rows containing the Tick Boxes that ticked?
The easiest solution is Query.
=QUERY(A1:B,"Select * where B=TRUE")
Also, you can use a logical IF statement as below.
=ArrayFormula(SORT(if(B1:B=TRUE,A1:B,)))
5. Create Dynamic Charts by Controlling Chart Data Using Tick Boxes in Google Sheets
This is my second favorite tip under my pick of 10 Tick Box tips and tricks in Google Sheets.
When you want to report your project status to your client on a weekly, fortnight, or monthly basis you can depend on a combination Chart.
If you have premium project management tools, then there is no question of this arising. You can use the Gantt Chart (Timeline) in such tools.
You May Also Like: Create a Gantt Chart Using Wrike Online Project Management Software
The Combo charts are useful in Google Sheets for monitoring the progress of your job like how the job is progressing against the schedule.
See this data. This shows a sample project schedule and its progress/completion status. This is a finished project. If you compare the Schedule (column B and C) and Actual (column D and E), you can see that I had to complete the project in 5 weeks but it took 6 weeks to complete.
For you to understand the data, here is the data break up.
Chart Data:
Column A: I had got a weekly schedule along with a Job Order (Work Order) to timely complete the project. There were 5 weeks allowed. But I took one additional week to complete the project. That week’s numbers can see in Column A.
Column B: If you sum Column B, you will get the value 100. It’s the total % Weightage of the tasks involved in the project.
What is the % Weightage?
You can say the total % Weightage of a project is 100.
The planning person assigns (distributes) this 100% Weightage to individual tasks considering several factors like time, manpower required and sometimes he even considers the money factor.
In the allowed five weeks, each week involves several tasks. Its % Weightage got summed and that is the values in Column B.
Colum D: It’s the actual % Weightage of the completed tasks.
Columns C and D are the cumulative values of the above two.
The Role of Tick Boxes in Dynamic Chart in Google Sheets
As you can see there are two Tick Boxes that in the cells G2 and H2. What is the role of these Tick Boxes there?
I am using these Tick Boxes to control the above data. I am going to use this formula in cell A10.
=if(and(G2=TRUE,H2=TRUE),{A1:E7},IF(and(G2=TRUE,H2=FALSE),{A1:B7,D1:D7},IF(and(G2=FALSE,H2=TRUE),{A1:A7,C1:C7,E1:E7})))
What does this formula do?
If both the Tick Boxes are checked, this formula extracts the entire data in the range A1: E7, if the first Tick Box is only checked, it only extracts the weekly data (Column A, B, and D), and when the second Tick Box is only checked it extracts the cumulative data (Column A, C, and E).
How to Create a Dynamic Combo Chart with Tick Box Control in Google Sheets?
Here are the steps to insert a dynamic combo chart in Google Sheets.
1. Select the data in the range A10: E16.
2. Go to the menu Insert > Chart.
3. Do the following changes in the Chart editor.
Under Data Tab:
Chart type: Combo Chart.
Stacking: None.
Under Customize Tab:
First, find the Series drop-down.
Change both the series that starts with “Cum” to;
Type: Line
Axis: Right
Change both the series that starts with “Weekly” to;
Type: Column
Axis: Left
That’s all. Your dynamic combo chart is ready. You can now easily control the chart series using the Tick Boxes. You can copy the chart from Here and do your experiment.
Must Read: How to Get Dynamic Range in Charts In Google Sheets
6. Carry or Retain Tick Boxes with Import Range or Other Functions
Just like any text, you can copy and paste Tick boxes into different cells. But if you use a formula to copy, you won’t get the Tick Box. Instead, you will only get the value of the Tick Box, which is TRUE or FALSE.
If you use the IMPORTRANGE function to import a dataset with Tick Boxes, you will face the above issue. It’s applicable to Filter and Query also. For example, I have inserted one Tick Box in cell A1.
=A1
In B1, the above formula will only return the value FALSE when unchecked and TRUE when checked. But you can try this workaround to get a Tick Box similar character.
Example:
I am inserting Tick Boxes in the range A1: A10. I should insert it as follows.
Steps:
1. Select the Cells A1: A10.
2. Go to the menu Data > Data Validation and set it as follows. Against the Ticked and Unticked, you can copy the below characters.
Unticked: ☐
Ticked: ☑
Now you can see the above characters instead of TRUE/FALSE in your imported or extracted data.
7. In Google Sheets How Do I Select and Deselect Multiple Tick Boxes at a Time?
Do you know how to select and deselect multiple Tick Boxes at a time? It’s very simple. Just select the Tick Boxes and hit the spacebar.
8. Tick Box in To-Do Lists
The just above screenshot is an example of the use of Tick Boxes in a to-do list. Now let me explain how to apply strike-through in a completed activity with the help of Tick Box.
In the above to-do list, my data range is A4: D8. Go the conditional formatting and set the rule under “Format cells if…” as above.
When you check the Tick Box, Google Sheets will strike through the contents in that row.
9. Highlight Rows Individually
When I make a tick, I want that row to get highlighted. How to do that?
This is actually another conditional formatting-related topic. I’ve detailed it already Here.
10. Delete Tick Boxes in Google Sheets
Before concluding the topic 10 Tick Box Tips and Tricks in Google Sheets, let me explain how to delete Tick Boxes.
To delete Tick Box, you can just select the Tick Box and hit the Delete button. You can also do it from the menu Edit > Remove Tick Boxes.
One final question?
Can I insert Tick Boxes via a shortcut in Google Sheets? Nope! But you can record a macro and generate a shortcut.
Must Read: How to Record and Run Macros in Google Sheets
That’s all. Enjoy!
I have a pretty interesting and complex problem with checkboxes – I will do my best to explain. I don’t think there’s a solution, but I’d love to make sure.
On Tab A – Column 1 is “First Name”, Column 2 is “Last Name”, Column 3 is all checkboxes.
On Tab B – In Tab B Column 1, I use an ARRAYFORMULA to combine “First Name” and “Last Name” from Tab A into “Full Name”. In Column 2, I have all checkboxes. The checkboxes in Tab B Column 2 are used to indicate something different than the checkboxes in Tab A Column 3.
Here’s when things break…I go to sort first or last name in Tab A, my Tab A check marks re-sort as intended along with the name, my full names in Tab B re-sort as intended, but my checkmarks in Tab B remain fixed in their previous cells and do not re-sort making my data in Tab B now incorrect.
This is the undesired behavior that I’m hoping to solve.
Hi, Danielle Epstein,
I have a similar tutorial here – Align Imported Data with Manually Entered Data in Google Sheets.
Let’s apply the same logic here to solve your tick box problem.
Solution:
You require a Unique ID column in your table. So that we can keep the data aligned in different tabs. Here is my example sheet.
https://docs.google.com/spreadsheets/d/1IebnZaHI98vGwGQ8BeD1vNDn9dzVoDQ3TSrE0ZhOur8/copy
Tab A:
Column 2 is for “First Name”, Column 3 is for “Last Name”, and Column 4 is for checkboxes. Column 1 is an additional column that contains some unique IDs.
Tab B:
In Column 1, I have copy-pasted all the unique IDs from Tab A, Column 1.
In column 2, I have used a flexible array formula to combine “First Name” and “Last Name” from Tab A Column 2 and 3 into a “Full Name”. Further it keeps aligned to the Unique IDs in column A. For that uses a Vlookup.
Column 3 contains tick boxes.
The column 2 data will always keep its position by aligning to the unique IDs. So if you sort the data in Tab A, it won’t affect the data in Tab B. So you can have different tick boxes in Tab A and Tab B.
Tab C:
It’s for sorting Tab B as per the data in Tab A. It has no other purpose. If you don’t want, you can delete this tab.
I have checkboxes on column B and checkboxes on column D. When cell on column B is checked(true) how to make column D uncheck automatically.
Thank you.
Hi, giovanni,
You can do that as follows.
Assume you have Checkbox in cell B1 and also in cell D1.
In cell D1, enter the below formula (yes! you can overwrite on the existing Checkbox there).
=if(B1=TRUE,FAlSE,TRUE)
It has one issue. By default, this formula makes the Tick box in D1 to TRUE. When you uncheck Tick box in B1, the Tick box in D1 will also get unchecked.
Hope this may help.
Looks good so far, I just can´t have D1 always true… I need three stages, to be done(checkbox unchecked D1) done(box checked D1)..then Item sent(checkbox true B1)
Hi, I may need additional information to give you a proper reply.
Is there any other cell involved other than cell B1 and cell D1. If so, we can code the formula using Logical AND/OR.
Like;
=if(and(B1=TRUE,F2="to be done"),FALSE,TRUE)
In this cell B1 to be TRUE and F2 must contain the string “to be done”, then only the checkbox in cell D1 will be unchecked (FALSE), else checked (TRUE).
This way you can develop this logical test formula. If you can share an example Sheet, I may able to help.
Useful Link: Combined use of If, AND, OR logical functions.