Consider a situation where you have a workbook with multiple sheets. Imagine having 10, 15 or 20 worksheets where a spelling or name change must be performed.  Such a task would be very tedious if you were to perform the change one sheet at a time. The Find and Replace function has a setting where you can perform the change on ALL instances in the workbook.

In our example below, there are multiple sheets in a single workbook (2020, 2021, 2022 and 2023).  The objective is to change the spelling of “B & B INC.” on all the sheets to “Books and Beyond.”

Steps to Perform (Find and Replace)

1.  Press CTRL + H to get the Find and Replace dialog box

2.  In the Find What field, type the original name to be replaced – in this example, B & B INC.

3.  In the Replace With field, type the new name – in this example, Books and Beyond (case sensitive)

4.  Click on the Options button, which shows more options for replacing

5.  For Within: field, select the dropdown and choose workbook

6.  Leave the two check boxes unchecked
(Reason:  We are not trying to “find” B & B INC. in a particular case, and leaving the match entire cell contents unchecked will look for partial names or entries)

7.  Click on Replace All

8.  You now have a notification that 8 replacements were made, for this example