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!