Find and Replace in Excel is a fairly powerful and convenient tool that allows you to find, and, if necessary, replace information on a worksheet. As part of this lesson, you will learn how to search in a given area of an Excel document, as well as change the information found to the desired value.
When working with a large amount of data in Excel, it is sometimes difficult to find any specific information. And, as a rule, such a search takes a very long time. Excel offers a great search tool. You can easily find any information you need in an Excel workbook using the Find command, which also allows you to change the data using the Find and Replace tool.
Finding Data in Excel Cells
In our example, we will use the Find command to find the desired name in a long list of employees.
If you select one cell before using the Find command, Excel will search the entire worksheet. And if the range of cells, then only within this range
- On the Home tab, use the Find and Select command, and then select Find from the drop-down list.
- The Find and Replace dialog box appears. Enter the data to be searched. In our example, we will enter the employee’s name.
- Click Find Next. If data is present on the sheet, it will be highlighted.
- If you click the Find Next button again, you will see the next search option. You can also select Find All to see all the options that Excel has found for you.
- When you’re done searching, use the Close button to exit the Find and Replace dialog box.
You can access the Find command with the keyboard shortcut Ctrl+F.
To see additional Find and Replace options, click the Options button in the Find and Replace dialog box.
Replacing cell contents in Excel
There are times when a mistake is made that is repeated throughout the Excel workbook. For example, someone’s name is misspelled, or a certain word or phrase needs to be changed to another. You can use the Find and Replace tool to quickly make corrections. In our example, we’ll use the Replace command to fix a list of email addresses.
- On the Home tab, click Find and Select, and then select Replace from the drop-down list.
- The Find and Replace dialog box appears. Enter the text you are looking for in the Find field.
- Type the text you want to replace the found text with in the Replace with box. And then click Find Next.
- If a value is found, the cell containing it will be highlighted.
- Look at the text and make sure you agree to replace it.
- If you agree, then select one of the replacement options:
- Replace: Corrects one value at a time.
- Replace All: Corrects all variants of the searched text in the workbook. In our example, we will use this option to save time.
- A dialog box will appear confirming the number of substitutions to be made. Click OK to continue.
- The contents of the cells will be replaced.
- When finished, click Close to exit the Find and Replace dialog box.
