If you’ve got a ton of data to sift through, it can be very useful to know how to search in Excel. The good news is that by using the tools in Excel, you can quickly and easily find specific words or values. You can even replace a specific word or value with another. If your word or value occurs multiple times throughout your spreadsheet, you can every instance with just a single click. You can also use Excel functions to find specific values or strings and return useful information about them. If you’re ready to start hunting through your spreadsheet, then here’s how to search in Excel.
How to Search in Excel Using Find
If you’re trying to find a specific piece of information within your spreadsheet, you can use the Excel Find tool to help you find it. You can search for the exact word or value, or use wildcards to perform a less specific search. To search in Excel using Find:
Within: Choose to search within the current sheet or the entire workbook. Search: Choose By Rows or By Columns to search only within one row or column at a time. Look In: Tells Find whether to search within Formulas, Values, Notes, or Comments. Match Case: Only returns results where the case is an exact match for what you entered in the Find What field. Match Entire Cell Contents: Ignores cells that contain your search query but also other text. Note that in Excel for Mac, this option is called Find Entire Cells Only. Format: You can get Excel to search only for results that have specific formatting. Click Format to set your desired formatting.
How to Search in Excel Using Replace
If you realize that one word or value has been entered incorrectly throughout your spreadsheet, you can use the Excel Replace tool to find these errors and replace them with the corrected text. You can also use this method to replace one value with another. To use Replace in Excel:
How to Use Wildcards in Excel
You can use wildcards to find multiple different words or values within your spreadsheet. These are special characters that tell Excel the criteria for what to search for. To use wildcards in Excel:
Use a question mark (?) to replace any single character. For example, searching for b?g would find bag, beg, big, bog, and bug.Use an asterisk () to replace any string of characters. For example, searching for ba would find words such as boa, bra, banana, bandana, and ballerina.Use a tilde (~) if the text you are searching for includes either a question mark or an asterisk. For example, searching for really~? would find ‘really?’ including the question mark.
How to Search in Excel Using Functions
All of the methods above allow to you find the location of cells that contain the specified words or values. However, you can also use Excel functions to find text or values within your spreadsheet and provide information about those values or their location. Here are a few of the functions that you might want to check out:
FIND: This function is quite different from the Find tool in Excel. It can be used to find the position of a specific character within a string.SEARCH: This is similar to FIND but ignores the case of the character you are searching for.VLOOKUP: The VLOOKUP function finds a specific value in one column and returns the value from a different column in the same row. For example, you could search for a name and return their age from the next column along.HLOOKUP: The same as VLOOKUP but returns a value from a different row in the same column.XLOOKUP: This is similar to VLOOKUP and HLOOKUP but it allows you to look at data on either side of your selected row or column, which the other functions do not.MATCH: This function returns the position of the search value within an array. In other words, it will return the location of the search value within your selected data.INDEX: This is the opposite of MATCH; if you provide a cell reference, it will return the value in that cell.
Exploring Your Data in Microsoft Excel
Learning how to search in Excel ensures that you don’t spend ages trying to find that one piece of information you need. Using the methods above, you can quickly find any text or values within your spreadsheet. Excel is a hugely powerful application, and you could almost spend a lifetime learning how to use all of its functions. You can learn useful features like how to enable or disable macros in Excel. It can also be useful to know how to search for duplicates in Excel. And if your formulas are producing errors, you can learn how to find a circular reference in Excel. Comment Name * Email *
Δ Save my name and email and send me emails as new comments are made to this post.
