How to Search in All Sheets in Google Sheets (An Easy Guide)

Most of the time while working with Google Sheets, you may have data stored in different sheets of your workbook. The data may be scattered and you may want to search for specific values or data from various sheets. If you want to locate the data manually, it can become very tiring and time-consuming. In such instances, Google Sheets provides an easy solution to this problem. In this article, we will show you how to search in all sheets in Google Sheets.

Table of Contents

A Sample of Practice Spreadsheet

Search All Sheets

Step by Step Process to Search in All Sheets in Google Sheets

While the Find dialogue box is easy and convenient to use, it cannot search for data from multiple sheets in a workbook. In such cases, the Find and replace tool of Google Sheets comes in handy. We can search within multiple sheets using the Find and replace tool.

Step 1: Insert Find and Replace Window

First, press CTRL+H or go to Menu bar > Edit > Find and replace.

You will see that a window appears that looks like this:

Step 2: Search for Keyword

Now, type in the text or value you want to search for in the Find. In our example, we type in Stuart.
Leave the Replace with box empty.

Step 3: Setting the Search Conditions

Then, in the Search box drop-down menu, select All sheets to search in all the sheets of your workbook.

You can use the Search box drop-down menu to look for data in the present sheet or within a specific range too.

Step 4: Use the Find Button to See Results

Press Find and it will highlight the first cell that matches the findings with a blue border. You can continuously press Find to look through all the other findings.

After the last match appears, then it will show a message: No more results found, looping around.

If you press Find one more time after this message appears, then it will show the first match again.

Step 5: Ending the Search

Finally, press Done if you are satisfied with what you were looking for to close the Find and replace tool.

Note: You can replace the words that you find in the Replace with box. Just type in what you want to replace the findings with and it will replace the result. It can even replace all the findings or any specific finding.

Similar Readings

Other Features of the Find and Replace Tool

The Find and replace tool offers some great options with which you can modify your search result according to your need.

Match case: Use Match case when you want the findings to be case-sensitive. For example, if our table had a name with stuart (with a lowercase ‘s’) then the search will ignore it.

Match entire cell content: Select Match entire cell content to search for cells that match your word exactly. For example, if we typed only stuart and selected this option, then Find and replace will not find any cell because no cell contains stuart.

Search using regular expressions: If your table has a regular expression in the Find box, then select Search Using Regular Expressions A regular expression is a string having a particular pattern. If a cell data matches the pattern, then it considers it as a match and shows it.
Also search within formulas: Generally, Find and replace searches for matches in cells and the final result of any formula. If you want to search within the formula itself, then select Also Search Within Formulas.
Also search within links: Generally, Find and replace does not search in any links. If you select Also Search Within Formulas then, it will also search within a link.

Extra: 2 Ways to Search in a Worksheet in Google Sheets

1. Using the Find Dialogue Box

The Find dialogue box is a pretty straightforward and easy way to search for content within a specific sheet although it cannot search for data from multiple sheets in a workbook. It can search within the present sheet.

First, press CTRL+F to summon the Find dialogue box. You will see it appear at the top right-hand corner of your sheet.

Type the word you want to find in the find box. We type Stuart for our example.

Note: The results are not case-sensitive and will show you all the matches.

If it can find the text or value within your sheet, then it will highlight the cell with a light green color.

If there are multiple matches, it will highlight all your results. You can navigate through them using the Up Arrow or the Down Arrow in the Find dialogue box.

Finally, close the box by pressing X in the Find dialogue box.

2. Applying Conditional Formatting

Conditional formatting can be used to look for and highlight any specific cells within a range in a single sheet.

First, select the range where you want to look for values and open the conditional formatting side panel from Format > Conditional formatting.

Then, from the Format cells if drop-down menu, select Text contains.

Then in the Value or formula box type in the value or text that you are looking for. For our example, we type Stuart.

You can select the formatting style you like. We kept the default one for our example.
Finally, press Done and the cells that match your word are highlighted.

The search results are now highlighted:

Conclusion

In this article, we showed you how to search in all sheets in Google Sheets in 3 different ways. Keep practicing the methods that we have shown here for a better understanding of the concept. We hope this article was useful to you to help you.

Also, check out other articles on OfficeWheel to keep on improving your Google Sheets work knowledge.