Use Conditional Formatting With Checkbox in Excel & Google Sheets

This tutorial demonstrates how to use conditional formatting with a checkbox control in Excel and Google Sheets.

 

conditional formatting w checkbox final data

 

Conditional Formatting With Checkbox

In Excel, you can use a checkbox to control whether or not conditional formatting should be applied. For the following example, you have the data below in Column B and a checkbox linked to cell C2. (Here, we’re starting with an existing checkbox. If there isn’t already one in your worksheet, you’ll need to insert a linked checkbox before you start.)

 

conditional formatting with checkbox

 

Say you want to create conditional formatting rules for the range (B2: B9) that adds a fill color to the cells when the checkbox is checked. Since the checkbox is linked to cell C2, this cell will have the value TRUE if the checkbox is checked, and FALSE if it’s unchecked. You’ll use the value of cell C2 as the determinant for the conditional formatting rule.

  1. Select the data range and in the Ribbon, go to Home > Conditional Formatting > New Rule.

 

conditional formatting with checkbox 1

 

  1. In the Rule Type menu, (1) select Use a formula to determine which cells to format. This gives you a formula box under Edit the Rule Description. (2) In the box, enter:
=$C$2 = TRUE

Then (3) click Format.

 

conditional formatting with checkbox 2

 

  1. In the Format Cells window, (1) select a color (e.g., light blue) and (2) click OK.

 

conditional formatting with checkbox 3

 

  1. That takes you back to the Edit Rules window. Click OK.

 

conditional formatting with checkbox 4

 

  1. Click Apply, then OK to confirm the new rule.

 

conditional formatting with checkbox 5

 

Now, when you check the checkbox, the value of cell C2 becomes TRUE, and all cells in the range (B2:B9) get the light blue background color.

 

conditional formatting with checkbox final data

 

Conditional Formatting With Checkbox in Google Sheets

The process is similar in Google Sheets.

  1. Select the data range and in the Menu, go to Format > Conditional formatting.

 

google sheets conditional formatting with checkbox

 

  1. In the Conditional format rule window on the right, (1) select Custom formula is and (2) enter the formula:
=$C$2 = TRUE

For the Formatting style, (3) select Fill color, (4) choose the background color (i.e., light blue), and (5) click Done.

 

google sheets conditional formatting with checkbox 1a

 

The result is the same as in the previous example. When the checkbox is checked, cells in B2:B9 are filled with a light blue background.

 

google sheets conditional formatting with checkbox 2