How To Use The Find And Replace Search Feature In Excel

Microsoft Excel is a versatile tool that you can use for a wide range of tasks. Managing data and billing information, keeping financial records, you name it. However, you’ll have to learn how to use formulas, manage worksheets, and more for all that.

But the find and replace feature of Microsoft Excel is useful for anyone, regardless of their expertise. This article will demonstrate how to find and replace values in Excel, along with a brief overview of the additional features it has to offer.

How to Find a Value in Excel
When you’re working with a spreadsheet, sometimes you have to search for a particular cell value. Sure, if it’s a small sheet, you can run your eyes along the screen to find the cell. But this task can quickly become cumbersome when there are thousands of rows and columns to process.

Don’t worry. The find feature of Microsoft Excel has got you covered. To search for a particular cell value:

1. First, select the range of cells that you want to search within. If you want to search the whole spreadsheet, simply click on a random cell.
2. Head over to Home > Find & Select > Find. Alternatively, you can also use the Ctrl + F key combination.
3. Next to the Find what label, enter the value that you want to search for.
4. Excel will highlight the first occurrence of the value in the spreadsheet. Click on the Find Next button to move on to the second cell result.

You can list down every occurrence of the search term by clicking the Find All option. By clicking on an entry, you will be taken to the corresponding cell.

Use Wildcard Characters to Find Cells
Excel also allows you to use wildcard characters while searching for values in a spreadsheet.

1. Asterisk (*): Matches a string of characters. Ab* will match Abbott, Abandon, Absolutely, etc.
2. Question Mark (?): Matches a single character. Ab? will match Abc, Abd, Abz, etc.

For example, to search for values starting with J in a spreadsheet, type “J*” in the Find what field.

Find Cells With a Specific Formatting
Excel also allows you to search for the formatting of a cell instead of its value. To do that, press Ctrl + F to bring up the Find and Replace dialog box. Then, click on the Format option located on the right side.

Select the formatting that you want to search for. For example, the formatting selections in the image below will search for cells with a green background.

Click on OK. Then, select the Find Next or Find All option. Excel will list down all the cells that have the same formatting style you have specified.

Find Cells With Formulas
Similarly, you can also search for cells that are using formulas. Head over to Home > Find & Select and then click on the Go To Special option.

Excel will display a list of options with checkboxes. Check Formulas and select the sub-items that meet your needs. The Numbers options highlights formulas that return numbers, Text displays formulas that return text value, and so on.

Once done, click on OK, and Excel will highlight all the cells with formulas that match the criteria.

How to Replace Values in Excel
After searching for the cells you want, Excel’s replace feature will help you change the value of the cells. Luckily, replacing hundreds of values is only a matter of few clicks with Excel.

To find and replace cell values in Microsoft Excel:

1. Click on Home > Find & Select > Replace.
2. Enter the text you want to search for in the field next to the Find what label.
3. Type the value with which you want to replace the cells in the Replace with field. For example, let’s search for names starting with the letter J and replace them with the word “Jade”.
4. Excel will highlight the first cell that matches the search criteria. You can click on Replace to replace its value.
5. Then, click on Find Next to move on to the next cell, and select Replace again to change the cell value.
6. You can also click on Find All and Replace All to change the values at once. Excel will notify you about the success of the process by displaying a prompt.

Additional Options
Apart from the basic find and replace, Excel also offers some extra options that you can use while working with a spreadsheet.

Search the Entire Workbook
By default, Excel only searches the current spreadsheet for the search term. To make sure that you get search results from the whole workbook:

1. In the Find and Replace dialog box, click on Options.
2. Next to the Within label, click on the dropdown menu and select Workbook instead of Sheet.
3. Now, when you click on the Find All option, Excel will display cell results from the whole workbook instead of a single sheet.

You can also change the default search flow by changing the value next to the Search label from By Rows to By Columns.

Match Case While Searching
To perform a case-sensitive search in Excel, head over to the Find and Replace window and click on the Options button.

Now, check the box next to the label Match case to enable a case-sensitive search.

Change Formatting of the Values
While Excel allows you to find and replace the values of cells, you can also easily change the formatting of cells. To start:

1. Click on Home > Find & Select and then select Replace from the dropdown menu.
2. Enter the value of the cell that you want to search for in the Find what field.
3. Click the Format option next to the Replace with label.
4. Change the formatting style and background color according to your needs.
5. Click on Find All to search for cells that match your search term.
6. To change the formatting of the cells, click on the Replace or Replace All option.
7. Excel will apply the formatting style to the highlighted cells automatically.

Excel Makes Your Work Easier
Excel provides you with numerous features to manage and organize your spreadsheets, which makes it one of the best spreadsheet applications in the market.

When you’re starting out with Microsoft Excel, the unlimited number of rows and columns with a complicated taskbar can seem daunting at first. But with time, and a significant number of tutorials later, you will find yourself comfortable with the environment and its workflow.