Productivity Spot

How to Search in Google Sheets and Highlight the Matching Data

Watch Video – Search in Google Sheets and Highlight the Matching Data

If you work with huge datasets in Google Sheets, sometimes, it may get difficult to identify some data points.

For example, if you have a list of students and their marks, it can take some time to find a student name and all his/her marks.

In this tutorial, I will show you how to search in Google Sheets and highlight the matching data (cells/rows).

Something as shown below:

Note that as soon as I enter any text in cell B2 and hit Enter, it searches for that data point in column B and highlight the rows where there is a match.

Now let’s see how to create this is Google Sheets.

Search in Google Sheets and Highlight the Matching Data

There are different ways you can use this technique:

In the below section, I will each of these cases in detail and show you how to create this in Google Sheets.

Search and Highlight Cells with the Matching Data

In this section, I will show you how to create something as shown below:

Note that as soon as I enter the country name in cell B1 and hit Enter, it highlights the matching cells in column B.

Here is how to make this in Google Sheets:

Now, whenever you enter any text in cell B1, it will highlight the matching cells in the dataset.

Note that there needs to be an exact match. If the text entered is misspelled, or is partial/incomplete, the cells would not be highlighted.

In the above example, I am entering the data manually. However, if you have a huge dataset, you can create a drop-down list in cell B1. This will allow the user to see all the options at one place and select the required one.

Search and Highlight Rows where there is a Match

In the above example, we highlighted only the matching cells.

But in real world example, you’re more likely to search for a data point and highlight the entire row.

Something as shown below:

Here are the steps you can use to search and highlight the entire row in Google Sheets:

Search and Highlight Rows with Partial Match

In the above examples, it was necessary that there be an exact match for the cells/rows to get highlighted.

But you may have a situation where you want to get match based on the partial search string.

Something as shown below:

Note that here as soon as I entered ‘abc’ in cell A1 (it doesn’t matter whether it’s lower case or upper case), all the rows in the dataset that have that string get highlighted.

Here are the steps to create this in Google Sheets:

In case you want the search to be case sensitive (which means that ‘abc’ and ‘ABC’ are different), then use FIND function instead of the SEARCH function in the above formula.

You may also like the following tutorials:

FREE Google Sheets Tips Ebook

Get Work Done Faster with these 10 Google Sheets Tips

X