---

Search posts under Excel:

---

Find and Replace in Excel

How to find & replace, select cells data, go to in MS Excel?

In previous post we learnt about Templates in MS Excel. In this post, we shall learn how to find, replace, select cells based on data, conditions, formulas etc.

To learn those, we will take an example of below data.

How to find data in MS Excel?

To find data in MS Excel, we can use following approach

Click on 'Find & Select' icon from the 'Editing' group on the Ribbon and select 'Find...'. 

This opens up Find and Replace dialog box as shown below. Alternatively, we can also press CTRL+F to open up Find and Replace dialog box.

Enter the data you want to find in your current sheet and click 'Find Next' button. If the data is found, it selects that particular cell otherwise displays a message of not found.

: By default Excel is case insensitive that means that it doesn't check whether the text is written in upper case or lower case.

In case, we need more control over how Excel finds the data we are looking for then click on 'Options >>' button and it shows like this.

Here, we can select

1. 'Within:' - to select data within the current spreadsheet or in the entire workbook.
2. 'Search:' - start searching record by rows or columns
3. 'Look in:' - whether to search in Formulas, Values or Comments (will learn about it in coming posts)

How to find data into a certain formatted cells only in Excel?

Sometimes, we want to find a specific text into a specific formatted cells only; like only in header or footer etc.

To do this, click on 'Format...' dropdown button and select 'Choose Format From Cell...'.

Notice the mouse cursor that changes like below.

Now click on the cell whose format you want to choose to find the data into.

See that the 'Preview' button changes to the chosen cell format. Now write the data to find into the Text box and click Find Next button.

The data to find will be searched only to those cells that satisfies the chosen format.

Quickies 

To clear the find format on the Find and Replace dialog box, click on the Format... dropdown again and then select last option (Clear Find Format).

How to find all occurence of data in Excel?

To find all occurence of data in Excel, we can use 'Find All' button.

All the matched cells references are listed at the bottom of the dialog box. Clicking on each occurence, selects that particular cells.

Quickies 

To hide the list of occurences, simply hold the bottom border of the dialog box with mouse and drag up. To show again, hold the bottom border of the dialog box again and drag down.

How to find case sensitive data or entire cell data only in Excel?

To find case sensitive data use 'Match case' checkbox. Checking this check box will find only those data that is exactly written in the 'Find what' textbox.

To match all the content of the cell (not partial content of the cell), check the 'Match entire cell contents' checkbox and then click on 'Find Next' button.

How to replace data in MS Excel?

There are three ways to open Replace dialog box.

1. Click on 'Replace...' of the 'Find & Select' dropdown from Editing group on the Ribbon.
2. Press CTRL+H.
3. Click on Replace tab.

You may have noticed that almost all options of the Find are available here and they work exactly the same way.

The only addition in the Replace dialog box is 'Replace with:' textbox, Replace All and Replace buttons.

Let's try to replace 'FALSE' to '0' using Replace dialog box. Fill the data as it is written in below dialog box.

Now we have two options to replace data

1. Replace All - to replace all occurences at once
2. Replace - to replace each occurences one by one

Quickies 

To close this dialog box, simply press ESC key on the keyboard or press Close button.

How to go to a specific cell in Excel?

'Go to' is used to go to a specific cell in the spreadsheet.

To open the Go to dialog box, choose 'Go To...' of the 'Find & Select' dropdown from Editing group on the Ribbon.

This opens up Go To dialog box that looks like below.

Write the Reference of the cell in the text box and click OK. You will be sent to that particular cell.

If the reference contains the range of cells, all those cells will be selected.

Clicking on Special button opens up 'Go To Special' dialog box.

Go To Special dialog box also helps us in finding a particular cells with specifics as shown in the picture below.

Try them yourself and learn. If any question, feel free to ask.

How to find all the cells having Formulas in Excel spreadsheet?

What if we want to select/view all the cells having Formula in it? 

Okay, we have a way to find them. Select Formulas from the Find & Select dropdown on the Ribbon as shown below (Do not worry about Find & Select icon here, when you decrease the size of the Excel widow, the big icon & description goes and small icon appears to accomodate all the commands on the Ribbon).

When you click on 'Formulas', you will see all the cells having formulas gets selected. Notice the picture below (PPF field formula in this case is '=E4*0.15').

Same applies to other commands (Comments, Conditional Formatting etc.) of the Find & Select dropdowns.