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 to find a particular text or number within the spreadsheet easily. The replace option along with find will provide a better way to replace specific data which is already found using the find option. Find can be used alone but to replace a data you should use find option first and then only replace.
Find and replace comes under the editing option of the Home menu. Both options can be used separately according to your necessity. To find something in a spreadsheet with hundreds of rows and column manual scanning is not an effective method. 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
In this example let’s see how can you use find and replace options. 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 short cut 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 ever 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 and it will change and show the different locations by selecting the corresponding cell. Once the list gets completed it again moves to the first listed location.
Example #2 – Finding and Replacing a Word within Excel
In the above example, you were able to find a word within the given spreadsheet. Now let’s see how can you replace the word after finding with another word or data. You may come across the situation to make changes to your data within a spreadsheet. If this is a continues process, for example, you want to replace a particular data where ever it appears in the spreadsheet.
- Finding each data one by one and replacing, in the same way, is time-consuming. Here we can use the find and replace option in excel.
- By pressing the shortcut keys in the keyboard Ctrl+H a dialog box will get open. This will offer you two dialog box where you can provide the text which you want to find and replace with.
- To Replace the given data Two options are available.
- Replace
- Replace All
- ‘Replace’ button will work similarly to the ‘Find Next’ button. This will replace the given data one by one. Similar to 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 ‘Replace With’ input textbox in a single click.
- As a final result, this will show a dialog box with a number of replaces made in the entire spreadsheet. This message will help you to verify your data replacement among the worksheet.
Example #3 – Find and Replace with More Options
For advanced find and replace you have more options. This will provide you more ways to specify the data that you need to find and replace. By customizing the data finding needs you can make the find and replace more effective within the spreadsheet.
4.9 (1,928 ratings)
View Course
- Use Ctrl+F shortcut to find the data. Once the find and replace window appears, click on the ‘Options’ button within the dialog box.
- From the available option, you can select different options to find the data. There exists an option to select where you want to find the specified data. From the dropbox near ‘Within’ option select one from the list, whether you want to find the data in a sheet or entire workbook.
- Another option is to arrange your search. By row or column, you can perform the search. If you want to find data within different columns or row separately this facility is a better way.
- You can direct where you want to ‘Look in’ the mentioned data. The drop-down provides a list which contains the below options.
- Formulas
- Values
- Comments
According to the selection, it will search the data in different areas instead of finding all over the spreadsheet.
- The different match cases are able to 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. To find and replace within special data format is another available customization.
- The format dropdown will provide a new window where you can select the format of the data.
What is Find and Replace and How to Use it?
Find and replace is used to find data among a spreadsheet or worksheet. Find and replace is used as a combination. Where you can find data and replace the data with another mentioned data. This facility uses in two ways.
- Find
- Find and Replace
From the ‘Home’ menu select the ‘Find & Select’ which 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 these use the keyboard shortcut as 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 Find and Replace in Excel. Here we discuss How to Find and Replace Data in Excel along with practical examples and downloadable excel template. You can also go through our other suggested articles –
