Quickly Replace A Lot of Data in Excel

Add to Favorites
Author:

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.

 


Downloadable Files: Excel File

Question? Ask it in our Excel Forum


Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons 50+ Hours of Instruction 200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Similar Content on TeachExcel
Sort Data that Doesn't Have Headers in Ascending Order in Excel
Macro: Sort data that doesn't have headers in ascending order in Excel with this macro. This is a...
Filter Data in Excel to Show Only the Top X Percent of that Data Set - AutoFilter
Macro: This Excel macro filters a set of data in Excel and displays only the top X percent of tha...
Loop through a Range of Cells in Excel VBA/Macros
Tutorial: How to use VBA/Macros to iterate through each cell in a range, either a row, a column, or ...
Limit the Total Amount a User Can Enter into a Range of Cells in Excel
Tutorial: How to limit the amount that a user can enter into a range of cells in Excel.  This works...
Group Data Together for Increased Readability in Excel
Tutorial: How to group data together or collapse it in order to focus only on the important data in...
Quickly Switch the Columns and Rows of a Data Set in Excel
Tutorial: How to switch a data set in Excel so that the columns become rows and the rows become col...
Tutorial Details
Downloadable Files: Excel File
Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course