Advanced settings of Find and Replace
Now that you know all the basics of Find and Replace, let’s look at some advanced features it has to offer.
To open the advanced functions of the Find feature, click the Options button in the dialog box.
It displays a wide variety of options to choose from.
Note that the list of options for the Find and Replace tab is the same. The only difference is that in Replace tab, you get an additional format option for the replace box.
Let’s study all these options in detail below.
Format: The format option lets you specify the format of the search item. Click the Format option at the top to select a format.
You can also choose the format for your text string from a particular cell. For that,
- Click on the drop-down arrow next to Format.
- Delete the existing format by clicking “Clear Find Format.”
- Click “Choose Format From Cell”
The cursor appears with a dropper.
Just click on the cell whose format you want to copy, and click Replace all. The dialog box also shows a *Preview*.
Within: The within option lets you search a specific text string throughout the entire workbook 😵
So even if you don’t remember where a certain piece of text is in your workbook, you can simply search for it. This feature is actually very helpful when you have to search for a single term from extensive data.
Match Case: The match case option enables case sensitivity. This means that Excel will search for a specific term with the exact capitalization used.
Say we search “hr” in lowercase instead of uppercase and put a checkmark on the ‘Match Case’ option.
Press Find Next.
Excel will show a warning message because we didn’t capitalize the position properly.
If we write the same search term in uppercase, Excel searches and shows the result as:
Isn’t that amazing? 🤩
Search: The search option lets you select the order in which you want to search.
If you want to look for the search item row by row, select “by row”. And if you want to search column by column, select the “by column” option.
Match Entire Cell Contents: This option restricts Find and Replace to look only for characters you have entered in the box. If you even miss a period, Excel will show a warning message.
Say, we put a checkmark on the ‘Match entire cell contents‘ option. And type in the name “John,” whereas, in the data set, the correct name is “John O.”
This is what will happen:
And if we add “O.” to the same box, the result becomes:
Excel returns the correct result. This tool is great if your spreadsheet contains a large amount of data.
Look-in: The look-in option lets you specify the type of data of your search item.
The Find tab offers three types, namely, Formulas, Values, and comments. The Replace tab, on the other hand, uses only one type of data, i.e., Formulas.
If you only want to look for formulas, you can find them using the Go To Special option from Find & Select.
It offers a plethora of options and you can set the options as per your choice. You can use it to find other items too.
Just select the options and Excel will automatically apply them on your next search.