How to quickly search 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’re searching within one workbook, multiple workbooks, or you need a formula-based or consolidated data solution. These approaches address practical needs you may encounter in routine Excel workflows or data analysis projects.

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

Search a value in all workbooks of a folder with VBA

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

Search a value across multiple sheets using Excel formulas

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

Excel’s Find and Replace function 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 in 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:

Tip: The Find and Replace tool only searches within the selected sheets that are open and visible. If you want to extend your search to additional sheets, be sure to select them as described above. If you accidentally miss a sheet, simply repeat the operation after including it in your selection.

Precautions: This method does not search across closed or hidden workbooks and does not highlight cells automatically – it will only list the results for navigation.

Troubleshooting: If you do not see expected results, double-check your sheet selection and confirm that you have not applied any filters or cell protections that might hide or restrict the search.

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. Enjoy permanently free AI features! Get It Now

Search a value in all workbooks of 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. Select the cell where you want the search results to appear (usually cell A1). Press Alt + F11 to launch the Microsoft Visual Basic for Applications editor window.

2. Within 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 a value across all workbooks of a folder.

Sub SearchFolders()
'UpdatebyKutoolsforExcel20200913
    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
    xAWBStrPath = xAWB.Path & "\" & xAWB.Name
    On Error GoTo ErrHandler
    Set xFileDialog = Application.FileDialog(msoFileDialogFolderPicker)
    xFileDialog.AllowMultiSelect = False
    xFileDialog.Title = "Select a forlder"
    If xFileDialog.Show = -1 Then
        xStrPath = xFileDialog.SelectedItems(1)
    End If
    If xStrPath = "" Then Exit Sub
    xStrSearch = "KTE"
    xUpdate = Application.ScreenUpdating
    Application.ScreenUpdating = False
    Set xOut = 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(xStrSearch)
                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.Cells.FindNext(After:=xFound)
                Loop While xStrAddress <> xFound.Address
                End If
            Next
            If Not xBol Then
            xWb.Close (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 the F5 key or click the Run button to execute this macro. A Select a folder dialog window will appear, allowing you to choose the folder that contains the workbooks you want to search through. See screenshot:

4. Click OK. After the search completes, a message box will inform you of 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.

Tip: The current search term is set in the VBA as “KTE”. You can customize this by changing the line xStrSearch = "KTE" in the code to any value you want to look for.

Precautions: Before running, make sure all relevant workbooks are saved and closed (except the one where you run your macro, which can be open). Large folders with many or complex Excel files may take some time to process. Do not interrupt the macro during execution.

Troubleshooting: If you encounter errors, verify that all files are genuine Excel workbooks (not corrupted or password-protected) and your macro security settings allow code to run. If the macro does not finish, try running it on a smaller set of files first.

Quickly search a value in multiple opened workbooks with Kutools for Excel

When you want to perform a search across several workbooks that are already open in your Excel session, 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, 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 of your opened workbooks, go to the Kutools tab and select Navigation. Then click the Find and Replace button  to bring up the Find and Replace pane, usually located at the left side of your Excel window. See screenshot:

2. In the Find tab, enter the value you want to search for into the Find what textbox. Choose All workbooks from the Within drop-down menu to perform the search across every open workbook. Then click Find All to instantly 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 specifically in selected sheets, the active workbook, current worksheet, or even in just your current selection. This gives you tailored control depending on your needs.

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

Troubleshooting: If some files do not appear in your search results, double-check that they are fully loaded in Excel and not in a protected view or read-only mode that might restrict search access.

Demo: Search Value Across Multiple Opened Workbooks with Kutools for Excel

Search 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 need for scripts or add-ins; all handled inside the workbook.
Disadvantages: Not suitable for situations where sheet names frequently change, or if you have a very large number of sheets.

Example scenario: Assume you have three sheets named Sheet1, Sheet2, and Sheet3. You want to know in which sheet(s) a specific value, such as "Invoice123", appears in cell A1, or to simply check if the value exists anywhere in those sheets.

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

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

Step2. 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 (e.g., A1:Z100) if needed.

Additional Tip: To retrieve the sheet name(s) containing the value, use the following array formula (entered with Ctrl + Shift + Enter in older Excel versions or as a regular formula in Microsoft 365/Excel 2021+):

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

This will return a comma-separated list of all sheet names where the value is found. Be careful with INDIRECT—it only works with open workbooks and does not allow for searching closed files.

Precautions: If sheet names are changed or deleted, the formula will return a #REF! error; always verify the sheet name list is correct. For larger workbooks, INDIRECT-based formulas may slow down your workbook's performance.

Troubleshooting: If you see errors, check that all referenced sheets exist and that your search range is correct. For dynamic sheet lists, consider using named ranges or Data Validation to keep the sheet list updated automatically.

Related Articles:

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