How to Find and Replace in Excel? A Step-by-Step Guide
(Note: This guide on how to Find and Replace in Excel is suitable for all Excel versions including Office 365)
Have you ever faced a situation where you have to change a particular data in multiple places? Of course, you can choose to find and replace the data manually. However, finding and changing each data one after the other might be easy when the data are less. You will need an effective way to find and replace multiple data simultaneously.
In this article, I will tell you how to use find and replace in Excel along with its features.
You’ll Learn:
- What Is Find and Replace in Excel?
- How to Find and Replace in Excel?
- Additional Options to Find and Replace in Excel
Watch our video on how to use find and replace in Excel
Related Reads:
How to Calculate Factorial in Excel? Along with 2 Easy Examples
How to Graph a Function in Excel? 2 Easy Ways
Short Date Format in Excel – 3 Different Methods
What Is Find and Replace in Excel?
As its name suggests, the Find and Replace option in Excel is used to search for any particular data and replace it with the data of your choice. You can search for any number or string and replace them one after the other or replace all the data at the same time.
Additionally, Excel’s Find and Replace option allows you to use wildcards to find any missing data only by using a part of the data. And, you can also search between worksheets or whole workbooks.
How to Find and Replace in Excel?
Now, let us see how to use the Find and Replace option in Excel along with its functionalities.
In short, the Find option is mainly helpful when you want to find any particular data across multiple cells. The Replace option replaces the found data with any value of your choice. Using these options, you can find and replace the cells containing data across the spreadsheet or even the whole workbook.
Let us now see how the Find and Replace option works in Excel with an example.
Consider an Excel worksheet that consists of the consolidated mark list of 20 students for 20 subjects. There happened to be a small mishap in the validation process and the students who scored 100 marks should be changed to 99. So, you have to find the cells which house the value “100” and change them to “99”. This process can be a little tedious manually. In these cases, you can use the Find and Replace in Excel.
To find and replace the values, navigate to Home. From the Find & Select dropdown, click on Replace. Or, you can use the keyboard shortcut Ctrl+H.
This opens up the Find and Replace dialog box.
In the Find what: text box, enter the value you want to find. In the Replace with: dialog box, enter the value you want to replace the found value with. In this case, we want to search through the worksheet to find the value “100” and replace it with the value “99”.
Once you have entered the value, you can see two buttons to help you find the data and the other two buttons to help you replace the data. Let us see them one by one in detail.
Find Next
This option finds the particular value and highlights them to the user one after the other.
When you enter 100 and click on Find Next, Excel searches through the sheet and shows you the first occurrence of the particular data. When you click on the Find Next again, Excel searches for the next occurrence of the data in the worksheet.
When just finding the data, Excel goes in loops displaying the same occurrences even after highlighting all the entries.
Find All
This option finds all the entries of the particular data and shows them to the user in an instant.
When you click the Find All button, Excel shows you all the cells that contain the particular value. You can scroll down and click on the value to display it in the worksheet.
If you want to see all the cells with the particular value at the same time, press Ctrl+A. This shows you all the entries of the particular value in the worksheet.
Note: If you want to only find the occurrences of the values without any need to replace them, just click on the Find tab in the Find and Replace dialog.
Replace
This option is used to replace the values in cells one after the other. This is particularly helpful when you have to confirm the values before replacing them.
When you first click on the Replace button, Excel replaces the first instance of the value with the value in the Replace with: textbox and highlights the next occurrence of the value.
If you click on the Replace button again, the highlighted value will be replaced and the next occurrence of the value will be highlighted.
Clicking on the Replace button replaces the values one after the other. If you don’t want to replace the particular value, click on the Find Next button. This will skip replacing the value in the cell and move on to highlight the next occurrence of the particular value.
Once you have replaced all the entries of the particular value, Excel will throw a pop-up saying “Excel couldn’t find a match”. Click OK to close the pop-up.
Note: If you only want to find and replace the values in Excel, you can straight away click on the Replace or Replace All button instead of finding the values using the Find Next and Find All buttons.
Replace All
If you feel like replacing the values one after the other is a little less effective, click on the Replace All button to replace all the values in one go.
This replaces all the values in the Find what: textbox with the value from the Replace with: text box.
Once you click on Replace All, Excel replaces all the data and throws a pop-up showing the number of replacements done. Click OK to close the pop-up.
Note: Always be very cautious when using the Replace All button because it changes all the occurrences of the particular value in the worksheet. If you ever feel like there might be some instances that need not be replaced, you can just use the normal Replace button to check and replace the values one after the other.
| Replace All | Replaces all the cells with a particular value. |
| Replace | Replaces the cell with the particular value one after the other. |
| Find All | This option searches and finds the total occurrences of the particular value in a single go. |
| Find Next | Find Next option searches and displays the occurrence of the value one after the other. |
Also Read:
Excel String Compare – 5 Easy Methods
How to Add a Secondary Axis in Excel? 2 Easy Ways
How to Wrap Text in Excel? With 6 Simple Methods
Additional Options to Find and Replace in Excel
Excel has various functionalities to Find and Replace the data in any way possible. Clicking on the Options button in the Find and Replace dialog box shows you advanced options to find and replace any data in Excel. Let us see each option and its functionalities in detail.
Format Option
This option enables you to set the format of the cells where you can search or replace the values.
Only when the format of the cell is the same as the format specified in the Find what: textbox, Excel finds and displays the value. The same is true for the Replace with: text box. When you specify any format in the Replace with: textbox, Excel changes the format of the cell along with replacing the value.
There are two ways to set the format criteria.
- Click on the Format button. This opens the Format Cells dialog box. Select the formatting of the cell and click OK.
- Click on the dropdown near the Format button and select Choose Format from Cell. This navigates to the worksheet and you can select any cell to copy its formatting.
You can also clear the formatting criteria from the dropdowns in the Find and Replace text boxes.
Within Dropdown
Using this option, you can change the scope of the Find and Replace option. You can either limit the functionality of the search to the current worksheet or the entire workbook.
Search Dropdown
By default, Excel searches for the value in the Find textbox in a row-wise manner. Using this option, you can assign the order of search either row-wise or column-wise.
Look in Dropdown
This option helps you specify the type of place where the find option has to search. You can choose to look for the data in formulas, comments, or cells with values.
Match Case
When the match case option is checked, Excel searches and finds only the values that pertain to the case and format as specified in the text box. Check this option if you want the search to be case-sensitive.
Match Entire Cell Contents
When this checkbox is checked, Excel only takes into account the entire cell content. Partial data of any data type is ignored and only the complete entry in the textbox is searched.
Using Wildcards
Use the wildcards to search for any missing values. You can use the * wildcard to replace any character between the first and last characters, whereas using the ? wildcard between any two characters replaces exactly one character. Additionally, when you have to search for * or ? character, you can use the ~ tilde character along with * or ? to search for their occurrences in the worksheet.
Suggested Reads:
How to Merge Cells in Excel? 3 Easy Ways
How to Calculate Percentile in Excel? 3 Useful Formulas
How to Select Non Adjacent Cells in Excel? 5 Simple Ways
Frequently Asked Questions
How to minimize the extended dialog box without closing the Find and Replace dialog box?
To minimize the extended partition when you click on the Find All button, navigate to the corner of the dialog box and drag to the edges to hide them.
What are the advanced features of the Find and Replace option in Excel?
Clicking on the Option>>> button enables advanced options. From here, you can set the format criteria according to your needs.
How to access the find and replace option in Excel?
You can access the find and replace option by navigating to Home and under the Find & Select dropdown, click on Replace. You can also use the keyboard shortcut Ctrl+H.
Closing Thoughts
The Find and Replace function is really helpful and offers a variety of functionalities to help you search and replace data or values in Excel. You can search and replace any data in Excel irrespective of their data type or the cell formatting.
If you find this guide helpful and are looking for more guides, please visit our Excel resources center.
Want to skill up in Excel?
Simon Sez IT has been teaching Excel for ten years. For a low, monthly fee you can get access to 130+ IT training courses. Click here for advanced Excel courses with in-depth training modules.
Simon Calder
Chris “Simon” Calder was working as a Project Manager in IT for one of Los Angeles’ most prestigious cultural institutions, LACMA. He taught himself to use Microsoft Project from a giant textbook and hated every moment of it. Online learning was in its infancy then, but he spotted an opportunity and made an online MS Project course - the rest, as they say, is history!