How to Search in Google Sheets (3 Easy Ways)

With Google Spreadsheets, you can store, maintain, and process medium and even large amounts of data. However, a larger volume of data means greater difficulty in finding what you need.

Imagine having to sift through cell after cell of data to find, say the surname of a particular customer! It would be like finding a needle in a haystack!

Fortunately, Google Sheets provides a Find and Replace feature that lets you search through entire workbooks.

In this tutorial, we will demonstrate, with an example, how to search your Google Sheets using its Find and Replace feature.

We will also explain what Find and Replace options are available to help you further fine-tune your search, so you get exactly what you’re looking for.

In the end, we will show you how to use Conditional Formatting in Google Sheets to find and highlight all matching cells in a range so that you can quickly identify and work with them.

Search Using the Find Option to Highlight All Matching Cells in the Sheet

If you have cells with text and you want to quickly find out the cells that contain specific text string, you can do that using the find option in Google Sheets.

Note that this is a part of the final replacement option that we are going to cover in the next section. use this option when you want to quickly search the entire worksheet hey 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 to search in Google Sheets using the find option:

  1. Open the worksheet that has the data
  2. Use the keyboard shortcut Control + F (for Windows) and Cmd + F (for Mac). This will open a small Find box at the top right part of your sheet.Find Search box in the Google Sheets
  3. Enter the string that you want to search in the entire worksheetEnter the search string in the find box

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

Highlighted Cells that have the search string

If you want to go through each of the cells one by one, you can use the downward-pointing and upward-pointing arrow in the Find field.

Upward and Downward pointing arrows

You can also visually scan the result as all the matching cells are highlighted in the green color.

This is a really quick method morning and you can use to find out if there are any cells that match the text string and go through these ones by one.

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

spreadsheet fundamentals

Search Using Find and Replace

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

Dataset in the worksheet in which you want to search

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.Click on Edit and Find and Replace
  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).Find and replace dialog box
  4. In the input box next to the label “Find”, type the word that you want to search for (In our example, we can type the word “Paul”, since that’s what we’re looking for).Enter the String you want to search
  5. Click on Find.Click on Find
  6. This will select the first cell containing the word.First cell with the matching string is selected
  7. If there are more instances of the word in the sheet, clicking on Find each time selects the next cell containing the word.Selecting the second cell with the matching string
  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.No more results message in Find and Replace
  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.Done button

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 button (if you want to manually replace the word one at a time) or Replace all button (if you want to replace all occurrences of the word at once).

Replace options in case you want to search and replace

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.

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

Search option in the range, sheet or the workbook

Find and Replace Options

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.

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:

Find and replace 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.Match the entire cell content
  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 as 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:Help on how to search in Google Sheets
  4. Also Search within Formulae: In general, Find and Replace just 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 & Highlight Cells with Search String)

The above method lets you find cells containing a search string and selects them one by one.

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.Click on Conditional Formatting
  3. This will open the ‘Conditional format rules’ sidebar on the right of the window.Conditional formatting rules
  4. In the input box under “Apply to range”, type in the range of cells you want to search in, or simply select the range of cells. In our example, we can type A2:B11.
  5. Selecting the range of cells will open a dialog box displaying the range that you selected. Once you’re done, you can click on OK. If you prefer to manually type the range, then you can ignore this step.Select the data range
  6. Next, in the Format Rules section, under “Format cells if”, click on the dropdown arrow.
  7. From the dropdown list that appears, select the “Text contains” option.Click on Text contains
  8. You will see an input box below the dropdown list. Type in your search word here. If you’re looking for cells containing the word “Paul”, type in the word “PaulEnter Paul as the text to search for
  9. Under “Formatting style”, click on the Fill Color button ( ).
  10. Select the color you want to use, in order to highlight matching cells/rows. We selected “yellow”.Select the color in which to format the cell
  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.

Data highlighted in yellow

Other Search Options in Conditional Formatting

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

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

Text does not contain option

  • 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 a number of other such options, including an option to provide your own custom formula. You can take advantage of these options 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, we saw two techniques in which you can search in Google Sheets and find particular cells with the matching data.

The first technique uses the Find and Replace feature, which is the most commonly used search feature of Google Sheets.

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.

We hope this was helpful.
spreadsheet fundamentals

Other Google Sheets tutorials you may like:

Most Popular Posts

Sumit

Sumit

Sumit is a Google Sheets and Microsoft Excel Expert. He provides spreadsheet training to corporates and has been awarded the prestigious Excel MVP award by Microsoft for his contributions in sharing his Excel knowledge and helping people.

2 thoughts on “How to Search in Google Sheets (3 Easy Ways)”

  1. What I am trying to do is find a particular sheet in google sheets based on its contents.

    There’s a spreadsheet where I stored the different values of app stores (Amazon, Googleplay, Apple app store, Samsung, Huawei etc) and calculations of profit based on selling different volumes and types of apps in those stores.

    I can’t remember what it’s called.

    I simply want to be able to search the google sheets on my google drive and find the one that has, for example, the words “Apple”, “Huawei” and “Android” inside it.

    I can’t seem to be able to do this. It seems odd to me that a search engine giant should be so consistently poor at search and sorting within its own online apps (gmail is also rubbish at this). I can’t even find a way to list the titles in reverse alphabetic order.

    If anyone knows how to do this (The search – not the reverse order thing although that would also be useful) please let me know.

    Reply

Leave a Comment