Updated August 22, 2023
Excel Find and Replace (Table of Contents)
Introduction to Find and Replace in Excel
Find and replace is an option available with Excel spreadsheets. This will help you easily find a particular text or number within the spreadsheet. The replace option and find will provide a better way to replace specific data already found using the find option. Find can be used alone, but to replace data, you should use the find option first and then only replace it.
Find and replace comes under the editing option of the Home menu. Both options can be used separately according to your necessity. Manual scanning is not an effective method to find something in a spreadsheet with hundreds of rows and columns. Also, it is time-consuming.
Examples of Find and Replace in Excel
Let’s understand how to Find and Replace Data in Excel with some examples.
Example #1 – Finding a Word within Excel
Let’s see how you can use find and replace options in this example. Some company details with the address in the spreadsheet are given below.
- Open the spreadsheet where you want to find the word. The next step is to open the find option using the keyboard shortcut Ctrl+F.
- This will open a box where you can give the word that you want to find. After typing or pasting the word in the ‘Find What:’ area, Click on the ‘Find All’ button.
- This will give you a list of places where the particular word is located. The first will be automatically selected, and the cell will be selected.
- By clicking the ‘Find Next button, the cell selection will be moved to the second location, which is listed in the window.
- The ‘Find Next’ button will move the selection to the next corresponding locations. Until the list completes the search, it will change and show the different locations by selecting the corresponding cell. Once the list is completed, it moves to the first listed location again.
Example #2 – Finding and Replacing a Word within Excel
In the above example, you could find a word within the given spreadsheet. Now let’s see how you can replace the word after finding it with another word or data. You may come across the situation of changing your data within a spreadsheet. If this is a continuous process, you want to replace a particular data where ever it appears in the spreadsheet.
- Finding each piece of data one by one and replacing it, in the same way is time-consuming. Here we can use the find and replace option in Excel.
- A dialog box will open by pressing the shortcut keys in the keyboard Ctrl+H. This will offer you two dialog boxes where you can provide the text you want to find and replace it with.
- To Replace the given data, Two options are available.
- Replace
- Replace All
- The ‘Replace’ button will work similarly to the ‘Find Next’ button. This will replace the given data one by one. As the ‘Find Next’ button Replace will replace the data one by another.
- Click on the ‘Replace All’ button the data will replace with the data mentioned in the ‘Replace With’ input textbox in a single click.
- As a final result, this will show a dialog box with several replacements made in the spreadsheet. This message will help you to verify your data replacement in the worksheet.
Example #3 – Find and Replace with More Options
For the advanced find and replace, you have more options. This will give you more ways to specify the data you need to find and replace. By customizing the data finding needs, you can make the find and replace it more effective within the spreadsheet.
- Use the Ctrl+F shortcut to find the data. Once the find and replace window appears, click the ‘Options’ button in the dialog box.
- You can select different options from the available option to find the data. There exists an option to select where you want to find the specified data from the Dropbox near the ‘Within’ option; select one from the list whether you want to see the data in a sheet or an entire workbook.
- Another option is to arrange your search. By row or column, you can perform the search. This facility is better if you want to find data within different columns or rows separately.
- You can direct where you want to ‘Look in’ the mentioned data. The drop-down provides a list that contains the below options.
- Formulas
- Values
- Comments
The selection will search the data in different areas instead of finding it all over the spreadsheet.
- The different match cases can mention while finding the data. The two checkboxes are provided:
- Match Case
- Match entire cell contents
Match Case: This will find similar to the mentioned data. And any matching case will be highlighted.
Match Entire Cell Contents: Xerox copy of data will find. Similarities won’t be accepted while searching.
- All these advanced search options are available to find and replace too. Finding and replacing within a special data format is another available customization.
- The format dropdown will provide a new window where you can select the data format.
What is Find and Replace and How to Use it?
Find and replace is used to find data in a spreadsheet or worksheet. Find and replace are used as a combination. You can find data and replace the data with another mentioned data, which this facility uses in two ways.
- Find
- Find and Replace
Select the ‘Find & Select’ from the’ Home’ menu, which is categorized under editing tools. A drop down will provide the find option and replace option.
Click on anyone and a dialog box will be appeared to get the data you need to find.
Shortcut for Find and Replace
Instead of moving to all of these, use the keyboard shortcut below.
- Find: Ctrl+F
- Replace: Ctrl+H
Things to Remember
- Advanced options will provide more customization to find and replace.
- If the worksheet is in protected mode, the replacement will not work. Excel will not allow editing operations.
- To avoid unwanted replacement, go with format options while replacing.
- If the mentioned data to find within the spreadsheet does not exist, then Excel will show a warning message “We couldn’t find the data looking for. Click options for more way to search”.
Recommended Articles
This is a guide to Finding and Replacing in Excel. Here we discuss How to Find and Replace Data in Excel, practical examples, and a downloadable Excel template. You can also go through our other suggested articles –
