How to Do a Search on an Excel Spreadsheet

How to Do a Search on an Excel Spreadsheet
Image Credit: Manuel Breva Colmeiro/Moment/GettyImages

Excel is a hugely useful tool when it comes to analyzing and synthesizing large amounts of information, but finding a single phrase or figure in a voluminous spreadsheet can feel like searching for a needle in a haystack. With so many columns and rows of data, locating a single piece of information manually isn't easy or practical, which is why Microsoft included several functions you can use to perform a search in Excel and turn up the piece of information you seek. You can approach the problem in a few ways, though, so it helps to learn about them all before deciding the best way forward.

Find and Replace in Excel

The simplest way to search a spreadsheet for a specific phrase or number is by using the Find window in the same way you would on Microsoft Word and many other Office programs. This option doesn't require any code and returns exactly what you're looking for – the cell locations of specific strings of text or numbers. The other options for finding specific information in a spreadsheet are more useful for finding something within a specific cell and require a bit more work to get exactly what you want.

Under the Home tab, look to the far right of the ribbon for Find & Select in the Editing group. Click the drop-down arrow and then choose Find or Replace from the displayed options. The Find option is only for finding specific bits of text and brings up the simplest version of the window. Type the text you're looking for into the Find what field and then click either Find Next to cycle through all instances of the word, number or phrase or Find All to list all the cell locations containing the search term.

Click Options>> to bring up more choices for exactly what you want the window to return. You can change the Within field to search the sheet or the whole workbook. The search can be organized by row or by column using the Search field, and you can choose to look in formulas, values or comments using the Look in field. You also have options for Match case, which returns only cells with matching cases, or for Match entire cell contents when you only want results in which the search term occupies the entire cell. You can use the Format option to find instances of specific formatting.

Replacing in Excel

The Find option locates the term you're looking for, but what if you want to change something in every instance it occurs? For example, if you've listed a client's name as Smith, but it's actually Smyth, you'll want to change every instance of Smith to Smyth. Use the Replace feature for this, which you select using either the drop-down menu under Find & Replace or by switching to the Replace tab from the window you brought up in the last step using the Find option.

This method works in exactly the same way as the Find section, except there is an extra field called Replace with where you enter the text or number you want in its place. In the example, you'd type Smith in the Find what field and Smyth in the Replace with field. All the other options are the same, so you can choose to find a specific term and reformat each of the cells containing it, for example, by using the Format option next to the Replace with field.

As in the Find section, you can click Replace or Replace all to either replace each instance in turn or replace them all at once.

Excel’s Search Function

There are specific Excel functions designed to handle different aspects of searching in Excel, with the Search function being one of the two most important. This function is designed to help you search a specific cell for a string of characters. Learning the basics of how to use it can be useful in many situations, especially when you have cells that contain many characters.

The Search function on Excel has three arguments in the format SEARCH(find_text, within_text, [start_num]) using Excel's terminology. The find_text section is where you input the text string you're looking for, and the within_text section is where you enter the reference for the cell containing the text you want to search. This returns a number, which tells you the character number where your search term starts or #VALUE! if it doesn't appear in the cell at all. You can use the final optional argument [_start_num_] to enter a character number, which starts the search at this character number and ignores everything before it.

For example, if you were searching for the ZIP code 98199 from a series of addresses to find people in the spreadsheet who lived in the specific location, you would type =Search("98199", [cell reference_]) with a reference to a specific cell in the [_cell reference_] spot, so _=Search("98199", A3), for example. From there, you could drag the formula down the column or across the row to check other cells, remembering that it will return #VALUE! if the term doesn't appear.

Excel’s Find Function

The Find function in Excel works a lot like the Search function, but it isn't as effective as using the Find & Replace option if you want to search the whole sheet. The arguments of the Find function are exactly the same as those of the Search function: find_text, within_text, [start_num]. So, you use it in the same way.

In the previous example, you would type =Find("98199",A3) to look where the ZIP code appears in cell A3, if it does at all. The outputs are also the same as for the Search function. You can type this formula into one cell and then drag it to copy the formula but change any cell references accordingly, which allows you to search a spreadsheet or a column or row within it for a specific term.

Differences Between Search and Find

You might be wondering why Search and Find are separate Excel functions if they have the same arguments and output the same basic information. There are two key differences between these two functions, and learning these will help you determine the best one to use in each situation.

The Search function isn't case-sensitive, but Find is. If you're searching for green, for example, the Find function won't return a value for cells that contain Green, but the Search function will. The other major difference between the two is that Search allows you to use wildcard characters in your search string, which can be useful in many situations, but Find doesn't.

Searching With Wildcard Characters

When you want to search for the start and end of a specific term but want to return all results regardless of what's in the middle of the search term, you can make use of the wildcard characters ? and ***** with the Search function or the Find & Replace dialogue box.

The ? character placed in the middle of a search term (the find_text argument) tells Excel to include the specific term with any individual character in the space where the question mark is. For instance, if you search s?t, you get sat or set as results, but not spent, because the latter option has multiple characters between the s and t.

The **** character works in basically the same way as the question mark, except it can be any number of characters in the space it occupies. For example, if you search _st,_ results may include sat, sit, spit, spot, spent and any other combination with the s and t at the start and end of it. Using either wildcard character in your search delivers all the results you're looking for without missing anything that might be relevant, but be careful how you use it because it can also lead to extraneous results.

Searching for Wildcard Characters

So what do you do when you want to search for a question mark? How would you stop Excel from treating it as a wildcard character? This can be achieved by typing a tilde ~ in the field before the character you're searching for. For example, if you wanted to search for a question mark in cell A2, you would type =Search("~?",A2) into an empty cell.

Searching for Multiple Possible Terms

When you're looking for one of many terms in a specific cell, the methods described so far don't work unless you create a separate search for every single term. However, using a formula, it is possible to search for multiple terms at once, although this method takes a while to set up properly. This method involves the Search function working in combination with the IsNumber function and SumProduct.

Although this isn't required, creating a named range for the list of things you want to test simplifies the process. Do this by entering each of the search terms in neighboring cells, highlighting all the cells, and then giving them a name by typing the name in the top left field just to the left of the formula bar. This allows you to refer to the entire list by its given name. However, you can also include all the search terms without creating a named range by separating them with commas inside { } brackets.

To test a cell, use the formula:

=SUMPRODUCT(--ISNUMBER(SEARCH([named range],[cell])))>0

where [named range] is replaced with the name of the range or the { } brackets containing each separate term and [cell] is replaced by the reference for the cell you want to test.

The formula returns a TRUE value if the cell in question contains one of the terms or FALSE if it doesn't. It can be dragged down to check other cells because the cell reference in the formula will update automatically.

How Should I Search in Excel?

There are many ways you can search in Excel, so which one should you use? The answer very much depends on your purpose. If you're looking through a big list of cells for one specific term – and in most situations in general – the Find & Replace box is the easiest and most effective way to get your result. However, if you want to find a specific term in a series of long cells, the Search function is likely to be the best option, because it isn't case-sensitive, and it allows you to use wildcard characters.

If you're looking for one of many things in specific cells, the formula in the previous section is the most efficient way to complete your task.

references