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
Leave a Reply