How to Highlight Multiple Groups and Control Checkboxes in Google Sheets

Highlighting multiple groups using checkboxes in your Google Sheets document makes it easier to read separate groups in your spreadsheet. This is especially useful when you are making a checklist and want to section each checklist off, making it easier for you to keep track.

Google Sheets has a built-in Data Validation and Conditional Formatting features that make data visualization easier. It helps spreadsheets become organized and easily understood – whether the data is numbers or words.

For example, you are shopping for a big party and you are keeping track of each category of goods you need for the occasion. The list could get so long and you can lose yourself in your list. If only there was a way to keep track of everything!

So, how should we go about it?

To accomplish our goal, we have some options we could consider. We can use a combination of ARRAYFORMULA, MATCH and LOOKUP, and even REGEXMATCH functions of Google Sheets.

 

How to Create Checkboxes in Google Sheets

Before we start highlighting any rows, you should know where to find the checkbox option in Google Sheets.

  1. On the menu bar, pick Data, and from the dropdown menu, choose “Data Validation”.

How to Highlight Multiple Groups and Control Tick Boxes in Google Sheets

 

  1. In the criteria dropdown menu, go all the way down and pick “Checkbox”.

How to Highlight Multiple Groups and Control Tick Boxes in Google Sheets

 

  1. That should do it, go ahead and click “Save”.

How to Highlight Multiple Groups and Control Tick Boxes in Google Sheets

 

  1. There should be a checkbox on the first section of the checklist. Note that when you look at the value of the checkbox, it’s false if unchecked, and true if checked.

How to Highlight Multiple Groups and Control Tick Boxes in Google Sheets

 

  1. Do this for the rest of the list and sections.

How to Highlight Multiple Groups and Control Tick Boxes in Google Sheets

 

How to Highlight Multiple Groups and Control Checkboxes with a Helper Column in Google Sheets

Here is the first option you can choose: put in a “Helper” column.

  1. Insert the following formula where you want to start the helper column. Make sure to change the reference arrays as needed.
=ARRAYFORMULA(IF(ROW($B$4:$B<=MATCH(2,1/($C:$C<>""),1),LOOKUP(ROW($B$4:$B),ROW($B$4:$B)/if($B$4:$B<>"",TRUE,FALSE),$B$4:$B),))

How to Highlight Multiple Groups and Control Tick Boxes in Google Sheets

 

  1. The ARRAYFORMULA will immediately fill out the rest of the included items.

How to Highlight Multiple Groups and Control Tick Boxes in Google Sheets

 

  1. After that, choose Format > Conditional Formatting.

Conditional Formatting to Highlight Multiple Cells

 

  1. Choose the array of your list.

How to Highlight Multiple Groups and Control Tick Boxes in Google Sheets

 

  1. Choose Custom Formula from the dropdown menu.

Entering Custom Formula into Spreadsheet

 

  1. Insert the following formula, and click done!
    1.  
=E4=TRUE

How to Highlight Multiple Groups and Control Tick Boxes in Google Sheets

 

  1. Test your highlights now – you will notice checking the box highlights the group that you want!
    How to Highlight Multiple Groups and Control Tick Boxes in Google Sheets

 

How to Highlight Multiple Groups and Control Checkboxes with the REGEXMATCH Function in Google Sheets

Want to do this without putting in a helper function? There is another option for you, by turning the Helper column into a REGEXMATCH Function in the Conditional Formatting formula.

  1. Choose Format > Conditional Formatting.

Conditional Formatting

 

  1. Choose the array of your list, and choose Custom Formula from the dropdown menu.

How to Highlight Multiple Groups and Control Tick Boxes in Google Sheets

  1. Insert the following formula and click done!
    
    =REGEXMATCH(ROW(B4)&"","^"&textjoin("$|^",true,ArrayFormula(IF(ArrayFormula(IF(ROW($B$4:$B)<=MATCH(2,1/($C:$C<>""),1),LOOKUP(ROW($B$4:$B),ROW($B$4:$B)/IF($B$4:$B<>"",TRUE,FALSE),$B$4:$B),))=TRUE,ROW($B$4:$B),)))&"$")

    How to Highlight Multiple Groups and Control Tick Boxes in Google Sheets

 

  1. Test your highlights now – it works just like the previous function!

How to Highlight Multiple Groups and Control Tick Boxes in Google Sheets

 

There you have it! You are now able to highlight multiple groups and control checkboxes in Google Sheets as you wish, without disturbing the integrity of your data.

Now that you have a grasp on how to combine data visualization styles in your spreadsheets, you can combine this with other Google Sheets formulas to make really powerful data documents!

Use the link below to use our spreadsheet sample:

Get emails from us about Google Sheets.

Our goal this year is to create lots of rich, bite-sized tutorials for Google Sheets users like you. If you liked this one, you'll love what we are working on! Readers receive ✨ early access ✨ to new content. There will be no spam and you can unsubscribe at any time.

0 Shares:
1 comment
  1. Very helpful entry. I have a question. I have my checkboxes on A2:A5 and their corresponding data on B2:B5. Is there a way for me to display on C2 the data on B2:B5 ONLY IF they’re checked on A2:A5?

Leave a Reply

Your email address will not be published. Required fields are marked *

You May Also Like