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.
A Sample of Practice Spreadsheet
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.
- How to Find Median in Google Sheets (2 Easy Ways)
- Conditional Formatting Based on Another Cell in Google Sheets
- How to Find Edit History in Google Sheets (4 Simple Ways)
- Using Conditional Formatting With Custom Formula in Google Sheets
- How to Find Trash in Google Sheets (with Quick Steps)
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.
Read More: Find and Replace in Google Sheets (3 Ways)
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.
- 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:
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.
- How to Find and Delete in Google Sheets (An Easy Guide)
- Google Sheets: Highlight Row If Cell Is Empty
- How to Highlight Duplicates for Multiple Columns in Google Sheets
- Highlight Duplicates in Two Columns in Google Sheets (2 Ways)
- How to Copy Conditional Formatting in Google Sheets
- Pivot Table Formatting in Google Sheets (3 Easy Ways)
- How to Find Correlation Coefficient in Google Sheets
- Replace Space with Dash in Google Sheets (2 Ways)