How to Find and Replace Values in Multiple Excel Files (3 Methods)

Method 1 – Find and Replace Values in Multiple Worksheets in an Excel File

In the following image, you can see that we have an Excel sheet named One.

In the same workbook, we have another sheet named Two.

We will replace the word “Ambrose” with “Moxley”.

• Open your Excel workbook.
• From the Home tab, select Find & Select ➜ Replace.

• A Find and Replace pop-up box will appear. Click on the Options>> (shown in the image below).

• New options will turn up in the pop-up box.
• Enter the word you want to find (e.g., “Ambrose”) in the Find what section.
• Enter the replacement word (e.g., “Moxley”) in the Replace with section.
• Choose Workbook from the drop-down list next to Within to search all sheets in the workbook.
• Click Replace All.

• Confirm the replacements. If you have more sheets, additional replacements will occur.

Check whether the replacements occurred or not.

Consider both the above and the following images.

The word “Ambrose” from both of our sheets is now replaced with the word “Moxley”.

Read More: How to Find and Replace within Selection in Excel

Method 2 – Find and Replace Values in Multiple Sheets of Multiple Workbooks

Look at the following images. We have opened two Excel workbooks. One is named Find and Replace Value (see the image below).

Another one is named Transpose Multiple Columns (shown in the picture below).

Now, after you have done replacing value from one specific worksheet discussed in the above section, you will notice that the Find and Replace pop-up box is open.

• After replacing values in one worksheet, keep the Find and Replace pop-up box open.
• Hover over the next workbook without closing the pop-up box.
• Press Replace All to find and replace values in all worksheets of that workbook.

Repeat for as many workbooks as needed.

Read More: How to Find and Replace Multiple Words from a List in Excel

Method 3 – Embed VBA to Find and Replace Values in Open Workbook in Excel

• Press Alt + F11 on your keyboard or go to the Developer tab ➜ Visual Basic to open the Visual Basic Editor.

• In the pop-up code window, click Insert ➜ Module from the menu bar.

• Copy the following code and paste it into the code window.

Sub MultipleFindAndReplace()
Dim iSheet As Worksheet
Dim OldValue As Variant
Dim NewValue As Variant
Dim i As Long
OldValue = Array("John", "Roman", "Dean", "Seth", "Finn")
NewValue = Array("Ben", "Alex", "Joe", "Chris", "Josh")
  For i = LBound(OldValue) To UBound(OldValue)
      For Each iSheet In ActiveWorkbook.Worksheets
        iSheet.Cells.Replace What:=OldValue(i), Replacement:=NewValue(i), LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
      Next iSheet
  Next i
End Sub

This piece of code will replace the array of values “John, Roman, Dean, Seth, Finn” with the values “Ben, Alex, Joe, Chris, Josh”.

• Press F5 on your keyboard or select Run ➜ Run Sub/UserForm from the menu bar.
  • Alternatively, click the small Run icon in the sub-menu bar to execute the macro.

• After the code runs, check the workbook to see that all values (“John,” “Roman,” “Dean,” “Seth,” “Finn”) have been replaced with their corresponding values (“Ben,” “Alex,” “Joe,” “Chris,” “Josh”).

• Execute this code on one workbook.
• Hover over to another workbook.
• Go to Developer ➜ Macros and select the macro name.
• Click Run to apply the same replacements to the other workbook.

The VBA code scans each worksheet in the active workbook, replacing the specified values. It’s a powerful way to automate find-and-replace tasks.

Dim iSheet As Worksheet
Dim OldValue As Variant
Dim NewValue As Variant
Dim i As Long

OldValue = Array("John", "Roman", "Dean", "Seth", "Finn")
NewValue = Array("Ben", "Alex", "Joe", "Chris", "Josh")

  For i = LBound(OldValue) To UBound(OldValue)
      For Each iSheet In ActiveWorkbook.Worksheets
        iSheet.Cells.Replace What:=OldValue(i), Replacement:=NewValue(i), LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
      Next iSheet
  Next i

Download Workbook

You can download the practice workbook from here:

Related Articles

• How to Find and Replace Text Color in Excel
• How to Show Dash Instead of Zero in Excel
• Replace Text of a Cell Based on Condition in Excel
• How to Find and Replace Using Formula in Excel
• How to Replace Text in Excel Formula

<< Go Back to Find and Replace | Learn Excel