Now that you have basic searching down, let’s take a look at some of the more advanced options you have for finding data.
Click the Options >> button in the Find and Replace dialog. You’ll see a wide variety of new choices. Here’s what they do:
- Format… lets you narrow your search to specific cell formats (we’ll see shortly how useful this can be).
- Within: allows you to choose to search the entire workbook, instead of a single sheet.
- Search: sets the search to run by row or by column, changing the order in which you’ll see your search results—this can be useful if you have a massive spreadsheet and want to the search to run left-to-right instead of up-to-down.
- Look in: tells Excel where to look for your search query (we’ll talk about this in a moment).
- Match case makes the search case-sensitive.
- Match entire cell contents tells Excel to only return cells that match your search query exactly, and don’t contain anything else.
Several of these options are self-explanatory, but we’ll take a look at some of the more esoteric options.
First, let’s see how the Look in menu changes our search. Click over to the second sheet in the example workbook, where you’ll see names, vehicle makes and models, and the base price, discount, and sale price of each vehicle.
It’s important to notice that the sale price is determined by a formula (in this case, it’s the base price multiplied by 1 minus the discount). That will become important. Take a look at the first number in the sale price column:
Let’s run a standard search for the number “26,229” and see what happens.
Excel doesn’t find anything because the default search looks at formulas. Because “26,229” isn’t in the formula (which is =C2*(1-D1)), we don’t get a result. Let’s change the search scope to Values and run it again.
The search works perfectly now. When you search for values, you’re searching what’s displayed in the cell, and not the formula that creates it.
So if you have a spreadsheet with a lot of formulas, this is a good thing to remember.