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 find out the cells that contain a specific word or text string, you can do that using the ‘Find’ option in Google Sheets.
Open your spreadsheet and press the shortcut key combination ‘Ctrl + F’. The shortcuts are only available on desktop versions of the google sheet.
This will open a small ‘Find’ box at the top right corner of your sheet. 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.
If you want to go through each of the cells one by one, you can use the downward and upward arrows next to the Find field.
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 gives you a number of options to filter your search and replace found instances of your search string.
Let us assume 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 appear. Alternatively, you can open this dialog box directly by pressing ‘CTRL+H’ (if you’re on a PC) or ‘Cmd+H’ (if you’re on a 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 even on a selected 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
On the Find and Replace dialog box, you will find four options below the ‘Search’ label. Each of these options can help you further narrow down your search so that you can find exactly what you need.
- Match case – Select this option if your search is case-sensitive. For instance, if you have a cell that has the value ‘xerox 1891’ (with lowercase x), selecting this option will ignore the cell.
- Match entire cell contents – Select this option if only want to search for the cells that exactly match your search word. For instance, if this option is selected, and your search word is ‘Xerox 1891’, then the search only considers 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 results of formulas. If your search word is contained within the actual formula of cells, then you need to check this option.
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 instances of the word across all sheets, clicking on the ‘Find’ button each time selects the next cell containing the word.
When you reach the last instance of the word, you will get a message that says “No more results found, looping around” at the bottom of the dialog box. If you click the ‘Find’ button after you get this message, Find and Replace will loop back to the first occurrence of the word.
Once you’re finished searching, click the green ‘Done’ button to close the Find and Replace 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’. Then press the ‘Replace’ button (if you want to replace the word one at a time) or the ‘Replace all’ button (if you want to replace all occurrences of the word at once).
Now, you know how to search in all sheets in Google Sheets.