September 15, 2020

How to Highlight Cells Containing Matching Search Query in Google Sheets

Chart with arrows pointing up and down
SECTIONS
  1. Highlight Individual Cells Containing Search Query
  2. Highlight Rows Containing Search Query

The keyboard shortcut to search for items in Google Sheet is the same as in other software: Ctrl+F. But that’s not what you need, right? You can improve upon it by using conditional formatting!

Highlight Individual Cells Containing Search Query

We need to make sure there is space for us to search for items. For our sample spreadsheet, we moved the entire spreadsheet three cells down, so we can insert the improvised “search bar” on the top. 

Space above the spreadsheet


We then mark certain cells for our search bar:

Crude search bar inserted above the spreadsheet


Here are the steps:

Step 1: Select all cells that you want to be included in the search.

Step 2: Click Format, then Conditional formatting.

Step 3: A Conditional formatting tab will appear on the right side. Click on the box below Format cells if…

Conditional format rules, Format cells if highlighted


Step 4: A drop-down box will appear. Scroll to the end and select Custom formula is.

Drop-down box, Custom formula is highlighted


Step 5: A space will appear where the formula can be added. Add the following: =ISNUMBER(FIND($B$2,A5)), where you replace $B$2 with the address of the cell where you type the search and A5 is the first cell in the area of the spreadsheet where you want to search.

This will allow you to highlight cells even with partial search:

Cells containing search item highlighted


Highlight Rows Containing Search Query

If you want to highlight an entire row containing search query, you have to do some adjustments to the above procedure:

Step 1: Select the entire spreadsheet containing data

Step 2: Click Format, then Conditional formatting.

Step 3: A Conditional formatting tab will appear on the right side. Click on the box below Format cells if…

Step 4: A drop-down box will appear. Scroll to the end and select Custom formula is.

Step 5: Use the following formula instead: =OR(ISNUMBER(FIND($B$2,$A5))), replacing $B$2 with the address of the cell where you type the search and replacing $A5 and $B5 with the first cell in the area of the spreadsheet where you want to search.

Row highlighted with search item


These are simple tricks to use to make it easier to analyze data. More sophisticated search methods involve other functions available in Google Sheets. Stay tuned!

Suscribe to get more data and analytics tips!

Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.