How to use Advanced Options in Find & Replace in Excel

 
Everyone know that we can find and replace a text with find and replace option. But very few people know that you can use this option to an advance level.
Do you?
If not, then after reading this post you’ll love this tool. In this post, you will learn some useful examples to use find and replace option to an advance level.
So let’s get started.
 
First of all,  you have to activate advanced options in find and replace. Click on the “Options” button to activate.

With advanced options, you can find values using different parameters. You can specify conditions like searching for specific formulas, cells with specific colors, bold texts, etc.

Here are some useful examples for searching some specific values:

1. Value In Entire Workbook
2. Cell Having a Specific Formula
3. Values from Cell Comments
4. Copy Format From A Cell To Find Another Cell
5. Having Specific Format

Find Value In Entire Workbook

You can find a value from your entire workbook rather than from a single worksheet.
And, you can also replace that value with some other specific value.

• Step-1: To find a value in an entire workbook, select workbook from “Within” drop down option.
• Step-2: Enter search value in “Find what” to search & enter replace value in “Replace with” to replace.

Find Cell Having a Specific Formula

This is also a useful option to find a cell which has a specific formula and replace that formula with some other specific formula.

• Step-1: To find a cell with a specific formula, activate your find and replace option. And, select formula option from “Look in” drop down.
• Step-2: Now, enter the formula in “Find what” to search for formula and enter the formula in “replace with” you want to replace.

Find Specific Value From Cell Comment

This one is my favorite. You can find the cell in which you have a cell comment with a specific value. This will help you in two ways, first, it will search for the cells which have the cell comment and then it will search a specific value in that comment.

• Step-1: To do this, activate your find and replace option. Select “Comments” from “Look In” dropdown.
• Step-2: Add the value in “Find What” you want to search for.
• Step-3: Click find or find all.

Copy Format From A Cell To Find Another Cell

Here we have another useful option to find a cell on the basis of value and format of a specific cell.
And, after that, you can also change that value and format.

• Step-1: Click on the down arrow on the format button.
• Step-2: Select “Choose Format From Cell”. You will get a selection tool to select the cell which you want to use as a base for your search.

You can also select a cell to take as a base for replacement.

Find Cell Having Specific Format

You can also use the custom format to search and replace cell values and formats.

• Step-1: Activate your find and replace option, Click on the format button.

• Step-2: You’ll get a format dialog box to select the format to find for. Select the desired format to search for.

Using this option you will able to make any type of search basis on the format.
You can search for:

1. Cell Borders
2. Font Colors
3. Background Color
4. Number Format
5. And, much more…

Things to Remember

1. There is no option to replace the value in cell comment. 🙁
2. While searching for a format, you can only search for the cells which exactly have the same format.
3. And for text, you have the option to match case.
4. You can also match exact cell content for your search.

Conclusion

Using find and replace this way can save your lot of time. This can give you an advanced way to search for the values. And the best part is you, you can replace those values.
I hope you like it. Do you have any unique idea to use this option? Please share your view in the comment box, I would love to hear from you.

What’s Next?

• Fill Justify