How to Add Checkbox in Google Sheets

Whether you want to create a to-do list, track attendance, or manage a project, checkboxes offer a simple yet powerful way to create interactive workbooks for the user or yourself. In Google Sheets, inserting and using checkboxes is quite easy compared to Microsoft Excel.

• It’s quite easy to insert checkboxes in Google Sheets (Insert > Checkbox)
• When you insert a checkbox, it goes straight into the selected cell.
• When you check or uncheck a checkbox, the value in the cell changes.
• The best use of checkboxes is to manage to-do lists.
• Removing a checkbox from a cell is super easy; you can use the Delete key.

Now, in this tutorial, you will learn to use the checkbox in Google Sheets for all its applications. So let’s get started…

Insert a Checkbox in Google Sheets (Steps)

There are two ways to insert a checkbox in Google Sheets. You can use any of these methods that suit you the best.

1. First, select the cell where you want to add the checkbox and then go to the Insert menu. Now, in the “Insert” menu, you must click the “Checkbox” option. And the moment you click on this option, you will get a check box in the selected cell.
   
2. The second way is to copy and paste a checkbox from a cell where you already have one. When you copy and paste a checkbox from a cell and paste it somewhere else, the new checkbox instantly connects to the new cell.
   

Insert Multiple Checkboxes in one Go.

You may want to have more than one checkbox in that case; either you can use the copy-paste method, which we have discussed above, or you can also use the data validation option.

First, select the cells where you want to add checkboxes. Then, go to the data menu. Once you scroll down, click on the data validation option.

Now, in the data validation option, you need to click on the “Add Rule” to create a new rule for the data validation. And after that, select the “Checkbox” from the criteria drop down and click “Done”.

This will insert checkboxes into the cells you have selected.

You can also use the fill handle from the insert checkboxes to all the cells downwards. When you have a checkbox in a cell, please select it and then use the fill handle to drag it downwards.

Deleting Checkboxes

If you don’t need a checkbox, you can delete it. Here are the different methods to remove checkboxes from your worksheet:

1. Go to the Edit menu at the top, and from the dropdown menu, select Clear > Values to clear all the content, including the checkbox from the selected cells.
2. Select the Range of cells containing the checkboxes you want to delete, and then press the Delete key on your keyboard.

You can also use the Google App Script below to remove checkboxes from the worksheet in one go (CODE BELOW). To use this code, go to the Extensions > App Script. Then, paste the code there and click on the “Run” button to run the code to remove all the checkboxes.

function removeAllCheckboxes() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var range = sheet.getDataRange();
  var formulas = range.getFormulas();
  var values = range.getValues();
  
  for (var i = 0; i < formulas.length; i++) {
    for (var j = 0; j < formulas[i].length; j++) {
      // Check if the cell has a checkbox
      if (sheet.getRange(i + 1, j + 1).isChecked() !== null) {
        sheet.getRange(i + 1, j + 1).clearDataValidations();  // Remove checkbox
        sheet.getRange(i + 1, j + 1).setValue('');  // Clear the cell value
      }
    }
  }
}

Using Custom Values in a Checkbox with Data Validation

When you enter a checkbox in a cell, it stores a value based on its state. If a checkbox is ticked, that value will be TRUE, and if it’s unticked, then it will be FALSE.

These are the default values in the cell all the time. But you can use custom values if you want to use a checkbox further in a formula or something else. First, you need to get to Data > Data Validation. In the Data validation, click on the validation rule.

In the Data Validation dialog box, set the criteria to the checkbox if it still needs to be selected. Then, check the option “Use custom cell values”. Enter the desired values for Checked and Unchecked states.

• Checked value: Yes
• Unchecked value: No

In the end, click “Save” to save the values.

You can use these custom values to count checkboxes that are checked or unchecked.

Apply Read-Only on a Checkbox

You can use the lock cells option in Google Sheets to make a check read-only. By protecting the cell, you prevent other users from changing the state of the checkbox. Only the user who set the protection can modify the checkbox.

1. Select the cell(s) containing the checkbox, right-click it, and go to View More Cells Action > Protect Range from the right-click menu.
   
2. Click Add a sheet or range in the “Protected sheets and ranges” sidebar. Enter a description for the protected Range (optional) and click on Set permissions.
   
3. In the permissions dialog, choose “Only you” to restrict editing to yourself. In the end, click “Done”.
   

You can allow specific users to edit the checkbox and set custom permissions in the Protect range dialog. To make the checkbox editable again, go to the “Protected sheets and ranges” sidebar, select the protected Range, and either delete the protection or adjust the permissions.

Using Conditional Formatting with a Checkbox for Duplicate Values

You can use a checkbox and conditional formatting to create a mechanism to highlight duplicate values from a range of cells. With this, you can highlight values whenever you want and then turn them off when you want.

1. First, select the Range of cells where you want to highlight duplicates (A1:A10), go to Format > Conditional formatting, and Under “Format cells if,” select “Custom formula is”.
   
2. Now, enter this custom formula (=AND(COUNTIF($A$1:$A$10, A2) > 1, $B$1 = TRUE)), assuming your data starts in A1 and the checkbox is in B1. In the end, specify the formatting style (e.g., a background color) to highlight the duplicates. Then click Done.
   

When you click on the check mark, it will highlight the duplicate values.

The formula we have used works in the following way:

• COUNTIF($A$2:$A$20, A2) > 1: This part of the formula checks if the value in the cell is duplicated within the Range.
• $B$1 = TRUE: This part checks if the checkbox is checked.
• AND(): Combines the two conditions to apply the formatting only if both conditions are met.

Use IF Function with Checkboxes

In Google Sheets, you have a column for tasks and a column to mark if they are completed. To make this easy, you can use checkboxes to show whether a task is done.

You insert a checkbox next to each task. Then, you use a simple formula to display “Task Completed” if the checkbox is checked and “Task Not Completed” if it is not.

• In column A, enter your tasks (e.g., “Write project plan”, “Gather resources”, etc.) and select the cells in column B where you want to insert checkboxes (e.g., B2 to B6).
• After that, in cell C2, enter the formula =IF(B2=TRUE, “Task Completed”, “Task Not Completed”). The fill handle is used to drag the formula from the bottom-right corner of cell C2 down to cell C6.

Here, for each row, the formula checks if the checkbox in column B is checked. If it is, the formula displays “Task Completed” in column C. If it is not, it displays “Task Not Completed”.

Combining Checkboxes with Google Apps Script

An example of using Google Apps Script with checkboxes to automate a common task is sending a notification email when a task is marked as completed using a checkbox.

Go to Extensions > Apps Script and paste the code below. Click on the floppy disk icon to save the Script.

function onEdit(e) {
  // Ensure the event object is defined
  if (!e) {
    return;
  }

  var range = e.range;
  var sheet = range.getSheet();

  // Check if the edited cell is in column B (column 2) and is checked
  if (Range.getColumn() == 2 && Range.getValue() == true) {
    var row = range.getRow();
    var task = sheet.getRange(row, 1).getValue(); // Get the task name from column A
    var email = sheet.getRange(row, 3).getValue(); // Get the email address from column C
    
    if (task && email) {
      sendEmailNotification(task, email);
    }
  }
}

function sendEmailNotification(task, email) {
  var subject = "Task Completed: " + task;
  var body = "The following task has been marked as completed:\n\n" + task;
  MailApp.sendEmail(email, subject, body);
}

Now, you need to set up a trigger to use the checkbox to trigger the email. Click on the clock icon to open the “Triggers” page.

Click “Add Trigger” and set the following:

• Choose which function to run: onEdit
• Choose which deployment should run: Head
• Select event source: From spreadsheet
• Select event type: On edit

Next, you need to go to the code editor to permit the Script to send emails from your Gmail Account. For this, click the Run button on the Script for the first time.

Select the account when a pop-up appears and click on “Allow” to give permission to the Script. Yes, that’s it.

When you check a checkbox in column B, the Script gets the task name from column A and the email address from column C. The Script then sends an email notification to the specified address, informing the recipient that the task has been completed.

Tip – You can apply strikethrough with conditional formatting to create a to-do list.