How to Find and Replace in Excel

In this tutorial, you will learn how to use Find and Replace in Excel to find specific data in the sheet or workbook in Excel and what you want to do with this data. You will also learn about the advanced features of Excel search such as finding specific formatted cells and formulas or particular formatting by using the find and replace Excel method.

When you work on a big data spreadsheet it is very hard to find specific data in that big data. You have to analyze hundreds of columns and rows in the spreadsheet in Excel. This is not a good practice to do. Instead, you use the Find and Replace in Excel.

Find the value in the data of the worksheet

The following steps will tell you how to specific values, numbers, text, or dates in the workbook, sheet, or a range of cells.

1. First of all, select the range cell in the data you want to find specific data or you want to search from the whole worksheet then click on any cell on the sheet.
2. To open the Find and Replace dialog box go to the home tab in the ribbon and look for the option of Find & Select in the Editing group and click on that option or simply use the shortcut key ctrl + F.

3. The Excel Find and Replace dialog box pops up.

4. In the Find What: box, type the value or character you want to search then click the Find All or Find Next button.
5. When you click the Find Next button, excel Find and Replace select the first occurrence in the sheet the second time click on the button Find Next occurrence if present in the data.

6. But when you click the button to the Find All it then gives the list of all the similar values.As you see in the Excel.

An additional option in the Excel Find 

To become more specific in your Find search, click the options button on the right bottom side of the dialog box.

1. To search in the specific sheet use choose from the Within option.

2. To search in data column by column or row by row like the right to left and top to bottom you use the Search option.

3. To search for specific data you also use the Look in option to select formulas, values, and Text data.

You can select the column and limit your search to very specific. This type of method can be very useful to find data.

Find data in a cell with a specific format in Excel 

To find a cell with unique formatting, press the Ctrl + F to open the find and replace window. Now click the option button then click the format button present in the upper right corner and then the Find Format dialog box opens.

If you want to find those cells that have the same format as other cells on your worksheet. Now remove Find what option criteria. Now click the Format button the dropdown appears now select the Choose Format From Cell option from the dropdown.

By default, MS Excel saves the option of the formatted cell that you specify.

Specific search with formulas in Excel

In Find and Replace you find data in formulas for a given value. To find a cell you have to choose the option Go To Special or you can press F5 to open the Go To Special dialog box and click the special button in the lower right corner of the dialog box.

Select the formula category in the Go To Special dialog box tick all the formula results you want to search and then click the ok button.

1. Numbers: Those formulas that return numeric values and also include dates.
2. Logical: Those formulas that return Boolean values of the True and False.
3. Error: Those formulas return results in errors like #N/A, #NUM, etc.
4. Text:

If selected the options find any cell that meets your defined criteria then those cells are automatically highlighted. Otherwise, no such cells have been found message displayed.

Use of Replace in Excel

Here you will learn about how to use Replace in Excel. In this tool, you can replace value from one value with the other selected range of cells or range of cells, entire worksheet, or workbook.

Replace value with the other value 

In the Replace tab of Excel Find and Replace dialog box you can replace certain characters, text, or numbers in the Excel sheet. The following step will tell you how to use the replace method in Excel.

1. Select the cell where you to replace the number or text. Click the cell on the active cell to replace the character.
2. Click the Ctrl + H shortcut to open the replace tab dialog box.

3. Alternatively, go to the Home tab click the Find & select option in the Editing groups, and open the Replace option.
4. The value you want to find is put in the Find What section and put the value in the Replace with section to replace the value.

5. Click the Replace All button on the dialog box.

You can also see the Format Cells in the tutorial.

In the next tutorial, we will learn about the templates in Excel.