How-To

How to Search in Excel

By Adam Davidson

Published February 9, 2023

A spreadsheet can contain a huge amount of data, but finding it shouldn’t be hard. Here’s how to search in Excel.

Spreadsheets are incredibly useful for storing data. If you’ve ever tried to see just how far the rows or columns go in Excel, you’ll know full well just how much data it can hold.

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:

1. Make sure that you are in the Home tab.
   
2. In the Editing section of the ribbon, click Find & Select.
   
3. Select Find.
   
4. Enter the text that you want to find into the Find What You can also use wildcards in your search, which are explained later in the article.
   
5. If you need additional search options, and they’re not already visible, click Options.
   
6. Select your additional options. These include:

• 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.
  

7. Once you’re happy with your search settings, click Find All to find every instance of your search query within your given parameters.
   
8. You’ll see a list of all the cells that were found. Click one to be taken to that cell.
   
9. Alternatively, if you only want to find the next single instance, click Find Next instead.
   

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:

1. In the Editing section of the Home tab, click Find & Select.
   
2. Select Replace.
   
3. In the Find What field, enter the text that you want to replace.
   
4. In the Replace With field, enter the text that you want to replace it with.
   
5. Set any other additional options as outlined in the section above.
6. If you want to replace all instances of your text, click Replace All. You need to be sure that you have set up your options so that you will only replace the text that you want.
   
7. If you want to play it safe and just replace one instance at a time, click Find Next to find the next instance.
   
8. Click Replace to replace this instance, or to skip it, click Find Next again.
   
9. Repeat until you have replaced all the instances that you want to.

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 b*a 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.