Find and Replace in Excel

Excel is one of the most widely used spreadsheet software tools developed by Microsoft. It is used practically globally by businesses and working professionals to store, manipulate, and gauge consumer or financial data efficiently.

It makes conducting analysis and decision making more structured and well informed by allowing us to manipulate the data to yield what we wish to see.

Excel allows us to interpret data visually and intuitively through charting and graphing. These enhancements improve Excel's usability and make it more streamlined. Excel also aids in structuring and organizing our data.

Microsoft Excel has a plethora of functions, from performing simple tasks to computing complex mathematical operations to draw necessary insights, making Excel a highly time-saving tool. Conducting these calculations manually would take almost forever and be highly inefficient.

Examples where excel is used:

• Human Resource (HR) department for storing large amounts of data on employees, their bio data, salaries, joining date, compensation/health plans, benefits, etc.
• Creating and maintaining balance sheets of companies for financial modeling.
• Investment Risk Management to analyze the risk associated with investing in a project.

On September 30, 1985, Microsoft Excel launched its beta version. Since then, Microsoft has launched new versions of Excel from time to time adding new features and functions, making Excel a more user-friendly, tactful, and industry-friendly software.

In this article, we shall discover several brand-new Excel features that might be really useful to consumers when using Excel. Today, we shall discuss the Find and Replace functions in Microsoft Excel.

Find and Replace Functions in Excel

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 in 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 on the top of the Excel worksheet. Go to Find & Select, and click the Find option. It opens the same dialog box shown above. 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 when doing financial modeling in Excel. We generally use the Find function in conjunction with the Replace function, as it can quickly edit the values or formulas in many cells at once.

Let us take an example to understand the usage and applications of these functions. Suppose we have hundreds or thousands of cells. We 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 having an average and replace them 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.

Researched and authored by Devang Shekhar | LinkedIn

Reviewed and Edited by Shahrukh Azim Butt | LinkedIn 

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