How to Search in Google Sheets (Complete Guide)

how to search in google sheets 18

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:

how to search in google sheets 1

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.

See also  Project Management Templates in Google Sheets (Free Templates)

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

how to search in google sheets 2

Step 3

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

how to search in google sheets 4

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 search in google sheets 3

See also  How to Get Data Using Google Sheets API (Complete Guide)

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.

how to search in google sheets 5 

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.

how to search in google sheets 6

Step 3:

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

how to search in google sheets 7

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

how to search in google sheets 8

Now we have got a dropdown list in selected cell.

how to search in google sheets 9

how to search in google sheets 10

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

how to search in google sheets 11

Step 6:

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

how to search in google sheets 12

how to search in google sheets 13

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.

how to search in google sheets 14

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&”

how to search in google sheets 15

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.

how to search in google sheets 16

Step 10:

Finally, hit Enter and your search box is ready.

Step 11:

Testing (Remember that A and a are different)

how to search in google sheets 17

how to search in google sheets 18

how to search in google sheets 19

See also  How to Use IFNA Function in Google Sheets (User Guide)

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

how to search in google sheets 20

Step 2

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

how to search in google sheets 21

Step 3

Click on three vertical dots to find the replace box.

how to search in google sheets 22

Step 4

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

how to search in google sheets 23

Step 5

Click on replace all, and you’re done.

how to search in google sheets 24

Step 6

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

how to search in google sheets 25

All 2 are replaced by Two.

how to search in google sheets 26

See also  How to Find & Highlight Duplicates in Google Sheets (5 Methods)

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)

how to search in google sheets 27

Step 2

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

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

For example, this will return the ID of John.

how to search in google sheets 28

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

how to search in google sheets 29

how to search in google sheets 30

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.

how to search in google sheets 31

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

how to search in google sheets 32

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

how to search in google sheets 33

how to search in google sheets 34

See also  How to Change Text Color Using Conditional Formatting in Google Sheets

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,

how to search in google sheets 35

Step 2:

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

how to search in google sheets 36

Step 3:

Then change the format cells to custom formula,

how to search in google sheets 37

Step 4:

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

how to search in google sheets 38

Note: If you are looking for a duplicate value in the “J” column then you will write “J” in the formula if you are looking for the “I” column you will write “I” in the formula.

Step 5:

You may also customize highlights colors font editing, etc.

how to search in google sheets 39

Step 6:

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

how to search in google sheets 40

See also  How to Use Google Sheets Conditional Formatting Multiple Columns

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.

To recap, we learned how to search in google sheets, we have seen many use cases for search and find in google sheets, we saw some functions and built-in methods to search in google sheets, and we have also discussed and implemented a mini project in the search box section, where we made a complete functional search box in the google sheet and test it. We have seen examples and formulas for advanced searching and replacing data for bulk data.
See also  How to Strikethrough on Google Sheets [4 Methods]

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.

Content Protection by DMCA.com

M. Shaiq Ansari

Hi, I am Shaiq. A young and self-motivated content writer having years of experience expertise in MS Office suite, Google docs Editor Suite. I have a technical education background that empowers me to stand out in today's digital world. I am currently a freelance content creator and a part of a local digital marketing agency in Karachi Pakistan.

OfficeDemy.com
Logo
Enable registration in settings - general