How to Find and Replace in Excel

Sometimes, when we are working on a lot of projects then it is hard to find some specific data in a worksheet. Scanning the whole document which has hundreds of rows and columns is an impossible thing to do.

Here, with this find and replace super powerful and handy feature we can find any number, string, or special character if you know how to use it. Moreover, replace function allows you to change any word or value with others.

In this tutorial, we’ll learn or understand the working of the find and replace function.

Shortcut to Open Find and Replace

To quickly open the find and replace option you can use the following keyboard shortcuts.

• Find: Ctrl+ F
• Replace: Ctrl+ H

Use the Find Option in Excel

Let’s take a simple example to understand how the find option works.

1. First, go to the Home tab and then from the Editing group. Click on the find and select drop-down and select the “Find” option.
   
2. Once you click on it, it will open the “Find and Replace” dialog box on the window.
3. Now, you can type the value in the “Find What” input box that you want to find in your worksheet.
4. After that, press the “Enter” button and you’ll notice the cell with the same value will be highlighted.
   

When you click on the “Find All” button and this will provide you with a list of cells wherever that value or word is located. While using the “Find next” button, Excel selects the other occurrence cell of the search value in the sheet, and so on.

Use Replace option in Excel

Now let’s see how we can replace the particular value or data with another from the worksheet. Here are the following steps to do:

1. First of all, you can open the “Replace” dialog box by using the shortcut keys i.e., Ctrl+ H.
   
2. There will be two dialog boxes that are “Find what” and “Replace with”. In this, you can fill the value or text that you want to find and replace it with.
3. Now, click on the “Replace” button and you will look at each value that is replaced one by one. (It works like the “Find next” option.)
   
4. Moreover, the “Replace all” button will help you to save time and replace the whole data in a single click.
   

Find and Replace with More Options

There are some advanced options in the find and replace dialog box that will help you in the customization of data as per your needs in the spreadsheet. Let’s get discuss this in detail:

1. First, use the Ctrl+ F shortcut keys to open the find dialog box. Then, click on the “Options” button within the dialog box.
2. Once you click on it, you can find different parameters with various specific conditions.
3. The first one is “Within”. When you click on the drop-down of it, it will give you choice of searching the value within the sheet or in the entire workbook.
   
4. Next, the “Search box” will provide you with a facility for finding the data within rows and columns separately.
5. Whereas, in case of “Look in” option you can directly find a cell which has a specific formula, values, and comments.
   

Match Case: You can tick mark this option when you want to search for a value along with the exact match case. For Example: When you have a value that is in a capital case and you want to search for values with a capital case only.

Match Entire Cell Content: When you click on this option, it will help you to highlight the exactly same match that you have added in your search. Moreover, in this case, it is not possible to find the same value with any symbol or character.