Find and Select in Excel: Replace and Go to Special Feature in Excel

Find and Select in Excel

MS Excel provides you the flexibility to use “Find and Replace” features to find specified text and replace it with your desired text. It also provides you the feature of “Go To” to quickly select all cells with comments, formulas, conditional formatting, data validation, constants etc. In this tutorial, you will learn how to use these features.

Find

To find the occurrence of any value within the sheet, follow the steps below.

1. On the Home tab, under editing section, click on Find & Select

2. Select Find from the drop down menu or press Ctrl + F as a keyboard shortcut

3. ‘Find and Replace’ dialog box will appear

4. Type the text you want to search. This example used Ferrari as search word.

5. Click ‘Find Next’

MS Excel will search for the word on active sheet and will select the first occurrence.

You can keep on finding more occurrences by pressing Find Next.

MS Excel also supports a feature called Find All, that collect all the occurrences and display them below the Find and Replace dialog box as shown in the figure below.

Replace

Replace feature is equally important as of Find feature in MS Excel. To replace a text in MS Excel, follow steps below

1. On the Home tab, under Editing section, click Find & Select

2. Select Replace from the drop down list or press Ctrl + H as a keyboard shortcut

This will open a ‘Find and Replace’ dialog box (with the Replace tab selected).

3. Type the text you want to find (i.e. Veneno) and replace it with the desired text. (i.e. Diablo).

4. Click ‘Find Next’.

MS Excel selects the first occurrence but will not replace anything yet.

5. Now, click ‘Replace’ to make a single replacement in the selected cell.

You can use a ‘Replace All’ feature to replace all occurrences within the sheet.

Go To Special

If you want to quickly select all cells with formulas, conditional formatting, comments, data validation, constants, etc., you can do it with Go To Special feature in MS Excel. For example, if you want to select all cells with formulas, follow the steps below.

1. Select a single cell.

2. On the Home tab, under Editing section, click Find & Select

3. Select Go To Special from the drop down list

4. Select Formulas from the Go To Special Dialog box and click OK.

Note: you can further refine your search for cells with formulas by defining the type of value it returns i.e. Numbers, Text, Logicals (TRUE and FALSE) and Errors. These check boxes are also available if you select Constants.

Find and Select in Excel

Find and Select in Excel

More Related Articles For You

Microsoft Excel Tutorial