How to Search in Google Spreadsheet (5 Easy Ways)

Google Sheets allows us to perform searches for data and values in many different ways. So today, in this article we look at how to search in Google spreadsheet for various scenarios, with both technique and formulas.

Let’s get started.

5 Ways to Search in Google Sheets

To present our processes today, we will be using the following dataset. It should cover the many different scenarios that people face in their day-to-day tasks.

base dataset - how to search in google spreadsheet

1. Search with the Find Option (Keyboard Shortcut) in Google Sheets

The first method we are going to discuss is the simplest and most basic, the Find option.

It is easily accessible with a keyboard shortcut:

  • CTRL+F (Windows)
  • CMD+F (Mac)

Which opens the Search Bar.

opening the search bar with keyboard shortcut

Inputting our search text, “shirt”, will highlight all the cells that contain this particular set of characters within the worksheet, regardless of case.

how to search in google spreadsheet using Find option

While this method does allow you to search for your desired text or number, search queries in a spreadsheet are not always that simple. The following methods will discuss varying scenarios that will utilize different types of search in a Google spreadsheet.

2. Search with the Find and Replace Option

The next level to the Find option is the Find and Replace option of Google Sheets. This function not only searches for the given text but also goes the extra mile to find conditional queries and replace them if needed in one go.

You can navigate to Find and replace from the Edit tab on the top of the Toolbar.

Edit > Find and replace

navigating to Find and replace in Google Sheets

Or you can use the keyboard shortcut, CTRL+H, to open the Find and replace window.

the Find and replace window

To show the capabilities of this function, let’s find and replace all “shirts” that start with small “s” and replace them with “Shirts”.

Step 1: Open the Find and replace window.

Step 2: Search for “shirts”. Click Find. (this is optional)

using Find to go through all the occurrences

Keep clicking Find to see the different instances of the search query. Once all the instances have been found, the window will notify you with a message: No more results found, looping around.

message noting that the find cycle is complete - how to search in google spreadsheet

Step 3: Apply the following conditions for our task:

  • Find: shirts
  • Replace: Shirts
  • Search: This Sheet
  • Check the Match case option

setting conditions for the Find and replace window

Step 4: Click Replace all.

how to search and replace in google spreadsheet using Find and replace

Rows 6, 10, and 12 have been updated.

What we have shown is only a small percentage of the capabilities of the Find and replace option, but this is the extent of it being utilized for basic search in a Google spreadsheet.

You can also search for formulas and specific string sequences with regular expressions with this option. Please see our Find and Replace in Google Sheets (3 Ways) article for an in-depth guide.

more types of search options available in Find and replace

Find and Replace from a Mobile Device

We can also access Find and Replace from the mobile version of Google Sheets.

Tap the vertical 3-dots icon on the top-right corner of the screen to open the options menu.

options menu from Google Sheets mobile

Tap on Find and replace.

tap on the Find and replace option

The Find and Replace option of the mobile device is limited to basic functions only.

Find and replace functionality is limited in a mobile device

3. Search and Highlight using Conditional Formatting in Google Sheets

Conditional formatting is a fairly advanced way to search in a Google spreadsheet.

It combines the base search feature of the Find option, with the complex search options of Find and Replace, the ability to take custom formulas for searches, and finally, permanently highlight the searched values.

You can access the conditional formatting menu from the Format tab in the Toolbar.

Format > Conditional formatting

navigating to conditional formatting in google sheets

In the Conditional formatting rules menu, we have to apply certain conditions.

Step 1: Set the range. This is the range of cells that is to be counted and affected.

Step 2: Set the Format rules. We have chosen the Text contains option.

selecting the Text contains option for conditional formatting

As you can see from the drop-down, there are a plethora of options to select according to your needs. Here are just the Text search options:

  1. Text contains: Highlight cells that contain given text.
  2. Text does not contain: Highlight cells that do not contain the given text.
  3. Text starts with: Highlight cells that start with the given text as value.
  4. Text ends with: Highlight cells the end with the given text as value.
  5. Text is exactly: Highlight cells that only contain the given text.

Step 3: Add the search keyword. We are looking for all the occurrences of “Shirts”

how to search and highlight in google spreadsheet using conditional formatting

The main selling point of Conditional formatting is its ability to highlight cells according to complex conditions or criteria. This essence is captured by the Custom formula is option.

Here, we have highlighted rows that contain the word “Shirts” in the Product column.

The custom formula:


conditional formatting on the entire row

Our Conditional Formatting Based on Another Cell in Google Sheets article covers all methods in-depth on these custom formulas for conditional formatting.

4. Search using the QUERY function in Google Sheets

QUERY is a very powerful function of Google Sheets, especially when it comes to searching and extracting cells, rows, or columns that contain specific data.

The condition for our search query is to extract all rows of the table that have a Sales value greater than, let’s say, $1000.

Let’s through the query search process step-by-step:

Step 1: In the designated cell, for us, it’s cell B16, apply the QUERY function, =QUERY(.

Step 2: Select the range. We have selected the whole table including headers. Our range is B2:E12.

inputting range in the QUERY function

Step 3: We will now apply the query, which is “SELECT * WHERE E > 1000” (make sure that the query is enclosed in quotation marks). Then input 1 after comma (,) as we have included the table headers in our selection.

inputting the search query in the QUERY function

Step 4: Close parentheses and press ENTER.

=QUERY(B2:E12,"SELECT * WHERE E>1000",1)

how to search in google spreadsheet with QUERY function

We can make the process slightly more dynamic by using a cell reference for our condition. You may have noticed our condition in cell C14.

To add this cell reference condition we have to concatenate the reference in-between the query with the ampersand (&) operator.

Our updated formula:

=QUERY(B2:E12,"SELECT * WHERE E>"&C14&"",1)

using QUERY function with cell reference

The QUERY formula in action:

how to search in google spreadsheet using QUERY function animated

5. Search for String within a Text in a Cell

For this method, we will be using the SEARCH function. SEARCH is a niche function used to find the location of a text within a cell.

Following our examples before, let’s use the SEARCH function to find the string “Shirt”. Our formula:


how to search within cell in google spreadsheet using SEARCH function

As we can see, the first value we get is 8. This is because the string “Shirt” starts from the 8th character, the whitespace is counted.

We also see a #VALUE error where the function failed to find any instance of the searched word.

We can change our conditions to return as “FOUND” or “NOT FOUND” if the searched word exists in the cell.

Our new formula, which also takes the #VALUE error into account:


Use the fill handle to apply to the rest of the column.

adding Boolean condition with IF function

Final Words

We hope that all the methods we have discussed of how to search in a Google spreadsheet come in handy in your daily tasks.

Feel free to leave any queries or advice you might have for us in the comments section.

Mehrab Imtiaz

Mehrab Imtiaz

Mehrab Imtiaz is a Technical Writer for specializing in everything Google Sheets. Mehrab has always had a profound passion for writing and with his experience with MS Excel and Google Sheets throughout his career, it seems to be a great match when it comes to dishing out tips and tutorials for all to enjoy.

We will be happy to hear your thoughts

Leave a reply