How To Search in Google Sheets: 4 Easy Methods

To search in Google Sheets, use the Find and Replace option. There are several other ways, too.  I’ll show you how to search in Google Sheets with four easy methods.

Below, I’ll discuss how to use Find and Replace, additional search functions, conditional formatting, and a quick keyboard shortcut to search in Google Sheets. Each method quickly surfaces your target data, so you can find what you need. Some of these search methods also allow you to swap target data with something else.

How To Search in Google Sheets: The 4 Methods

The fastest way to search in Google Sheets is with a keyboard shortcut. On a windows operating system, you just press CTRL + F and type what you’re looking for. But Google Sheets has several more powerful ways to search. I’ll discuss them below.

I’ve recently covered the find and replace function in Google Sheets. That guide goes deeper into the subject, but the short version is this: You don’t need to be an expert to conduct a search. I regularly use these features every day, and many novice users do the same. In Google Sheets, there are three ways:

Method 1: Press Ctrl + F and type the text in the box that comes up
Method 2: Use one of the many Google Sheets search functions, e.g., MATCH, INDEX, FIND, and VLOOKUP
Method 3: Use conditional formatting to visually highlight search results
Method 4: Use Find and replace, like so:

1. Go to “Edit” > “Find and replace.”
2. Type in the word or words you want to search.
3. Set the search settings to narrow the search.
4. Click “Next” to see the next result if there are multiple results.

How to Search: Video

Here’s my video on how to search in Google Sheets. It goes through each of the methods I discuss in this guide.

Keyboard Shortcut for Google Sheets Search

The keyboard shortcut for a search in Google Sheets is CTRL + F. On a Mac, the shortcut is CMD + F. As with most programs, that shortcut brings up the “find” window and allows you to quickly search your data for specific text. This works for partial text and full text, partial numbers and full numbers.

I’ll cover what this looks like with screenshots below.

Using the Find Option to Highlight All Matching Cells

I’m a big fan of the “find” feature on all of Google’s properties. It’s quick, and I can use it to go exactly where I need to with just about any search. That applies to emails, documents, and (of course) spreadsheets. If you have cells with text and you want to find out the cells that contain specific text strings quickly, you can do that using the find option in Google Sheets.

Note: This is a part of the final replacement option we will cover in the next section. Use this option when you want to quickly search the entire worksheet and highlight the cells that have the matching string.

Suppose you have a data set as shown below, and you want to find out all the cells that have the name ‘Mark’ in it:

Below are the steps for how to search on Google Sheets using the find option:

1. Open the worksheet that has the data.
2. Use the keyboard shortcut Ctrl+ F (for Windows) and Cmd + F (for Mac). This will open a small Find box at the top right part of your sheet.
   

   

3. Enter the string that you want to search in the entire worksheet.
   

   

The above steps would highlight all the cells with the matching text string.

If you want to go through each cell individually, you can use the downward-pointing and upward-pointing arrows in the “Find” field.

You can also visually scan the result, as all the matching cells are highlighted in green. In my opinion, this is the best way to quickly search in Google Sheets. It’s super quick, and you can use it to find out if any cells match the text strings. You can also go through these one by one.

In case you need a little more control over how you can search for data in Google Sheets, you can use the full-blown find-and-replace dialogue box functionality.

Google Sheets Search Functions

You can also use formulas to search Google Sheets. There are a number of search functions in Google Sheets, including the SEARCH function and the FIND function. As with most formulas I discuss, I’ll break them down into their component parts below.

Syntax for SEARCH, FIND, and MATCH Functions

• SEARCH function – This searches a string within the text and returns the position of the string.
• FIND function – This does the same as the SEARCH function, but it is case-sensitive.

However, these Google Sheets functions are limited since they can only search within a cell. The best function to use instead would be the MATCH function, see the formula below:

MATCH(search_key, range, [search_type])

• The search key is the specific value you want to search. It can also be a cell reference to that value.
• The key range is the range of cells you want to perform the search in.
• The search type is an optional parameter that lets you define whether to return an exact match or a close match.

The MATCH function searches for the search key within a range and returns its position. If you want an exact match, you can add a 0 in the search type section.

In our example spreadsheet, we can use the search function to find Paula Lee in the table

1. Type the MATCH function.
2. Type the search key in quotation marks and add a comma.
3. Select the range we’re searching in and add a comma.
4. Add a 0 for an exact match.
5. Click “Enter.”

The MATCH function returns a 2, which is the position of the search key in the range. Since we added a 0 for an exact match, the formula returns the first instance of the words Paula Lee.

Need another way to search your spreadsheet? You may also want to try the LOOKUP function. As with the other functions I discussed, this works best when you want to build a search into the logic of your cell.

How to Search Using Find and Replace

Let us assume we have the following data in a single worksheet:

The dataset shown is a very simple one, and you can obviously find what you need by just looking at it. However, we’ve kept it simple so as to make the tutorial easy to understand.

Now let us say we want to find the cells containing the name “Paul.” Here are the steps you need to follow in order to find them:

1. Click on the “Edit” menu from the menu bar.
2. Select the “Find and replace“ option.
   

   

3. This will open the “Find and replace“ dialog box. Alternatively, you can open this box directly by pressing Ctrl+H (if you’re on a PC) or Cmd+H (if you’re on a Mac).
   

   

4. In the input box next to the label “Find,” type the word you want to search for. (In our example, we can type the word “Paul” since that’s what we’re looking for).
   

   

5. Click on “Find.”
   

   

6. This will select the first cell containing the word.
   

   

7. If there are more instances of the word in the sheet, clicking on Find each time selects the next cell containing the word.
   

   

8. When you reach the last occurrence of the word, you will see a message that says, “No more results found. Looping around” at the bottom of the dialog box.
   

   

9. If you press the “Find” button after you see this message, “Find and replace“ will loop back to the first occurrence of the word.
10. Once you are done searching (and/or replacing), press the “Done” button to close the Find and Replace dialog box.
    

    

Note: After Step 6, if you want to replace the word with something else, you can enter the new word next to the label “Replace with.” In that case, you will need to press the “Replace with” button (if you want to replace a word one at a time manually) or the “Replace all” button (if you want to replace all occurrences of the word at once).

Also note that you can use the Find and Replace dialog box to find what you need in the current sheet, all the sheets, or even a selected range of cells. This will jump to the cell with the results.

Just select the drop-down arrow list next to the label “Search,” and select your required option:

How to Modify Find and Replace for Search

If you take a look at the Find and Replace dialog box, you will find that there are many other options besides just the Find, Replace, and Search. This works well with conditional formatting, too.

Each of these other options can help you further fine-tune your search so that you can find exactly what you need.

Let’s take a look at some of these options:

1. Match Case: Selecting this option lets you make your search case-sensitive. So, if you have a cell that contains a “paul” (with a lowercase ‘p’), the search will ignore the cell.
2. Match Entire Cell Contents: Selecting this option lets you search for cells that exactly match your search word. For example, if this box is checked, and your search word is “Paul Rodriguez,” then the search only considers the cell containing exactly the full name as a match. However, if the search word is just “Paul,” then the search ignores the full name since it is not an exact match.
   

   

3. Search Using Regular Expressions: This option is used if you have a regular expression in your “Find” field. A regular expression is a sort of a string containing a particular pattern. If a cell’s content fits the pattern, it is considered a ‘match.’ Further explanation on Regular Expressions is beyond the scope of this tutorial, but if you want to learn more about it, you can click on the ‘Help’ link next to the option:
   

   

4. Also Search within Formulae: In general, the Find and Replace tool searches through cell contents and results of formulae. If you also want to see if your search word is contained within the actual formula of cells, then you need to check this option.

Search Using Conditional Formatting (to Find and Highlight Cells with Search String)

The above method lets you find cells containing a search string and select them individually. Instead of that, if you want to search and highlight all cells containing the search word, you can use the Google Sheets “Conditional Formatting” feature.

So, for the same dataset, if you want to look for all the cells containing the name “Paul,” follow these steps:

1. Click the “Format” menu from the menu bar.
2. Select the “Conditional Formatting“ option.
   

   

3. This will open the “Conditional format rules“ sidebar on the right of the window.
   

   

4. In the input box under “Apply to range,” type in the range of cells you want to search in or select the data range. In our example, we can type A2:B11.
5. Selecting the specific range of cells will open a dialog box displaying the specific range that you selected. Once you’re done, you can click on “OK.” If you prefer to type the range manually, then you can ignore this step.
   

   

6. Next, in the “Format Rules“ section, under “Format cells if,” click the drop-down arrow to add a search criteria.
7. From the drop-down list that appears, select the “Text contains” option.
   

   

8. You will see an input box below the drop-down list. Type in your search word here. If you’re looking for cells containing the word “Paul,” type in the word “Paul.”
   

   

9. Under “Formatting style,” click the “Fill Color“ button.
10. Select the color you want to use to highlight the matching cells/rows. We selected “yellow”.
    

    

11. Finally, click the “Done” button to let conditional formatting do its work.

You should see all the cells containing the word “Paul” in your selected range, highlighted in yellow.

Conditional Formatting Search Options

The Conditional format rules feature provides a number of options for you to find what you need.

If you click on the drop-down arrow under “Format cells if” (in the Format Rules section), you will find some of the following options:

• Text contains: To find cells that contain the search word as part of its contents.
• Text does not contain: To find cells that don’t contain the search word.
• Text starts with: To find cells that start with the search word.
• Text ends with: To find cells that end with the search word.
• Text is exactly: To find cells that contain exactly and only the search word.

There are several other options for search. This includes an option to provide your own custom formula. You can use these to fine-tune your search and highlight what you need. In this way, you can quickly browse through the sheet to find your required cells and make the required changes.

In this tutorial, I showed you two techniques in which you can search in Google Sheets. Both allowed you to find particular cells with matching data. The first technique uses the Find and Replace tool, which is the most commonly used Google Sheets search feature. The second technique uses the Conditional formatting feature to highlight matching cells in a given range.

Although this technique is not very commonly used for searches, you can use it to highlight vital cells in your sheets and perform analytical studies on your data.

Frequently Asked Questions

So there are four easy ways to search in Google Sheets. Here are a few questions I regularly hear on the subject.

What’s the best way to quickly find data in a spreadsheet?

Need to quickly find data? The Find and Replace function is usually your best bet. That said, other methods provide immense value as well. Conditional formatting, for example, may give you a better way to quickly find the same data or to visually highlight found data for your team.

How do you highlight search results in Google Sheets?

If you want to highlight search results, you may want to try using the conditional formatting method. I described this in more detail above, with screenshots. Note that I also covered how to highlight duplicates in a spreadsheet.

What’s the keyboard shortcut to search in Google Sheets?

On a Windows computer, the shortcut for search is CTRL + F. On a Mac, you’ll use CMD + F. In either case, the shortcut brings up the “find” window. Just type your search query there and you’ll see a quick result.

Wrapping Up

Now you know how to search in Google Sheets using Find and Replace and a few other simple methods, searching your spreadsheets should be a breeze.

Related:

• How To Turn on Google Sheets Dark Mode
• Google Sheets vs. Microsoft Excel

References

1. Google Support. Use add-ons & Apps Script [Internet]. Google; [cited 2024 Jan 9]. Available from: https://support.google.com/docs/answer/3094126
2. Microsoft Support. Find and replace text [Internet]. Microsoft; [cited 2024 Jan 9]. Available from: https://support.microsoft.com/en-au/office/find-and-replace-text-c6728c16-469e-43cd-afe4-7708c6c779b7
3. Spreadsheetpoint. How to Remove Duplicates in Google Sheets [Internet]. Spreadsheetpoint; [cited 2024 Jan 9]. Available from: https://spreadsheetpoint.com/remove-duplicates-in-google-sheets/