There are several ways to search in Excel. The first options are if you have a large spreadsheet of data and need to find a specific piece of data in a cell or group of cells.
The second set of options involves using search functions such as VLOOKUP or HLOOKUP, which allow you to search for data in one sheet and display the results in a second cell or on another sheet.
In this article, you will learn all the possible ways to search Excel to find the best one for your situation.
Using Find In Excel
When Excel is open to your data spreadsheet, you can search for anything in the spreadsheet using a simple word search or special wildcards.
1. Select the main menu. In the Editing group on the ribbon, choose Find and Select, then choose Find.
You will see a simple Find and Replace window, but you can see all the advanced options by selecting Options.
– /
You can use the following options to fine-tune your search:
- Find What: Enter the text or number you want to find.
- Format: Click this button to search only for text with the formatting specified in the Find Format window.
- Inside: Select a sheet or book to search within the current sheet or the entire book.
- Search: Select By Rows or By Columns to search by rows or columns (and stop at that row or column when it finds a result).
- Look inside: you can expand your search to other areas of your spreadsheet by specifying your search to look in formulas, values, notes, or comments.
- Match case: The search will be performed using case-sensitive text matching.
- Matching all cell content: this will search for cells containing only the text you typed and nothing else
If you choose Find Next, you will see that each incident in the spreadsheet that has text highlighted is highlighted. Press the button again to go to the next result. If you want to see them all at once, just select Find All.
This will display all the results, along with the sheet and the cell number in which they were found, in a list at the bottom of the window. Just select any of them to see that cell on the sheet.
Excel searches wildcards
When you enter search characters in the Find box, it is important to understand how wildcards work. They allow you to customize advanced search so that you can search Excel and find exactly what you are looking for in the worksheet.
- Question mark (?): Replace any single character or number. For example, enter c? T to find any three-letter word that starts with c and ends with t, such as cat, cut, or cot.
- Asterisk (*): replaces parts of words. For example, enter c * t to find a word of any length from the basket, and enter for count and court.
- Tilde (~): Use this character to override the special characters above. For example, if you really want to search for the word “user?” (including a question mark) can you enter user ~? and will this override the special? to perform a search, including a question mark.
Using Replace in Excel
In the same Find and Replace window, if you select the Replace tab, you will see additional options that will allow you not only to find text or numbers in the table, but also to replace them with other text or numbers.
You can also open this window from the Replace tab by selecting the main menu, choosing Find and Replace in the Editing group on the Ribbon, and then choosing Replace. As in the previous section, you will need to select Options to see all the advanced search features.
All advanced search functions work exactly the same as in the Find tab. The only difference is that you will now see the Replace With field.
This field works like this: you enter the text or number that you want to replace the text entered in the “Find” field. Then follow these steps to find and replace all instances of what you are looking for.
- Select Find Next to highlight the first location with the text or number you are looking for.
- Select Replace to replace this instance. If you find one that you don’t want to replace, simply select Find Next to move on.
- Continue to loop through Find Next and Replace until you have found all the instances of what you were looking for. and replaced.
If you are very brave, you can choose “Replace All” instead of going through each step as described above. However, if, under any circumstances, the word or number you are looking for is a piece of text in a cell that you do not want to replace, you risk losing data that you do not want to replace.
Use functions to search in Excel
Excel also has many useful functions that let you search columns, rows, or tables for information and return related information.
Below are a few examples of the most popular functions that you can use to search in Excel.
- VLookup: Look up data in one column of a table to return the value of another column in the same table. row.
- Index-Match: does the same as VLookup, but the lookup data does not need to be the leftmost column in the lookup table.
- Filters: you can use filters. to filter out the data you don’t need and display only the data you are looking for.
- Find matching values. Excel has several functions that let you find duplicate values ??in Excel, such as Exact and Match.
Finding data in Excel doesn’t have to be difficult. If you know what you are looking for and what you want to do with the data, there is a search option.
–