How to Search in Google Sheets [Complete Guide]

Today we are going to learn, how to search in google sheets, as we all know that google sheets provide the features of creating spreadsheets for statistical analysis and visualization, due to which we may have a huge bulk of data in the form of words, numbers, symbols, equation, etc.

So, if we are looking for a specific text then we need to use search or find in google sheets. So let you know how to search in google sheets. After having a data chart, you just have to press Ctrl + F then there is a dialogue box appears on the right top of the google sheets as shown below:

Use Case of Search Option in Google Sheets

As we have talked about that in google sheets, we usually have large amount of data, tables, charts &, etc. and we have to go for some particular data so it’s not easy to go and search one by one word or row or column so we need to use the search feature in google sheets, by which we can find any text easily and efficiently. It helps us to find and replace any text and also saves time to retrieve any specific data. This is why we need to learn – How to search in google sheets.

How to Search in Google Sheets

The search features in google sheets may be used in different criteria, for example, you may find any unique and specific word in google sheets, and we may also search for a particular name If we find any duplications, we can also replace them, it also helps If we are looking for any particular row or column. There are some advanced features as well as you may create your search bar in google sheets, these are all variations we are going to learn step by step accordingly, so let’s start without wasting our time.

How to Search for a Word in Google Sheets

As we have discussed at the start How to search in google sheets there is no difference from searching for a word in google sheets, one method we have to learn is by pressing the shortcut key Ctrl + F. Now we will learn one more method to search for a word in google sheets.

Step 1:

Simply press Ctrl + F, and a box will appear.

Step 2:

Type the word you want to search

Step 3

The word occurrence(s) will be highlighted by google sheets; this also includes substrings.

There are some more conditions you may apply if want to search for a word in a specific range you format it you may also give formulas conditions and other cases as shown.

How to Search for a Name in Google Sheets

Same as in our google sheet if we are looking for a particular person’s data, we may also search data by any name of a thing, same as we will go in Edit button then Find and Replace, a pop-up window will appear and write a name of any anything

Searching “name” in google sheets is identical to searching any word, you can write a name in the search box, and from the very first letter the search filter will be applied and you will see all the names highlighted that have your searched substring.

Step 1:

Use CTRL + F for the search bar, enter the name you want to search

Step 2:

The searched term will be highlighted

How to Add a Search Bar in Google Sheets

Why do we need to create a search box in Google Sheets, when there is already a search / find box in google sheets available to search across the sheet? Well, the answer is we will not be going to use the custom search box for search purposes only, what we will do is we will apply filters and formulas to make it more customized.

Step 1:

Create a sample data to make a search box for it.

Keep in Mind:

In this case, you cannot hide the columns containing actual data

Step 2:

Click on Data > Data validation in the top main menu.

Step 3:

In the data validation box, in criteria select “List of items”

Step 4:

In the next cell write down the options in CSV format.

Step 5:

Click on Save, and now have a drop-down list in your selected cell

Now we have got a dropdown list in selected cell.

Apply some basic designing to make the sheets look better with a search box.

Step 6:

Start writing a query for this function, the very first variable will be your entire range here: A2:C60

Step 7:

The next variable is SELECT * WHERE followed by a column name in which your searchable data is written (A) under double quotes, after a comma to separate it from the first variable.

Step 8:

Then without a comma, in the double quotes write “&&” and in the middle of && write your cell address where your search box is created. In this case E3, so the next variable is ”&E3&”

Step 9:

The next variable is the address of the cell where the terms will be searched, in this case ‘“&F3&”’, remember that this value will be under the single and double quotes because we cant use double inside double-quotes.

Step 10:

Finally, hit Enter and your search box is ready.

Step 11:

Testing (Remember that A and a are different)

How to Search and Replace a Word in Google Sheets

Search and replace in google sheets are a built-in function and it’s very simple and frequently used for searching and replacing purposes in a single sheet or the entire workbook, this feature is very helpful when we have a minor mistake in a big data and we want to apply changes to all the entries in few seconds.

Step 1

Simply search the term you want to replace using Ctrl + F shortcut.

OR

Go to Edit > Find and Replace

Step 2

Enter the word and all the occurrences will be detected and highlighted

Step 3

Click on three vertical dots to find the replace box.

Step 4

Write the new text to replace it with the old one, and look for further functionalities and features to find and replace

Step 5

Click on replace all, and you’re done.

Step 6

A confirmation message will appear, click on done to back to your sheet.

All 2 are replaced by Two.

How to Search a Column and Row in Google Sheets

Using find and replace the entire sheet rows and columns, but what if we need to search for a specific term inside a specific column or row? We can use the Lookup function that helps us to search a specific term inside of a column or row. We will see how does it work in the below step-by-step procedure.

Search for Word from a Column in Google Sheets

Step 1:

Write the LOOKUP Formula in any empty cell (very easy)

Step 2

Formula: =LOOKUP (“search_term”, cell range)

               =LOOKUP (“john”, “A2:B13)

For example, this will return the ID of John.

For example,  =LOOKUP(“Monica”, A2:C13) will return the Age of Monica.

Search for Word from a Row in Google Sheets

If you want to search for a string in a row, this will be a bit tricky. It is only valid for when the return value is true.

For invalid or False return, we will see Error message.

For a valid Lookup, it will return the name as show below.

How to Search for Duplicates in Google Sheets

Firstly, I will tell you that where we can use this function of google sheets depends on the type of data, we have let’s suppose we have an office staff list that contains name location, and employee id as we know every staff has their unique id nor same id. So, we check this error by using search for a duplicate in google sheets, same as if we have any employees of same cities so we can distinguish them with the same cities, let’s make you understand how it works step by steps,

Step 1:  

First, select the range of data in which you have to find duplicated values,

Step 2:

Then click on the Format button from the menu bar and go to the Conditional formatting,

Step 3:

Then change the format cells to custom formula,

Step 4:

Now put the formula =Countif(j:j,j1)<1

Step 5:

You may also customize highlights colors font editing, etc.

Step 6:

Now click on Done, all duplicated values will be in front of you.

Frequently Asked Questions

How can I search for a term inside links as well?

In the search and replace the window, you can get a checkbox for searching and replacing a term from the links as well.

How can I highlight the duplicate entries to remove later?

Conditional formatting can be used for highlighting the duplicates in multiple rows and columns by cell or text colors. You can use a custom formula as the rule for this purpose and when providing the data range, you need to provide the complete range i.e., A2:D10.

How can I search for a term in one column or row?

You can use the Lookup formula as described in the step-by-step procedure above for searching anything inside of a column or row.

Can I Use Conditional Formatting in Google Sheets to Highlight Search Results in Multiple Columns?

Conclusion

Summarizing the learning outcomes from this article here, we learned several methods, formulas, and use cases to understand how to search in google sheets, we tried to cover all types of common searching methods and some handy formulas to make your searches easy and functionally strong with custom features. This is it, I hope you have enjoyed the article and have learned many new things from this article, if yes, then don’t forget to share it with your social friends and consider subscribing to our Office Demy blog for much more interesting learning material. Thank you.