Have trouble tracking down what you need in the piles of data stored in Google sheets? You don’t want to scroll for minutes to find a customer name or a product name. Well, the functionalities that Google Sheets provide help pinpoint the needle in the haystack.
3 ways to search in Google Sheets
- Search sheets using the Find option
- Search using Find and Replace
- Search using Conditional Formatting
Method 1: Search sheets using the Find option
The Find option is perfect when you want to search a sheet containing phrases or words. All you have to do is enter the text you want in the find box and go through the cells that contain the phrase or word you entered.
Step 1: Open the find box.
Open a sheet that has the data you need. Press Ctrl+F (for Windows) or CMD+F (for Mac) on your keyboard to open a find box in the top right of your sheet.
Step 2: Enter the text string you want to search in the sheet.
Once you enter the string (phrase or a word) in the find box, the cells with your search string get highlighted.
You can go through the cells highlighted in green by clicking the up and down arrows beside the find box.
This feature comes in handy when you need to go through multiple cells that match the search text. However, for more control, while searching the sheet, you can use the Find and Replace function.
Method 2: Search using Find and Replace
The Find and Replace function in Sheets is a handy option to search cells and make changes instantly. The steps to use this function are pretty simple:
Step 1: Open the sheet in question.
Now in the menu bar, click the EDIT option to open a drop-down menu. Choose the Find and Replace option. You will get a Find and Replace dialog box with various search options.
You can also use shortcuts to open the Find and Replace dialog box. Press Ctrl+H (for Windows) or Cmd+H (for Mac).
Step 2: Enter your keyword.
In the input space against the Find option, enter your keyword or the phrase you want to search and click on Find.
This will select the first entry with your keyword.
Continue clicking the find button to move to the next cell containing your keyword.
Once you have reached the last instance of your keyword, a message saying No more results found, looping around will appear at the bottom of the Find and Replace dialog box.
You can close the dialog box after a search by clicking the Done button. It is the end of the story for searching the sheet.
Now, if you want to replace the keyword or phrase, the Replace option is more useful.
Step 3: Replace using Find and Replace.
To replace the word, enter the new word against the Replace label. Now, you can either press the Replace or Replace all button based on your needs. If you want to replace the words manually, select Replace. Select the Replace all button to replace all keyword instances at once.
With this function, you can search This Sheet, All Sheets or a Specific Range of cells. You can choose the option you want from the drop-down box next to the search label.
The best thing about the Find and Replace function is the various options apart from find and replace. These options give you precise control over your search mission.
Match case: This option allows you to conduct case-sensitive searches. For instance, the search ignores a cell that has paul starting with a lower case.
Match entire cell contents: Choosing this option allows you to search the cells with the same words as your keyword. This search option eliminates the cells with only a part of your keyword.
Search using regular expressions: The regular expression option helps in identifying a pattern in the sheets. If the contents of the cell express a pattern, the search considers it a match.
Also search within formulae: By checking this option, search the contents within formulae besides cell contents.
Method 3: Search using Conditional Formatting
The above functions allow you to select cells containing your keyword one by one. Instead, you can use the Conditional Formatting feature and highlight all the cells containing your keyword.
Step 1: Open Conditional Formatting function.
In the menu bar, click on the Format tab and select the Conditional Formatting option.
Now, you will see a sidebar on the right called the Conditional format rules.
Step 2: Highlight all the cells with your keyword.
Type in or select the range of cells you have in mind in the input box against Apply to range. Selecting the range opens a dialog box showing the range you selected. Click OK to close the dialog box. If you’re entering the range manually, you can skip the this step.
Now, click the drop arrow under Format cells if, which is under the Format Rules section. Choose the Text contains option from the drop-down list.
This will open a dialog box in which you should enter the keyword you want to search for in the range.
You can format your keyword in the cells using the Formatting style function. Press the Fill Color icon and select the color you want to highlight the cells with.
After selecting the color, press the Done button and close the conditional formatting bar. Now, the conditional formatting button will do its job to perfection.
The different find functions offered by Google Sheets help ease your projects. Learn more of such amazing tips on our website.