How to find and replace across multiple worksheets or an entire workbook in Excel
Excel's Find and Replace feature makes it easy to replace text in the active worksheet. When you need to apply the same change across several worksheets or throughout a workbook, you must select the appropriate search scope. This tutorial explains how to complete both tasks with Excel's built-in feature and Kutools for Excel.
- Kutools for Excel vs. Excel's built-in Find and Replace
- Find and replace across multiple worksheets or the whole workbook with Excel's built-in feature
- Find and replace across multiple worksheets or the whole workbook with Kutools for Excel
- Frequently Asked Questions
Kutools for Excel vs. Excel's built-in Find and Replace
Excel's built-in Find and Replace feature is a practical choice for a selected range, the active worksheet, or the active workbook. Kutools for Excel includes these common scopes and extends them to selected worksheets, specific open workbooks, or all open workbooks. It also provides additional search targets and a single pane for managing cross-workbook results.
| Capability | Kutools for Excel | Excel's built-in Find and Replace |
|---|---|---|
| Search and replacement scope |
|
|
| Selecting exact files and sheets | The Workbooks list displays open workbooks and their worksheets, allowing you to include only the specific workbooks or sheets needed. | There is no central list for choosing worksheets from multiple open workbooks. The operation is limited to the active workbook. |
| Batch replacement across workbooks | Set Within to All Workbooks, or choose specific targets, and then run Replace All once across every included workbook. | The same find-and-replace operation must be repeated separately in each workbook. |
| Searchable content types | Use Look in to search formulas, values, comments, hyperlinks, or chart titles. It can also replace values, hyperlinks, and chart titles across the included worksheets and workbooks. | The standard Find feature supports formulas, values, notes, and comments within the active search scope. It does not provide Hyperlinks or Chart Titles as dedicated Look in targets or process them across multiple open workbooks. |
| Cross-workbook results | Displays results from all included worksheets and workbooks together at the bottom of the Navigation pane. | Find All lists matches only from the current sheet or active workbook. |
Find and replace across multiple worksheets or the whole workbook with Excel's built-in feature
Find and replace across multiple worksheets
- Select the worksheet tabs in which you want to find and replace text.
Tip: To select nonadjacent worksheets, hold Ctrl while clicking each worksheet tab. To select adjacent worksheets, click the first tab, hold Shift, and then click the last tab.
- Press Ctrl + H to open the Find and Replace dialog box. Enter the text you want to replace in the Find what box and the replacement text in the Replace with box.
- Click Replace All. A dialog box displays the number of replacements made.
- Click OK, and then click Close to close the dialog boxes.
Find and replace throughout the workbook
- Press Ctrl + H to open the Find and Replace dialog box. Enter the text you want to replace in the Find what box and the replacement text in the Replace with box.
- Click Options to expand the dialog box, and then select Workbook from the Within drop-down list.
- Click Replace All. A dialog box displays the number of replacements made throughout the workbook.
- Click OK, and then click Close to close the dialog boxes.
Find and replace across multiple worksheets or the whole workbook with Kutools for Excel
With Kutools for Excel, you can use the Navigation pane to find and replace text in a selection, the active worksheet, selected worksheets, the active workbook, or specific and all open workbooks.
After installing Kutools for Excel, follow these steps:
- Open the workbook or workbooks you want to search. Click Kutools > Navigation to open the Navigation pane, and then click the Find and Replace button to open the Find and Replace pane.
- Configure the replacement in the Find and Replace pane:
- Open the Replace tab.
- Enter the text you want to replace in the Find what box and the replacement text in the Replace with box.
- Select the required scope from the Within drop-down list. You can use Selection, Active Sheet, Selected Sheets, Active Workbook, or All Workbooks. Use the Workbooks list to include only specific open workbooks or worksheets.
- Click Replace All to replace all matching text in the selected scope. To find matching text without replacing it, use the Find tab and click Find All.
Tips:
- Scope options in the Within drop-down list:
- If the Within drop-down list and Workbooks list are not visible, click the Expand button to display the advanced Find and Replace options.
Kutools for Excel's Navigation pane also lets you switch between recently used worksheets, list open workbooks, worksheets, columns, and names, and save frequently used formulas or pictures for reuse. Learn more about the Navigation pane.
Note: By default, the keyboard shortcut for opening Kutools' Find and Replace pane is Win + Shift + Q. If you prefer Ctrl + F, open the Navigation settings and select Pressing Ctrl + F to apply Find and Replace of Kutools. After this option is enabled, pressing Ctrl + F opens Kutools' Find and Replace pane.
Frequently Asked Questions
Why did Replace All also change cells containing longer words?
By default, Excel can replace matching text wherever it appears within a cell. For example, replacing tools also affects Kutools. To replace only cells whose entire content matches the search text, expand Options and select Match entire cell contents. Select Match case as well if capitalization must match.
What should I do after replacing text across grouped worksheets?
Ungroup the worksheets when the replacement is complete. Otherwise, later edits made in one grouped worksheet may also be applied to the other selected worksheets. Click an unselected worksheet tab, or right-click a selected tab and choose Ungroup Sheets.
Why can't Find and Replace locate text that is visible in the worksheet?
Check the selected scope and the Look in setting first. Also confirm that Match case, Match entire cell contents, or a previously selected search format is not excluding the expected matches.
Can I find and replace across several open workbooks at once?
Excel's built-in Find and Replace feature works only within the current worksheet or active workbook. With Kutools for Excel, you can set Within to All Workbooks and use Replace All to process every open workbook in one operation.
Can Kutools search workbooks that are not open?
No. The Workbooks list contains workbooks that are currently open in Excel. Open every workbook you want to include before starting the search or replacement.
What should I do if Replace All changes the wrong cells?
In most cases, press Ctrl + Z immediately or click Undo to reverse the replacement. If AutoSave is off and you have not saved the workbook, you can also close it and choose Don't Save, but this discards every change made since the last save—not only the replacement. If AutoSave is on and the file is stored in OneDrive or SharePoint, use File > Info > Version History if Undo is no longer available.
Can I search only specific worksheets from different open workbooks?
Excel does not provide a central list for selecting worksheets from different workbooks. With Kutools for Excel, you can choose Selected Sheets and use the Workbooks list to include only the worksheets you need.
Can I find and replace hyperlinks or chart titles?
Excel's standard Find and Replace dialog does not provide Hyperlinks or Chart Titles as dedicated Look in options. Kutools for Excel lets you target these content types and process them across the selected worksheets or open workbooks.
Demo: Find and replace across multiple worksheets or an entire workbook with Kutools for Excel
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in