The main purpose of a spreadsheet is to organize large amounts of data. However, with such massive data, searching for what you’re specifically looking for can be a bit tricky.
Google Sheets is one such platform that allows its users to organize data in a structured manner, allowing data entry and analysis not only easier but more convenient as well. When this data is extensive and consists of many values and categories, it gets a bit overwhelming to make sense of the spreadsheet. This extensivity also makes it difficult for users to search for specific categories and so, it is important to know how to search in Google Sheets.
When working on Google Sheets, sometimes having to search for specific values is inevitable and, in such a situation going through each single cell won’t only be tricky but also seriously bothersome, especially if bound by a time limit.
To avoid its users from going through all this unnecessary stress, Google Sheets offers a very easy solution in response to the question “How to search in Google Sheets?”.
The solution to the discussed problem of how to search in Google Sheets comes as:
- An option called “Find and Replace”
- The SEARCH Function
- The FIND Function
- Conditional Formatting
All of these options help users in identifying the values or text that they are searching for inside a Google sheet. These features differ from each other in certain respects which will be elaborated on in more detail in this article, however, the essence of all of these remains the same i.e. how to search in Google Sheets.
Contents
How to Search in Google Sheets with the “Find and Replace” Option
In order to understand the “Find and Replace” tool on Google Sheets, let’s look at its working on a data sample. Suppose we have this data about the sales of a flower shop in our spreadsheet. Our intent is to look up all the roses sold and replace them with sunflowers.
To search through this data using the find and replace option, you need to follow a series of steps. Read on to understand these steps.
Step 1:
First off, you need to go to the top menu bar and look for the option “Edit”. Click on it and a drop-down window will appear.
Step 2:
From this drop-down menu, click on the “Find and replace” option which is listed at the bottom.
Now, you will have the “Find and replace” toolbox opened. The Google Sheets’ “Find and replace” box can also be opened through Google Sheets’ shortcut keys. If using the keyboard is your preferred method of convenience, you can:
- When using Mac
Press Cmd+H on your keyboard
- When using Windows
Press Ctrl+H on your keyboard
Step 3:
In the input box next to “Find”, enter the word(/s) that you want to look for. Because we want to find the word “Roses” we will enter that into the available space. This will show the exact match for the word in your data.
Step 4:
Click on “Find” at the bottom-left of the “Find and Replace” toolbox.
Clicking on “Find” again and again will one-by-one highlight all the cells containing the word or the string “Roses” in them.
This will work even if only a part of your search query is contained by the cells. For example, if we try searching for just “Ro”, Google Sheets will still highlight the cells with “Roses”.
How to Replace in Google Sheets with the “Find and Replace” Option
To replace in Google Sheets, you can use the same “Find and Replace” toolbox. Let’s look at the details of the pop-up box again.
Because we wanted to replace “Roses” with “Sunflowers”, here, in “Replace with” we will enter this desired new string. Enter the data, and press “Replace all”, or “Replace” if you want to replace them one by one. This will allow you to manually select which cells’ data to replace and which not to.
Now in your current spreadsheet, let’s try replacing only a part of the cell. As an example, let’s replace “Sunflowers” with “Whiteflowers”. For this, we don’t need to enter the entire word “Sunflowers” but simply entering “Sun” in “Find” and “White” in “Replace with” will perform the intended function.
More “Find and Replace” Tool Box Options
The Google Sheets’ “Find and Replace” feature offers search and replace options with much more specificity as well.
These added features allow users to be more specific with their searches according to their requirements and offer a more seamless experience.
The first drop-down box allows you to search for your specified string in not just one but multiple sheets on your main Google Sheet. It also gives you the option of searching for the string in a specific range. For example, if you want to search for products with the Unit Cost of 20 and then replace this unit cost of 20 with 30, all you need to do is to fill in the details accordingly and specify your range of values as shown here.
The next two checkboxes allow you to be more specific with the search with respect to the range of values being searched.
a. Match Case:
This ensures case sensitivity of the search string and makes sure to not highlight “roses” if you searched for “Roses”.
b. Match Entire Cell Contents:
The feature of “Match entire cell contents” ensures that exactly whatever the user searches for is looked for by Google Sheets. For example, with this box checked, searching for “Sun” will not highlight “Sunflower” and “The are no entries matching Sun” will be displayed if there is no cell with the exact word “Sun” in the sheet.
c. Search using Regular Expressions:
The “Search using regular expressions” allows search specifically with Google Sheets’ “regular expressions” which are short text-based commands that perform functions like searching for, extracting, and replacing text.
d. Also Search within Formulas:
This option can be checked if you want to search for your query in the formulas used in Google Sheets too. For example, if you want to see which cell’s value is based on a value used in another cell, this can be helpful. This can also be used if you’re replacing a value that has been used in formulas throughout the sheet.
How to Search in Google Sheets using the Function “SEARCH”
The function “SEARCH” is offered by Google Sheets to search for non-case-sensitive substrings within larger string values in a range of cells. The function is useful when you have larger values in individual cells, and tells you the location from which your desired value starts in that one cell.
The syntax of the function is =SEARCH(search_for, text_to_search, [starting_at])
Here,
- SEARCH is the name of the function.
- search_for refers to the substring that you look for within your specified larger text or the text_to_search.
- text_to_search is the larger string that is searched to look for the search_for value.
- starting_at is an optional syntax value that directs the position form which Google Sheets is to look for the search_for in the text_to_search.
For example, if we want to know where exactly “flowers” in “Whiteflowers” starts from we will use the formula,
=SEARCH (“flowers”, “Whiteflowers”)
And, google sheets will display the answer as 6.
How to Search in Google Sheets using the Function “FIND”
The FIND function in google sheets has the exact same functionality as that of the SEARCH function, with the only difference between the two being that FIND is case-sensitive whereas SEARCH key is not.
FIND also has a similar syntax,
=FIND (search_for, text_to_search, [starting_at])
How to Search in Google Sheets using Conditional Formatting
“Conditional Formatting” searches for cells with defined values and highlights them. Here is a step-wise guide on how you can use this feature in Google Sheets.
Step 1:
On the top menu bar, go to “Format”. A drop down menu will appear. From this, go to “Conditional formatting”.
When you click on “Conditional formatting”, in the right-most corner of the screen, you will get multiple options for conditional formatting settings in the sidebar titled “Conditional format rules”.
Step 2:
Here, let’s highlight cells with “Sunflower”. For this, first of all, we need to mark our search range. After that we select our desired conditional formatting rule, which for our example can be “Text contains” or “Text is exactly”. The difference between these two is pretty evident in that the first option allows users to search for any string that is found inside a particular text whereas the second option requires users to be precise in what they are looking for.
After selecting “Text contains” or “Text is exactly” for our example, we enter “Sunflowers” in the text input box.
According to your own personal requirement, you can select a suitable conditional formatting rule and go on according to that. Besides searching for text values, you can also define mathematical queries or data-based queries for your conditional formatting; Google Sheets offers a number of options for both alphabets, numerals, and the two combined.
Step 3:
After defining the qualification rules we define our formatting style, which includes font color, font styling, and cell fill color options. For now, let’s just go with the basic Google Sheets formatting styles. You can use this custom function to define any based on your needs or personal preference.
After this, we click on “Done” to see our desired result.
Read more on conditional formatting rules and features here.