Quickly Replace A Lot of Data in Excel

The Find & Replace works much the same way as Find and is located in the same place. (Find & Select drop down on the Home tab) You can even just open up Find (Ctrl + F) and select the Replace tab:

All you need to do is enter what you are looking to find in the Find what box then what you are looking to replace it with in the Replace with box.

As an example I am going to use the same data set as the last tutorial and find all cells with Jackson but this time I am going to replace them all with Beckett as well. If I click Replace, only the first entry Excel finds for Jackson will be changed. As I know there are 2 entries for Jackson, I am going to click Replace All. This will replace all instances of Jackson with Beckett based on my Find criteria.

The following pop-up will appear to indicate the Find & Replace was successful and the number of cells which were changed. Note: The scope of my search was only within the current sheet so the entries for Jackson on Sheet2 will not be changed.

The other options for Find & Replace are the exact same as Find. I covered these in the previous tutorial as they mostly affect the search criteria of the Find & Replace, but the are also listed below. The only new option is the format of the Replace with. This allows you to replace the contents and/or formatting of cells based on their contents and/or formatting but this is a more advanced use of Find & Replace and will be covered later.

Other Find/Replace Options:

• The Search dropdown doesnt really matter unless your data set is quite large and you want to save time. It just defines how Find will search for your data. (Either column by column or row by row)
• As all my data is just simple text I dont need to change the Look in drop down. This is required when a workbook has lots of functions and formulas which I will cover how to use in a later tutorial.
• The match case and match entire cell contents checkboxes are pretty self-explanatory. They are used to refine a search. If you choose to match case, searching for TYLER for example would be different to searching for Tyler. If you choose to match entire cell contents then Excel will look for cells which entire cell value matches what you are looking for. So if you typed Tyl instead of Tyler with match entire cell contents enabled you wouldnt find anything in the dataset I provided.
• The Format options allow you to refine a search based on a cells formatting as well as its contents.