Advanced Find & Replace for Microsoft Excel

Start Find & Replace

To start your search, click the Find and Replace icon in the Search group on the Ablebits Tools tab:

Search in Excel workbooks

On the Advanced Find and Replace pane, configure your search options:

1. Enter the value you are going to search for.
2. Pick the Search within option:
   • Select Entire Sheet if you need to scan the whole document to find the value.
   • Tick Selection Only if your area of interest is just a certain range rather than the entire worksheet.
     Tip. You can specify the search option right away by clicking the down arrow below the add-in's icon and picking the necessary option from the drop-down menu:
     
3. Decide if the add-in should go through Values, Formulas, Comments, Hyperlinks, and/or Errors and check the needed option(s). You can tick all these boxes to perform search in all types of records at the same time.
4. The Match options section adds certain flexibility to defining the values to look for:
   • If you want to distinguish between uppercase and lowercase characters, select Match case. For example, to find the name "Robert" and exclude entries like "robert.t@domain.com", enter "Robert" into the field at the top, check the Match case box, and you'll see all entries that begin with an uppercase letter.
   • Check the Entire cell box to search for an exact and complete match of the characters specified in the top field. I.e. if you select this option with the value 'Robert', you'll find only those cells that don't contain any other values.
5. Tick the boxes near the workbooks and worksheets you want to scan.
6. Click the Options icon to select the shortcut you want to use to start the add-in:

When you are finished with the settings, click the Find All button.

Replace, select, or export the found entries

Now you can work with the search results:

1. If you want to replace the found entries, enter the new value into the Replace field.
2. Click the Replace all button to change all the found values in one go.
3. If you need to replace only certain found entries, select a single search result or pick multiple cells by holding the Ctrl key and use the Replace button to update only the highlighted records.
4. To immediately see where exactly the found data are located, highlight the entry of interest in the search results field to navigate to its original location and get it selected in your worksheet.

   You can sort the search results by the Value column and change the width of each column right in the add-in window.

5. Export the search results to a new workbook by clicking the Export all button.

   Take advantage of the additional options and choose the data to export. Click the arrow next to Export all and select one of the options from the drop-down list:
   

   • You can export only rows or only columns with the found values by choosing to Export rows with all found entries or Export columns with all found entries.
   • Export only certain entries by highlighting them and selecting to Export the highlighted entries in the drop-down list.
   • Export rows or columns with the highlighted entries by picking one of the last two options. Press the Ctrl key, select the entries in the add-in pane, and choose to export rows or columns that contain them.
6. By clicking the Select cells button, you can get all the found items highlighted in your worksheets.

   Besides, if you click the arrow next to Select cells, you'll be able to choose an option to highlight the rows or columns with the found entries:

   

   • Select only rows or only columns by choosing to Select rows with all found entries or Select columns with all found entries.
   • You can get only certain entries selected by picking them in the tree view and choosing to Select the highlighted entries.
   • Select rows or columns with the entries of interest by picking one of the last two options. Press the Ctrl key, highlight the entries in the add-in pane, and choose to select rows or columns that contain them.