Google Sheets is a powerful tool for organizing large sets of data. However, with large amounts of data it can be difficult to search in Google Sheets and find a specific entry among thousands of rows and columns.
However, manual cell-by-cell searching is inefficient and time-consuming. Fortunately, Google Sheets offers search operators to simplify finding data.
You can search for something in a Google Sheet in different ways. It doesn’t matter what device you use. The simplest way allows you to instantly look up a data point.
Additionally, you can also create constant searches that remain available for future use without additional plugin.
In this Guide we will show you the 5 different ways :
- Find Feature
- Conditional Formatting
- Find and Replace
- Using Match Function
- Using conditional formatting and the Search formula to create a search box
Whether you use a Windows PC, Mac, or Chromebook, Google Sheets makes it easy to find the information you need through large amounts of data.
Specifically, the search tools help you find what you want quickly without checking every cell one by one.
How to search in Google Sheets ?
Searching through large spreadsheets can feel like finding a needle in a haystack.
However, Google Sheets provides powerful yet simple search tools to help you efficiently locate the exact data you need.
In this section we’ll show you the different methods to search in Google Sheets on all devices.
Follow below mentioned instructions to search in Google Sheets efficiently:
Find Feature
The simplest search method in Google Sheets is the built-in search tool (Find Function).
Fundamentally, with a single action, this feature allows you to locate specific data points within your spreadsheet.
Similarly, whether you work in Google Sheets or another document app, the Search feature provides an easy way to search your content.
Follow below mentioned steps to use Find Feature to lookup you data in Google Sheets.
- To begin, press (Ctrl+F) on Windows or (CMD+F) on macOS to search for something in Google Sheets. This will show you a window where you can type your search.
- Next, within this window, type the specific text or value you want to find. In particular, the window will calculate and display how many instances of your search term exist in the spreadsheet.
- From there, you can utilize the arrow buttons to easily navigate between each matching result. By doing this, the cells containing your search query will be highlighted in green color for easy identification.
Conditional Formatting
If you want your searches to remain as highlighted, you can use the “Conditional Formatting” feature to search for the data in Google Sheets.
Additionally, this feature allows searches to remain on the sheet so you can easily find the same information later.
To use Conditional formatting to find your data in Google Sheets, follow the below mentioned steps:
- To begin, go to Google Sheets.
- Next, click on the top left corner of the sheet (the cell with the check mark in the image below) to choose the entire sheet or choose a column. It depends on what you want to search for.
- From there, go to the Format tab and select the Conditional Formatting option.
- Then, open the drop down menu under Formatting Rules.
- Subsequently, choose the correct option for your search. We’re looking for a name, so we’ll choose the Text Contains option.
- Following that, type what you want to find in the search box and click the Done button to save your changes.
- Consequently, the cells that contain your searched data are highlighted.
The benefit of using this feature is that your search will remain in the Google Sheets until you remove the conditional formatting rule.
Find and Replace
The “Search” tool is very easy to use. But it only performs basic searches. However, if you want more options for your searches, use “Find and Replace” in Google Sheets.
Specifically, this feature allows you to change how the search works in different ways. You can refine your searches by capital letters, use special search codes, and search all sheets at once.
Follow below mentioned steps to use this feature to search for the data in Google Sheets. That gives you more control over your searches.
- To begin, go to the Google Sheets document and click the Edit button.
- Next, select the Find and Replace option.
- Subsequently, type what you want to find and click the Search button to view your search.
- Next, if you have more than one search, click the Search button again to view the next cell with your search.
- Furthermore, when you see the words “No more results found, hanging around” on the screen, you will return to the first search.
- Moreover, if your document has more than one sheet and you only want to search the current sheet, click the All Sheets button and select this Sheet option.
- Additionally, choose the Specific Range option to search only some cells on the same sheet.
- Consequently, after finding your search, click the Done button to close the window.
Using Match Function
Google Sheets has a built-in MATCH formula to find things in a certain area of cells.
The formula is =MATCH(Key, range type).
In this syntax the Key is for (Word, number or combination of both).
Range is for (Range of cells to search).
Type is for (How does the function work to find the desired results). We’ll use (type=0) for exact match.
When using the formula, put commas and spaces between each part.
Follow below mentioned steps to use the match function in Google sheets.
- Firstly, go to a Google Sheet and click on a cell that is empty.
- Secondly, type = MATCH and click the Tab button to choose the Match formula.
- Then, type whatever you want to find in double quotes. For example, we use “Eva Rivera” as a search.
- After that, enter the cell range where you want to search. Our cells are A1:A1015, so we’ll write the same thing.
- Now, since we want the exact match so; we’ll use 0 as our type. Type 0 and press Enter to search.
- As a result, you can see that the Match form gives a result. It tells us that the entry is in 23 row.
The problem with the Match function is that it only returns the first thing it finds. So if you have more than one thing in your cells, you will only see the place of the first thing.
Use conditional formatting and the Search formula to create a search box
To begin, conditional formatting is great for saving search terms you use frequently. However, it is not very easy if you need to change the data what you search frequently.
Therefore, let’s make a search box that allows us to find different things quickly in Google Sheet. First, we’ll use some of Google Sheets’ built-in tools, such as conditional formatting and the SEARCH formula.
Furthermore, this will allow you to enter new search terms without having to remove the conditional formatting each time. It makes searching more flexible when your needs change in your Google Sheets.
- First, choose a cell to use as a search box. We choose L5.
- Next, select the column you want to search. Go to Format > Conditional Formatting.
- Then, in the Format rules, use drop down and select “Custom Formula.“
- After that, enter this formula: For example, if you search for column A and the search box is J4: =SEARCH($J$4,$A1)>0. Click “Done.“
- Now your search box is ready! As you type, cells that match that search term will be highlighted.
Note: To perform a case-sensitive search, use the FIND function instead of the SEARCH function. Next,in the formula shown above, replace “SEARCH” with “FIND” to make the search terms case-sensitive. Then, the FIND function will only return results that exactly match the case of the search terms, unlike SEARCH, which ignores case by default.
Also Check: How to make Google Forms public ?
How to search in Google Sheets on phone ? (Android and iPhone)
Follow below mentioned steps to search in Google Sheets on phone:
- Firstly, open a spreadsheet in the Google Sheets app.
- Secondly, tap More, represented by a three-dot icon in the top right corner, then Find and replace.
- Thirdly, type the word you want to find and tap Search, represented by the magnifying glass icon.
- Next, tap the arrows in the top right to see each time the term was found.
- Additionally, to replace the word with another one, type the new word in the Replace with box and tap Replace or Replace all.
- Finally, to return in the document, select Close.
Note: To search in Google Sheets on an iPad, follow the same steps outlined above for mobile phones.
How to search for duplicates in Google Sheets ?
Sometimes you may want to find, mark, and perhaps delete repeating items in Google Sheets.
However, you can only do this on a computer, not a phone or tablet. There are different ways to deal with repeating things in Google Sheets.
Highlight Duplicates
By utilizing this option, you can search and highlight data that are repeated in one column, many columns, or the whole worksheet this function highlight the data by changing its color.
Follow below mentioned steps to highlight duplicates in your Google Sheets:
- Firstly, choose the column or columns in which you want to search for repeated data.
- Secondly, click “Format” in the top menu bar.
- Thirdly, go to “Conditional Formatting” in the menu that appears.
- Next, choose the range from the menu that says Conditional Formatting Rules.
- Then, choose “Custom formula is” under “Format Rules.”
- Next, copy and paste this formula into the box below the formatting rules: “=countif(A:A,A1)>1”
- Following that, go to the “Format Style” part, click on the “Fill Color Icon” and choose the color you want to use to mark the results.
- Then, click “Done.“
- Finally, repeated data should be marked in the color you have chosen.
Remove Duplicates Feature
With this option, you can search and delete data that are repeated in one column, many columns, or the whole worksheet.
Follow below mentioned steps to remove duplicates in Google Sheets:
- Firstly, select the entire column or columns in which you want to search for repeated data.
- Secondly, click “Data” in the top menu bar.
- Thirdly, choose “Remove Duplicates” from the menu that appears.
- Next, choose which columns you want to check for repeated data.
- Then, click “Remove Duplicates.“
- Finally, the Sheets will find and remove repeated data for you.
Also Check: How to make Hanging Indent in Google Docs ?
How to search all tabs in Google Sheets ?
If you need to find or modify specific text across multiple sheets or tabs within a Google Sheets file, the Find and Replace tool allows you to do so from a single location.
Moreover, even if you don’t need to replace text, this feature can help locate matching words or phrases wherever they appear with just a few clicks, thereby saving you time compared to searching each tab individually.
Follow below mentioned steps to search all tabs in Google Sheets:
- Firstly, go to the “Find and Replace” menu and type the word or phrase you want to search for.
- Secondly, next to “Search,” click the menu that opens.
- Next, select “All Sheets” from the options.
- Finally, use the “Find” function as usual (as we explained before).
How to search a column in Google Sheets ?
To look for a word or phrase in a column, you can use the “Find and Replace” feature in Google Sheets and set a specific range to search in.
Follow below mentioned steps to search column in Google Sheets:
- Firstly, go to the “Find and Replace” menu and type what you are looking for.
- Secondly, under “Search,” select “Specific Range.”
- Thirdly, instead of typing a formula, simply click the “Select Data Range” function to the left of this box.
- Next, choose the entire column by clicking on its letter on your sheet.
- Finally, click OK.
Do your search as we told you in the first section. The good thing about this form of search is that you can use it for more than one column.
Furthermore, if you want to choose more than one thing, simply press the Ctrl key on your keyboard and create your search area.
Alternatively, to select more than one column, click column letters, click row numbers, click individual fields, or drag to select many fields in the Sheets file.
Conclusion
In conclusion, Google Sheets offers several ways to search for data in your spreadsheets. You can use the Find feature, Conditional Formatting feature, or Find and Replace feature to locate specific text or values on your sheets.
Each of these features has its own advantages and limitations, depending on your needs and preferences.
By using these search tools, you can save time and effort when working with large amounts of data in Google Sheets.
We hope this article helped you understand how to search in Google Sheets effectively.
Also Check:
How to exclude words from Google search ?
Reference:
https://support.google.com/docs/answer/62754?hl=en&co=GENIE.Platform%3DDesktop
https://support.google.com/docs/answer/62754?hl=en&co=GENIE.Platform%253DiOS
Frequently Asked Question’s (FAQ’s)
The difference between search and find in Google Sheets is that search is a function that returns the position of a substring within a text, while search is a tool that highlights all cells that contain a specific text.
Yes, you can search in the Google Sheets app using the find and replace tool or the search function.
There are several ways to find and delete multiple rows in Google Sheets, depending on your needs and preferences. You can follow the mentioned steps: 1. Use the find and replace tool. 2. Use the filter tool. 3. Use keyboard shortcuts (Press Ctrl + \ (Windows) or ⌘ + \ (Mac)). 4. Use a formula.
