Google sheets is a great alternative spreadsheet application to the heavyweight Microsoft Excel. It is a free, cloud-based application whereas Excel is a desktop program. It’s real-time collaborative, so everyone can see and work with the most up-to-date version of the same spreadsheet.

There are instances where you might want to locate specific information in a large spreadsheet file that contains multiple sheets and thousands of lines of data on each sheet. If you attempt to do this manually, it can be very difficult. Luckily there is a Find and Replace tool in the Google Sheets application that you can use to search for specific data across all tabs (or sheets) in the entire workbook.

In this post, we’re going to demonstrate to you how to use Google Sheets to search for specific words and phrases.

Quickly Search in Google Sheets with the Find Box

Let’s say you want to quickly lookup a specific word or text string (like name, date, product name, etc.) in Google Sheets, you can easily do that with the ‘Find’ option.

Open your spreadsheet and press the shortcut key combination ‘Ctrl + F’. The shortcuts are only available on desktop versions of the google sheet.

Then a small ‘Find’ box will appear at the top right corner of your sheet ad shown below. Type in the word/phrase in the ‘Find in sheet’ box.

In the example below, we want to find a product named ‘Xerox 1891’ in all sheets of the spreadsheet. So, once we start typing the word, it will highlight all the partially matching entries in light green color as shown below.

When you finish typing, it will highlight the fully matched text strings with a light green fill inside and a black border outside the matching cell(s) as shown below.

You can use the upward and downward arrows next to the Find field to go through each highlighted cells one by one.

If you want to search for the same text string in other sheets of the same workbook, you can just switch between the sheet, it will continue to highlight all the matching text strings in all sheets.

Search All Sheets Using Find and Replace Tool in Google Sheet

The Find and Replace tool provides you with a number of options to filter your search and find your text string across all sheets in a workbook.

For example, let’s say we have the following data in multiple sheets and we want to find the word ‘Xerox 1891’.

First, open the Google sheet file that contains the word/phrase that you’re looking for. Then, click on the ‘Edit’ menu from the menu bar and select the ‘Find and replace’ option from the drop-down.

A Find and Replace dialog box will open up. You can also open this dialog box by pressing CTRL + H (if you’re using Windows) or Cmd + H (if you’re using Mac).

In the Find and Replace dialog box, enter the word (Xerox 1891) that you want to search for in the input box next to the label ‘Find’.

You can also choose where you want to look for the word/phrase – in the current sheet, in all the sheets, or in a specific range of cells. To do that, click on the drop-down list next to the ‘Search’ label, and select ‘All sheets’.

Find and Replace Options

In the Find and Replace dialog box, you will see four options below the ‘Search’ label to filter your search. You can use these options to further narrow down your search.

  • Match case – Select this option if your search is case-sensitive. For instance, if you select this option to search the text ‘Xerox 1891’, it will ignore all the cells with ‘xerox 1891’ (with lowercase x).
  • Match entire cell contents – When you check this box, the tool only finds the cell contents that exactly matches your search words. For instance, if your search text is ‘Xerox 1891’, then the tool only finds the cell containing the exact word as a match. 
  • Search using regular expressions – If you check this box, it will only match the cell contents that fit the pattern.
  • Also search within formulae – This option is used to search through cell contents and formula results. Select this option, if you want to search both value cells and formula cells for the search word.

You can also perform a simple search without any of these above options. Once, you chose where you want to look for the word/phrase, click the ‘Find’ button. Doing this will select the first cell containing the matching word.

If there are more occurrence of the search word in all sheets, clicking on the ‘Find’ button each time selects the next cell containing the word.

When the last instance of the search word is reached, Excel will show a message that says “No more results found, looping around” below the filter options as shown in the below screenshot. If you click the ‘Find’ again after you get the message, the tool will take you back to the first instance of the search word.

Once you’re finished searching, click the green ‘Done’ button to close the dialog box.

As the name implies, you can not only search for a certain word using the Find and Replace tool, you can also replace the word with something else. If you want to do that, enter the new word in the input box next to ‘Replace With’. If you wish to replace the word one at a time, then click the ‘Replace’ or if you want to replace all instances of the word together, click the ‘Replace all’ button.

Now, you know how to search in all sheets in Google Sheets.