How to quickly search for a value in multiple sheets or workbooks?

Have you ever needed to find a specific value that could appear in different sheets or even across several workbooks in Excel? This is a common scenario, especially when working with large projects, monthly reports, or when consolidating information maintained in several files. Manually looking through each sheet or file is not only time-consuming but also prone to errors. In this tutorial, you’ll discover several effective methods to search for data efficiently, whether you need to search within one workbook, across multiple workbooks, or by using a formula-based or consolidated data solution. These approaches address practical needs you may encounter in routine Excel workflows or data analysis projects.

Which method should you use?

Search for a value in multiple sheets of a workbook with Find and Replace

Excel’s Find and Replace feature is a basic yet effective way to quickly find specific values across multiple worksheets within the same workbook. This method is most useful when you know which sheets you want to search or when your data is relatively well structured within a single file. It does not support searching across different files or closed workbooks, but it offers a straightforward way for quick lookups.

1. To begin, select the sheet tabs you want to include in your search by holding down the Ctrl key and clicking each worksheet on the sheet tab bar individually. This ensures that the search will be applied to all of the selected sheets simultaneously. See screenshot:

   

2. Once you have selected the desired sheets, press Ctrl + F to open the Find and Replace dialog box. Type the value you want to find in the Find what text box under the Find tab, and then click the Find All button. Excel will immediately display a list of all cells on the selected sheets that contain your search value, along with their locations. See screenshot:

   

Effortlessly Find and Replace Values Across Multiple Sheets and Workbooks

Kutools for Excel's advanced Find and Replace feature offers an efficient way to search and replace values across multiple sheets or even across all opened workbooks. With this advanced feature, you can save time and eliminate errors when working with large data sets, making your Excel tasks faster and more accurate.

Kutools for Excel - Supercharge Excel with over 300 essential tools, making your work faster and easier, and take advantage of AI features for smarter data processing and productivity. Get It Now

Search for a value in all workbooks in a folder with VBA

If you need to search for a specific value across multiple workbooks saved in a folder—including files you haven’t opened—Excel's built-in tools cannot do this directly. In this case, a VBA (Visual Basic for Applications) macro can automate the process for you, systematically opening each workbook in the folder, scanning all worksheets, and recording where matches are found. This approach is highly practical for periodic audits or checking for values in archived or batch files.

1. Start by opening a new or blank workbook in Excel. Press Alt + F11 to open the Microsoft Visual Basic for Applications editor.
2. In the VBA editor, go to Insert > Module to create a new module, and then paste the following VBA code into the module window.

   VBA: Search for a value in all workbooks in a folder

   Sub SearchFolders()
       'Updated by Extendoffice
       Dim xFso As Object
       Dim xFld As Object
       Dim xStrSearch As String
       Dim xStrPath As String
       Dim xStrFile As String
       Dim xOut As Worksheet
       Dim xWb As Workbook
       Dim xWk As Worksheet
       Dim xRow As Long
       Dim xFound As Range
       Dim xStrAddress As String
       Dim xFileDialog As FileDialog
       Dim xUpdate As Boolean
       Dim xCount As Long
       Dim xAWB As Workbook
       Dim xAWBStrPath As String
       Dim xBol As Boolean
   
       Set xAWB = ActiveWorkbook
       xUpdate = Application.ScreenUpdating
       xAWBStrPath = xAWB.Path & "\" & xAWB.Name
       On Error GoTo ErrHandler
       Set xFileDialog = Application.FileDialog(msoFileDialogFolderPicker)
       xFileDialog.AllowMultiSelect = False
       xFileDialog.Title = "Select a folder"
       If xFileDialog.Show = -1 Then
           xStrPath = xFileDialog.SelectedItems(1)
       End If
       If xStrPath = "" Then Exit Sub
   
       xStrSearch = "KTE"
       Application.ScreenUpdating = False
       Set xOut = xAWB.Worksheets.Add
       xRow = 1
       With xOut
           .Cells(xRow, 1) = "Workbook"
           .Cells(xRow, 2) = "Worksheet"
           .Cells(xRow, 3) = "Cell"
           .Cells(xRow, 4) = "Text in Cell"
           Set xFso = CreateObject("Scripting.FileSystemObject")
           Set xFld = xFso.GetFolder(xStrPath)
           xStrFile = Dir(xStrPath & "\*.xls*")
           Do While xStrFile <> ""
               xBol = False
               If (xStrPath & "\" & xStrFile) = xAWBStrPath Then
                   xBol = True
                   Set xWb = xAWB
               Else
                   Set xWb = Workbooks.Open(Filename:=xStrPath & "\" & xStrFile, UpdateLinks:=0, ReadOnly:=True, AddToMRU:=False)
               End If
               For Each xWk In xWb.Worksheets
                   If xBol And (xWk.Name = .Name) Then
                   Else
                       Set xFound = xWk.UsedRange.Find(What:=xStrSearch, LookIn:=xlValues, LookAt:=xlPart, _
                           SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
                       If Not xFound Is Nothing Then
                           xStrAddress = xFound.Address
                       End If
                       Do
                           If xFound Is Nothing Then
                               Exit Do
                           Else
                               xCount = xCount + 1
                               xRow = xRow + 1
                               .Cells(xRow, 1) = xWb.Name
                               .Cells(xRow, 2) = xWk.Name
                               .Cells(xRow, 3) = xFound.Address
                               .Cells(xRow, 4) = xFound.Value
                           End If
                           Set xFound = xWk.UsedRange.FindNext(After:=xFound)
                       Loop While xStrAddress <> xFound.Address
                   End If
               Next xWk
               If Not xBol Then
                   xWb.Close SaveChanges:=False
               End If
               xStrFile = Dir
           Loop
           .Columns("A:D").EntireColumn.AutoFit
       End With
       MsgBox xCount & " cells have been found", , "Kutools for Excel"
   
   ExitHandler:
       Set xOut = Nothing
       Set xWk = Nothing
       Set xWb = Nothing
       Set xFld = Nothing
       Set xFso = Nothing
       Application.ScreenUpdating = xUpdate
       Exit Sub
   
   ErrHandler:
       MsgBox Err.Description, vbExclamation
       Resume ExitHandler
   End Sub
   

3. Press F5 or click the Run button to execute the macro. A Select a folder dialog box will appear, allowing you to choose the folder that contains the workbooks you want to search. See screenshot:

   

4. Click OK. After the search is complete, a message box will show the total number of cells found that contain your specified value. See screenshot:

   

5. Click OK to close the message. All locations where the value is found are listed in a new worksheet, including the workbook name, worksheet name, cell reference, and the exact cell content for your review.

   

Quickly search for a value across multiple open workbooks with Kutools for Excel

When you want to perform a search across several workbooks that are already open in Excel, Kutools for Excel offers a dedicated Find and Replace pane that makes the process much easier and more organized. This is especially helpful for quickly scanning your workbooks without any need for scripting or complicated setup. It is ideal for users who often work with multiple files at the same time and need a user-friendly, straightforward tool to manage searches.

1. In any open workbook, go to the Kutools tab and select Navigation. Then click the Find and Replace button to open the Find and Replace pane, usually located on the left side of your Excel window. See screenshot:

   

2. On the Find tab, enter the value you want to search for in the Find what box. Choose All Workbooks from the Within drop-down list to search every open workbook. Then click Find All to display a list of all matching cells, along with their locations. See screenshot:

   

Tip: Kutools for Excel’s advanced Find and Replace utility allows you to search and replace data not only in all open workbooks, but also in selected sheets, the active workbook, the active sheet, or the current selection. This gives you tailored control depending on your needs.

Precaution: Make sure all the workbooks you want to search are open before you start, as this tool cannot search files that are not currently open in Excel.

Troubleshooting: If expected results are missing, confirm that the workbook or worksheet is selected in the Workbooks list and check the search options in the pane.

Demo: Search for a value across multiple open workbooks with Kutools for Excel

Search for a value across multiple sheets using Excel formulas

In situations where you have several known sheet names in your workbook and need to check if and where a specific value exists among those sheets, you can use Excel formulas to dynamically search across them. This approach is particularly suitable when you want to keep your search results refreshed automatically and your list of sheets is relatively static or managed in a separate table.

This method requires you to already know or list the names of all sheets to search. It is most effective for automated checks, dashboards, or whenever you want to build a summary reference without running a full scan each time.

Advantages: Results update automatically as data changes; no scripts or add-ins are required; everything is handled within the workbook.
Disadvantages: This method is not suitable when sheet names change frequently or when you have a very large number of sheets.

Example scenario: Assume you have three sheets named Sheet1, Sheet2, and Sheet3. You want to know which sheets contain a specific value, such as "Invoice123", in column A.

1. Suppose you have a list of sheet names in D2:D4 (D2: Sheet1, D3: Sheet2, and D4: Sheet3). Enter the value to search for, such as "Invoice123", in E1. Then, in F2, enter this formula:

   =IF(COUNTIF(INDIRECT("'"&D2&"'!A:A"),$E$1)>0,"Found","Not Found")

2. Drag the formula down from F2 to F4 to check all sheets listed in D2:D4. This will return "Found" or "Not Found" for each sheet.

How it works: The formula uses INDIRECT to create a reference to each listed worksheet and COUNTIF to check if the value in E1 appears in column A of each sheet. Adjust the range A:A to target another column or a specific range, such as A1:Z100, if needed.

Additional tip: To retrieve the names of the sheets containing the value, use the following formula:

=TEXTJOIN(", ",TRUE,IF(COUNTIF(INDIRECT("'"&D2:D4&"'!A:A"),$E$1)>0,D2:D4,""))

This returns a comma-separated list of all sheet names where the value is found. In Excel 2019, confirm the formula with Ctrl + Shift + Enter. In Excel 2021 or later and Microsoft 365, press Enter. Be careful with INDIRECT—it only works with open workbooks and cannot search closed files.

Frequently Asked Questions

Related Articles:

• How to quickly find and replace across multiple worksheets or the whole workbook?
• How to quickly find and replace in multiple open Excel files?
• How to find the largest negative value (less than 0) in Excel?
• How to extract all duplicates from a column in Excel?

Best Office Productivity Tools

🤖	Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions…
	Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |  Round without Formula ...
	Super Lookup: Multiple Criteria VLookup  |   Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
	Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
	Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
	Featured Features: Grid Focus   |  Design View   |  Big Formula Bar   |  Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells   |  Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
	Top 15 Toolsets:  12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more

Use Kutools in your preferred language – supports English, Spanish, German, French, Chinese, and 40+ others!

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...

More details of Kutools for Excel...Free Download...

---

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!

More details of Office Tab...Free Download...

---

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

More details of Kutools for Office...Free Download...