How to Highlight Cells Containing Matching 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.

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.

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!