Tuesday, October 18, 2005

Using the Find and Replace feature of Excel

Excel has some powerful Find and Replace features. Using them correctly in order to achieve the desired result is not always easy however.

Clicking Edit | Find on the menu or CTRL+F will open the Find Dialog Box. Ctrl+H gets you the replace one. These are actually two different tabs in the same dialog box.

In both cases you are presented with a search box and Replace also gives you a replace box. There is also a button called Options which is most useful. Here you can extend your find and replace options to include formats, both to find and to replace.

The remainder of the advanced options are also little used by many people but give you some very powerful control over the results.

  • Within Sheet or Workbook, In versions earlier than Office 2003 this wasn't available and by default it is Within Sheet. Changing to Within Workbook will search through the entire workbook.

  • Search By Rows or By Columns. Unless you have a very large set of data or a very slow computer changing this option won't affect the speed much. However it can make a difference to the direction of the search if you want to find results in a certain order.

  • Look in Formulas, Values or Comments. This option is only available to find. Replace looks in formuals only. Changing this option allows you to control where you search. Looking for 45 in formulas will find any cell with 45 in it, but also any cells that reference row 45 or any other row with 45 in it eg. row 457. Conversely it will not find a cell that has a formula =3+42 in it. Looking in Values will find this cell as well as cells with 45 in them and exclude the cells with row references of 45. Searching commments also is a good way to find text within comments that are other wise hidden.

  • Match Case and Match Entire Cell Contents help eliminate partial results. Eg searching for 45 will not return 450 if Match Entire Cell Contents is selected.


    • Using the Find All box generates a list of all the results and allows you to select them changing the background focus in the worksheet to the selected cell. (Available in Office 2003)

    Using the Replace All can be dangerous. I tend to select the cells that I want to work with first. If you are trying to change data inside just one cell then select it and a blank cell. Otherwise Replace All will operate on the entire worksheet.


    Posted by: Jethro | 1:13 PM |