How to Search in Google Sheets on Any Device
Sheets is an online Google app that, in many instances, has successfully replaced MS Excel. The app itself can even open Excel files and, alternately, allows users to download spreadsheets and open them with MS Excel.
If you’ve ever used MS Excel, you know that the program has a search function, which can be very convenient. Naturally, Google Sheets also comes packed with this readily-available feature.
In this article, we’re going to show you how to use Google Sheets to search for specific words and phrases. We’ll also supply you with a couple of additional tips to make using the search function in the program simple.
How to Search in Google Sheets on a Windows, Mac, or Chromebook PC
Google Sheets is an app primarily used on desktop or laptop computers. It’s also a web-based app, which means that it’s solely accessible via a web browser (when it comes to computers). The good news is things pretty much work the same on Windows, Mac, or Chromebook computer devices. So, the same steps apply across the board here.
- Open a spreadsheet in question using the Google Sheets page.
- In the upper menu bar, click “Edit.:
- From the drop-down menu, select “Find and replace.”
- Next to the “Find” entry, type in the word/phrase that you’re looking for.
- Keep on clicking “Find” until you get to the instance of the word that you’re looking for inside the sheet.
You can replace one instance of the selected phrase or all of them. To do this, type in the exact replacement phrase into the field next to the “Replace with” entry. By clicking “Replace”, each instance of the word or phrase that you’ve found will be replaced with your new selected word or phrase, one by one. Select “Replace all” to replace all instances of the selected word at one time.
There are some additional options that are available only on the desktop versions of Google Sheets. In the “Find and Replace” menu, you can select “Match case” to make the search case sensitive. Checking the box next to “Match entire cell contents” will search for cells that are exact matches. The “Search using regular expressions” option will search for cells matching a particular pattern. Checking the box next to “Also search within formulas” will include formulas into the search.
How to Search in the Google Sheets iOS/Android App
Although the mobile/tablet Sheets app doesn’t offer as many options as the desktop Google Sheets app, it still has all the basic functions. The “Find and Replace” function is a part of these basic functions. It’s also worth mentioning that both iOS and Android Sheet apps work exactly the same. So, let’s get to it.
- Start by running the Google Sheets app on your mobile/tablet device.
- Inside the app, navigate to the upper-right corner and select the three-dot icon.
- Inside the menu that pops up, tap “Find and replace.”
- Type in the word that you’re looking for.
- Tap the arrows in the top-right part of the screen to shuffle through instances of that particular word/phrase.
You might want to replace the word that you’ve just searched. Similar to how it’s done on the desktop version, all you need to do is select “Replace with” while in the “Find and Replace” menu. Go ahead and type in the replacement content.
Tap “Replace” for every instance of the word you’ve searched for that you want to replace. To skip replacing a particular instance, just use the arrow function. If you want to replace every single case of the word/phrase in question with a new word/phrase, select “Replace all.”
Unfortunately, the Android and iPhone apps don’t have the same options that you get on the desktop browser app version. You could gain access to these functions on mobile/tablet devices by opening the browser in desktop mode and navigating to Google Sheets as you would on a desktop computer. This is not an ideal option, but if push comes to shove and you need to perform this task immediately, it’s nice to know that you can do this.
To open Google Sheets in desktop browser mode on iOS devices, open the native Safari browser, tap the two-A icon in the upper-right corner of the screen, and select “Request Desktop Website.” On Android, open the Chrome web browser. Tap the three-dot icon and check the box next to “Desktop site.”
How to Search in Google Sheets with a Shortcut
There are many functions in Google Sheets that allow you to use the app effortlessly and quickly. Shortcuts are a prime example here. Of course, the shortcuts are only available on desktop versions of Google Sheets. To quickly access the “Find and Replace” function in Google Sheets, use the Ctrl+H shortcut. This will open the same menu as mentioned before.
However, there is a shortcut that is solely focused on finding words and phrases inside Google Sheets. This can be a useful alternative to the “Find and Replace” function, as it allows you a clear view of the spreadsheet while you shuffle through the search results, as opposed to having the find/replace options of the “Find and Replace” tool. This option is only available through a shortcut.
- Press Ctrl+F.
- Type in the word/phrase in the available box.
- Shuffle through the instances of the search query using the arrows next to the search box.
How to Search Duplicates in Google Sheets
There are instances where you might want to find, highlight, and potentially remove duplicates in Google Sheets. Keep in mind that this option is only available on desktop browser versions of Google Sheets. There are a few methods to deal with duplicates in Google Sheets.
The Remove Duplicates Feature
This option will help you find and remove duplicates in a single column, several columns, or in the entire worksheet.
- Highlight the entire column or columns where you want to check the duplicate data.
- Go to the top menu bar and click “Data.”
- From the drop-down menu, select “Remove duplicates.”
- Select which columns you want the feature to analyze.
- Press “Remove duplicates.”
- The Sheets app will automatically find and remove the duplicates for you.
If you don’t want to remove the duplicates, but just locate them, you can use the Color Highlighting function.
- Select the column/columns you want to check.
- Click “Format” in the top bar menu.
- From the drop-down menu, go to “Conditional formatting.”
- Select the range from the Conditional format rules menu.
- Select “Custom formula is” under “Format rules.”
- Paste this formula into the box below the Format rules:
- Go to the “Formatting style” section, select the “Fill color icon”, and choose the color that you want to use to highlight the results.
- Click “Done.”
The duplicates should be highlighted in the color that you’ve selected.
How to Search All Tabs in Google Sheets
While you might not want to replace any words or phrases, you may still want to find them across all tabs in Google Sheets. To do this, you’re still going to have to use the “Find and Replace” function that we’ve already covered.
- Enter the “Find and Replace” menu and type in the word/phrase.
- Next to the “Search” entry, click the “Specific range” drop-down menu.
- Select “All sheets” from the box.
- Proceed to use the “Find” function normally (as explained before).
How to Search a Column in Google Sheets
The easiest way to search a column for a particular word/phrase is to set a specific range for the search using the “Find and Replace” feature in Google Sheets. Here’s how to go about this.
- Open the “Find and Replace” menu.
- Type in the content you’re seeking.
- Under the “Search” section, select “Specific range.”
- Instead of using a formula, simply click the “Select data range” feature to the left of this box.
- Now, select the entire column by clicking its letter in your sheet.
- Click “OK.”
- Continue your search as explained in the first section of the article.
The best thing about this method of searching is that its use isn’t limited to finding entries in a single column. If you want to select multiple items, just hold down the Ctrl key on your keyboard and form your search grid. To select multiple columns, click on the column letters, click on row numbers, click individual fields, or drag to select multiple fields inside the Sheets file. These are probably the best ways to perform detailed searches in Google Sheets.
What If You Make a Mistake?
Selecting the “Replace all” function at the wrong moment can make you think you’ve absolutely ruined all your hard work. Fortunately, it really isn’t a big deal, especially in Google Sheets. You can undo any action by using the left-facing arrow toward the top-left corner of any Google Sheets page. Or, you can use the Ctrl+Z shortcut to perform the same function.
Google Sheet Searches
As you can see, doing searches in Google Sheets is very straightforward once you get used to it. We recommend using the Specific range search function in the “Find and Replace” menu if you need to search in specific columns, rows, fields, ranges, or a combination of the mentioned. For a simple search, just use the Ctrl+F shortcut.
We hope that we’ve shed some light on performing searches in Google Sheets. If you have any questions or additional tips, refer to our comments section and don’t refrain from joining in on the discussion.