How to always search the entire workbook in Excel?
Excel's Find and Replace dialog lets you search either the current worksheet or the entire workbook. If the Within setting is Sheet, however, you have to change it to Workbook whenever you want to search all worksheets. Excel does not provide a built-in setting that permanently makes Workbook the default. The two methods below offer practical alternatives: run a VBA macro that searches and replaces across the current workbook, or set Active Workbook as the default scope in Kutools for Excel.
In this tutorial:
Which method should you use?
The VBA method runs a separate whole-workbook replacement, while Kutools lets you save a default scope for its Find and Replace pane.
| Situation | Recommended method | Why |
|---|---|---|
| Run an exact-match replacement across the entire workbook | VBA | Searches every worksheet without an additional tool, but must be run manually and does not change Excel's built-in Find default. |
| Use Active Workbook as the default Find and Replace scope | Kutools for Excel | Saves the scope for its pane and still lets you switch to selected sheets or multiple open workbooks when needed. |
Search and replace the entire workbook with VBA
Excel does not offer a direct way to make Workbook the permanent default in its Find and Replace dialog. As a workaround, the following macro searches every worksheet in the workbook containing the code and replaces cells whose entire contents match the search value.
- Press Alt + F11 to open the Microsoft Visual Basic for Applications window.
- Click Insert > Module, and paste the following code into the module window.
VBA code: Search and replace the entire workbook
Sub FindReplace_WB() 'Updated by Extendoffice Dim ws As Worksheet Dim xFind As Variant Dim xRep As Variant xFind = Application.InputBox("Find what", "Find and Replace in Workbook", Type:=2) If VarType(xFind) = vbBoolean Then Exit Sub If xFind = "" Then MsgBox "Please enter a value to find.", vbInformation, "Find and Replace in Workbook" Exit Sub End If xRep = Application.InputBox("Replace with", "Find and Replace in Workbook", Type:=2) If VarType(xRep) = vbBoolean Then Exit Sub Application.ScreenUpdating = False On Error GoTo CleanUp For Each ws In ThisWorkbook.Worksheets ws.UsedRange.Replace What:=CStr(xFind), Replacement:=CStr(xRep), _ LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False Next ws CleanUp: Application.ScreenUpdating = True End Sub - Press F5 to run the macro. In the two dialog boxes that appear, enter the text to find and the replacement text.
- Click OK. Exact matches across all worksheets in the workbook are replaced.
- Save the file as an Excel Macro-Enabled Workbook (*.xlsm). The next time you open it, run the macro again whenever you need to search and replace the entire workbook.
📝 Notes:
- The macro uses LookAt:=xlWhole, so it replaces only cells whose entire contents match the search value.
- Changes made by a macro normally cannot be undone with Ctrl + Z. Save a backup before running it on important data.
Set the default search scope to the active workbook with Kutools for Excel
The Navigation pane in Kutools for Excel provides a direct setting for the default Find and Replace scope. Set it to Active Workbook when you normally work within one file; you can still switch to a worksheet, selected sheets, or multiple open workbooks when needed.
After installing Kutools for Excel, follow these steps:
- Click Kutools > Navigation.
- In the Navigation pane, click the
button to open Navigation Options. Under Find and Replace, set the default scope to Active Workbook, and then click OK. 
- In the Navigation pane, click the
button to open Find and Replace. Then complete the replacement as follows: 
- Confirm that Within is set to Active Workbook.
- On the Replace tab, enter the search text in Find what and the replacement text in Replace with.
- Click Replace All to replace the matching text throughout the active workbook.
- From now on, the Kutools Find and Replace pane uses Active Workbook as its default scope when you open it.
button to open Navigation Options. Under Find and Replace, set the default scope to Active Workbook, and then click OK. 
button to open Find and Replace. Then complete the replacement as follows: 
Frequently Asked Questions
Can I make Workbook the permanent default in Excel's built-in Find and Replace dialog?
No. Excel lets you choose Sheet or Workbook in the Within list, but it does not provide a permanent default-scope setting.
Does the VBA macro change the scope used by Ctrl + F or Ctrl + H?
No. It performs its own replacement across every worksheet in the workbook containing the macro. It does not change Excel's built-in Find and Replace settings.
Why does the macro ignore text that appears inside a longer cell value?
The code uses LookAt:=xlWhole, which matches the entire cell. To replace partial matches instead, change it to LookAt:=xlPart.
Can I undo replacements made by the VBA macro?
Usually not with Ctrl + Z. Save the workbook or create a backup before running the macro.
Can Kutools search more than the active workbook?
Yes. You can change the scope when needed to search selected worksheets, specific open workbooks, or all currently open workbooks.
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