Find and Select 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

Ctrl + F

3. ‘Find and Replace’ dialog box will appear

Replace

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.

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.

Find and Replace

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

Ctrl + H

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

Find Next

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

selects the first

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

single replacement

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

Go To Special

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

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.

select Constants

More Related Articles For You

    Microsoft Excel Tutorial

Search

Shortcodes Ultimate

 
Follow Us
 
BGP