Search in Excel - find & replace in all opened Excel worksheets

Advanced Find & Replace for Microsoft^® Excel^®

Advanced search, find & replace in Excel values, formulas, text, comments and hyperlinks

For Microsoft Excel 2010 32-bit and 64-bit, Excel 2007, Excel 2003.

• How to find information in all opened workbooks
• How to work with the found items

Back to Excel Find & Replace homepage

Find information in all opened workbooks

Open Advanced Find & Replace by clicking on its icon or pressing the Ctrl+F (Ctrl+Alt+F) hotkey.

Tip: How to change or disable hot key.

By default Advanced Find and Replace is opened with the standard Ctrl+F hotkey. If you'd like to keep it for the standard Excel Find and Replace dialog box, you can use the Ctrl+Alt+F combination to start the add-in instead. When you open the Advanced Find and Replace pane, you can specify the hotkey combination you want to use, if any. You can find the Shortcut section in the bottom right corner of the pane.

1. Enter the information you want to search for in the Find what field on the Search in tab.
2. Decide if the search should be performed in Comments, Formulas, Values or Hyperlinks and check the corresponding option(s) in the Look in section. You can tick all of these options to search in values, formulas, comments and hyperlinks at the same time.
3. If you want to distinguish between uppercase and lowercase characters, select the Match case checkbox in the Match options section.
   So if you want to find the name "Robert" and exclude entries like "robert.t@domain.com", enter Robert into the Find what field, select the Match case check box and you'll see all entries with an uppercase first character.
4. Check the Entire cell checkbox to search for an exact and complete match of the characters specified in the Find what box.

   Tip: You can use wildcard characters to find an entry within a cell that has other information. Use the question mark "?" to substitute one character or the asterisk character "*" to substitute a string. E.g. you can find all cells that contain the word "Part" at the end by entering "*Part" into the Find what field. If you want to find entries with any character after the word "Part", like Part A, Part B, etc., search for "Part ?".

   Note! To use the asterisk "*" or the question mark "?" as wildcard characters for the search, the Entire cell checkbox must be selected.

5. In the Workbooks tree choose the workbooks and worksheets you want to search in. By default the search is performed in the current worksheet.

   Note! If you have more than 2 cells selected, then the search will be performed only in the selected range. To select all opened workbooks and worksheets click the Check all button at the top of the Search in tab.

6. Click the Find all button. The add-in will perform the search according to the parameters you set. It may take a few minutes if you search in several large workbooks.

Work with the found items

You will see the found items on the Search results tab.

Navigation in search results

• When you highlight an entry in the search results, the add-in navigates to its original location and selects it in your worksheet. You will immediately see where exactly the found data are located.
• You can sort search results by the Type or Value columns.

How to replace the found values

1. Perform the search first (see How to find information).
2. Enter the replacement characters in the Replace with field.
3. Click the Replace All button to replace all the found values. If you want to replace only some particular occurrences, you can select these search results by holding the Ctrl key. Then click the Replace button.

How to export search results

You can export search results to a new workbook for further work. To do this click on the Export all button at the bottom of the Search results tab.

Besides exporting all found entries, you can expand the export to rows or columns with the found values. You can also highlight some entries in the results and export them, or export the entire rows or columns that contain them. To do this click on the down arrow next to the Export all button and select the option that suits you from the drop-down list.

How to select the cells with the found values

By clicking the Select cells button at the bottom of the Search results tab you can select all the found items in your workbooks.

You can choose the option to select the rows or columns with the found entries instead. Or you can narrow the view down to the entries you highlight in the search results. To do this just click on the down arrow next to the Select cells button and select the necessary option from the drop-down list.

Download now and see how it can help you