Watch Video – How to Search in Google Sheets and Highlight Matching Data Show
If you work with huge datasets in Google Sheets, sometimes, it may get difficult to identify some data points. For example, if you have a list of students and their marks, it can take some time to find a student name and all his/her marks. In this tutorial, I will show you how to search in Google Sheets and highlight the matching data (cells/rows). Something as shown below: Note that as soon as I enter any text in cell B2 and hit Enter, it searches for that data point in column B and highlight the rows where there is a match. Now let’s see how to create this is Google Sheets.
Just Searching in Google SheetsIf you use need to find something quickly as a one-off, you can use one of these methods: The Easiest Way to Search in a Google Sheet – A Keyboard ShortcutIf you just need to find something on your page in Google Sheets or many other programs, all you have to do is:
Use The Find and Replace Toolthis can easily find data you know is wrong and replace it without having to rummage through the whole spreadsheet. Here’s what you have to do:
You can use the dropdown box to only search certain sheets or ranges too. How to Search in Google Sheets and Highlight the Matching DataThere are different ways you can use this technique:
In the below section, I will each of these cases in detail and show you how to create this in Google Sheets. How to Search in a Google Spreadsheet and Highlight Cells with the Matching DataIn this section, I will show you how to create something as shown below: Note that as soon as I enter the country name in cell B1 and hit Enter, it highlights the matching cells in column B. Here is how to make this searchable Google spreadsheet:
Now, whenever you enter any text in cell B1, it will highlight the matching cells in the dataset. Note that there needs to be an exact match. If the text entered is misspelled, or is partial/incomplete, the cells would not be highlighted. In the above example, I am entering the data manually. However, if you have a huge dataset, you can create a drop-down list in cell B1. This will allow the user to see all the options in one place and select the required one. How to Search on a Google Spreadsheet and Highlight Rows where there is a MatchIn the above example, we highlighted only the matching cells. But in a real-world example, you’re more likely to search for a data point and highlight the entire row. Something like shown below: Here are the steps you can use to learn how to search on Google Sheets and highlight the entire row in:
How to Search on a Google Sheet and Highlight Rows With Partial MatchIn the above examples, it was necessary that there be an exact match for the cells/rows to get highlighted. But you may have a situation where you want to get match based on the partial search string. Something as shown below: Note that here as soon as I entered ‘abc’ in cell A1 (it doesn’t matter whether it’s lower case or upper case), all the rows in the dataset that have that string get highlighted. Here is how to search in a Google Sheet with the same method as above:
In case you want the search to be case sensitive (which means that ‘abc’ and ‘ABC’ are different), then use the FIND function instead of the SEARCH function in the above formula. It’s the best method to use when tackling how to find on Google Sheets. So this is how to search in Google Sheets and highlight matching data (be it cells or rows) You may also like the following tutorials:
SumitSpreadsheet Expert at Productivity Spot | Website | + posts Google Sheets and Microsoft Excel Expert. How do I search a column in sheets?Search Using the Find Option to Highlight All Matching Cells in the Sheet. Open the worksheet that has the data.. Use the keyboard shortcut Control + F (for Windows) and Cmd + F (for Mac). This will open a small Find box at the top right part of your sheet.. Enter the string that you want to search in the entire worksheet.. How do you search data in Google Sheets?Search for data. In Sheets, open a spreadsheet and click Edit. ... . Next to Find, enter the text or numbers that you want to find.. Next to Replace with, enter the new data.. Next to Search, choose the sheets that you want to search.. (Optional) To refine your search, select additional options.. Is there a search function in Google Sheets?You can find and replace words in a document, spreadsheet, or presentation with Google Docs, Sheets, and Slides. You can also search within a file using the keyboard shortcut Ctrl + f (⌘ + f on a Mac).
How do I find a specific cell in Google Sheets?Now if you want to jump to a specific cell or row/column, simply hit the F5 key. Note: When you enable compatible spreadsheet shortcuts, you may be able to use some of the keyboard shortcuts that are also available in other popular spreadsheet tools such as MS Excel.
|