Find and Replace in Excel

What Is Find and Replace Functions in Excel?

The find and Replace functions are an Excel feature that permits users to search for specific data in a worksheet or workbook and replace it with another piece of data.

If you have a large amount of data to store and analyze, it would require a large number of cells in an Excel worksheet. Now, if, after filling in all the data, we need to make modifications to the value entered in a particular cell, it would be difficult and time-consuming to find the cell manually.

The Find function in Excel can make our job easy in such cases. This function helps us to quickly search all the cells and formulas in our worksheet for all the instances that match our search criteria.

We can access the Find function in Excel in two ways:

1. Press Ctrl + F on your keyboard. Doing this would open a dialog box as shown below: We can type our search criteria or what we intend to find in the blank space provided and click on Find All. It shows all the cell numbers and the corresponding cell values matching our search criteria as a list.
2. Click on the Home tab at the top of the Excel worksheet. Go to Find & Select and click the Find option. The dialog box shown above opens. We can follow the above steps to find a particular cell.

These are the two ways by which we can access the Find function in Excel to search for any particular cell in our worksheet. The Find function helps in saving a lot of time, especially when dealing with large and complex datasets.

Some examples of what we can search for using the Find function in Excel are as follows:

• All cells that contain the number 80
• All cells that contain the name “John”
• All formulas that contain a reference to cell C4
• All cells having the SUM function, or any other function

Suppose we have entered a particular formula or text in a cell in our Excel worksheet. After we have entered all the data, we realize that we need to change the formula or the text in that cell, as it is incorrect, and write the correct formula in its place.

In such cases, the Replace function in Excel is quite handy. The Replace function helps us edit many cells or formulas at once, hence saving a lot of time. This function makes working in Excel more convenient and efficient.

The Find function has many applications in financial modeling in Excel. We generally use it in conjunction with the Replace function, as it can quickly edit the values or formulas in many cells at once.

Let's consider an example to understand the usage and applications of these functions. Suppose we have hundreds or thousands of cells and want to change a particular formula entered in many cells.

In such cases, using the Find and Replace functions together makes our job very easy. It helps save time and effort that would have been otherwise wasted in editing each cell having that formula. The Find and Replace function guarantees that we do not miss any cell, which we might do when replacing cell formulas manually.

Let us now see a few practical examples demonstrating the practical use of the Find and Replace function in Excel.

Examples of the Find and Replace Functions in Excel

We have already seen the theoretical use and applications of the Find and Replace functions in Excel. Let us now see how to use these functions to ease our tasks while working in Excel.

Suppose we have hypothetical data of the marks of five students in Mathematics, Science, and Economics. We have calculated the average in all three subjects as shown below:

Suppose we want to calculate the median marks instead of the average marks for all three subjects. One way to do this is to modify the formula in each cell individually and use the MEDIAN function to obtain the median for the three subjects.

Here, we can modify each cell with an average and replace it with the MEDIAN function, as there are only three subjects. But what if there are 10 or 12 or even more subjects? In this case, modifying each cell would be highly inconvenient and time-consuming.

In such cases, we can use the Find and Replace functions in conjunction. We first need to find all the occurrences of Average in the cells in our Excel worksheet.

1. To do so, press Ctrl + F, which opens the Find and Replace dialog box, as discussed above. Type Average in the Find What section provided and click on Find All.

2. Doing this shows the list of all the cells containing the term Average or the formula AVERAGE() and their corresponding cell values as shown above. To replace all instances of average with median in the worksheet, click the Replace option.

3. Doing this opens a dialog box shown above. In the space provided for Replace With, we write the text or formula with which we want to replace the existing text or formula. Since we want to replace the average marks with median marks in the above example, write Median in the Replace With space provided. After this, click on the Replace All option.

Doing this replaces the AVERAGE formulas with the MEDIAN formulas in all three cells and displays a message as shown below:

In this way, we can use Excel's Find and Replace functions to search for a particular cell and modify its value. Doing this helps us save time and makes it more convenient to work in Excel.

 What Are The Uses Of Excel Find And Replace Function?

The Find and Replace function in Excel can be quite helpful to users. Below are some of its uses.

1. Locating: The Find function enhances the users' ability to locate text, numbers, and special characters in a worksheet or a workbook. The Find function aids in searching and locating occurrences of a particular value.
2. Replacing & Modifying: The Replace function permits the users to find a specific value and replace it with another value. This allows the user to make changes in the workbook without any limitations.
3. Updating: The Find and Replace function can be utilized to update the cell references in formulas. This can be time-saving when you need to change a reference across multiple cells.
4. Correcting Spelling: Using the find-and-replace function can also help a great deal in identifying and fixing spelling mistakes across the worksheet or workbook. This use can also be extended to formatting the data across the worksheet.
5. Data Cleanup And Standardization: By utilizing the find and replace function, we can quickly change or normalize data. For example, we can use these function when we are in need to replace abbreviations with full words.
6. Searching: The find and replace function allows the users to search in the formulas, comments, and other cell contents. This can be achieved by utilizing the advanced functions to search not just the cell values but also underlying formulas and comments.
7. Selective Replacements: The Find and Replace functions cater to options to control and direct the scope of search and replacement. 

Free Resources

To continue learning and advancing your career, check out these additional helpful WSO resources:

• How to Reduce Excel File Size
• How to Repeat Previous Command in Excel?
• HLOOKUP Function
• IF Statement Between Two Numbers
• IFS Function