Advanced Find & Replace Tips

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.

how to use find and replace in excel options button

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.

how to use find and replace in excel advance options

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.

how to use find and replace in excel search in workbook

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

how to use find and replace in excel to replace formulas

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

how to use find and replace in excel to find cell comments

  • Step-1: To do this, activate your find and replace option. Select “Comments” from “Look In” drop down.
  • 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.

how to use find and replace in excel to search with cell format

  • Step-1: Click on the down arrow on the format button.
  • Step-2: Select “Chose 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.

how to use find and replace in excel to find a cell with a specific format

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

how to use find and replace in excel to find a cell with a specific format select format from options

  • Step-2: You’ll get a format dialog box to select the format to find for. Select 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. These 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?

Try Fill Justify

 

  • ratanak

    nice tips

    • Puneet Gogia

      I am so glad you liked it.

  • Krupesh Daiya

    Nice tips. Well I had seen the advanced option but never knew how to use it. Thanks for the blog

    • Puneet Gogia

      Welcome Krupesh.

      I hope you’ll continue to use it.

  • Sudhir Kumar

    Awesome!!

    • Puneet Gogia

      Thanks for your words

  • VINOD GUPTA

    Nice