How to Create a Search Box in Excel for Multiple Sheets (2 Methods)

This article demonstrates how to create a search box in Excel that works across multiple sheets. Imagine you have a workbook with 20 sheets, and you need to search for data relevant to a specific keyword across all those sheets. Manually doing this would be time-consuming and tiresome. Fortunately, you can create a search box within your workbook to efficiently search through all the sheets. Let’s explore how to achieve that.

Method 1: Searching Multiple Sheets in Excel Using Conditional Formatting

Follow these steps to apply conditional formatting and quickly search through multiple sheets in Excel:

• Select the Range in Sheet2:
  • Begin by selecting the range B3:F21 in Sheet2.
  • Then, navigate to the Conditional Formatting option and choose New Rule from the Home tab.

• Define the Rule Type:
  • In the New Formatting Rule dialog, select Use a formula to determine which cells to format.
  • Enter the following formula in the text box provided for Rule Description:

• Choose Formatting Options:
  • Click on Format and select a fill color to highlight the relevant cells.
  • Confirm your settings by clicking OK.

• Apply Formatting to Other Sheets:
  • Repeat the same procedure for other data ranges in different sheets.
  • Ensure consistency by applying the same formatting rules.

• Enter a Keyword in Sheet1:
  • Go to Sheet1 and enter a keyword in cell B4.
  • Consider using wildcards (*, ?, etc.) in the search box if you don’t require exact matches.

• View the Results:
  • Switch to Sheet2 to see the relevant results highlighted based on your search criteria.

• • Similarly, check Sheet3 for a similar result.

By following these steps, you’ll have an efficient search box that spans multiple sheets in your Excel workbook.

Read More: How to Create Search Box in Excel with Conditional Formatting

Method 2 – Creating a Search Box in Excel for Multiple Sheets Using VBA

If you want to retrieve the highlighted data in the same sheet (Sheet1) where you’re searching for a keyword, follow these steps using VBA:

• Duplicate Your Dataset:
  • Make a copy of your dataset. This ensures that you won’t modify the original data during the process.

• Open the VBA Window:
  • Press ALT+F11 to open the Visual Basic for Applications (VBA) window.
• Insert a New Module:
  • In the VBA window, go to Insert > Module. This creates a new module where you can write your VBA code.
• Copy and Paste the Following Code:
  • Write or paste the VBA code that will create the search box functionality. You can customize this code based on your specific requirements.

Sub SearchMultipleSheets()
Main_Sheet = "VBA"
Search_Cell = "B5"
SearchType_Cell = "C5"
Paste_Cell = "B9"
Searched_Sheets = Array("Dataset 1", "Dataset 2")
Searched_Ranges = Array("B5:F23", "B5:F23")
Copy_Format = True
Last_Row = Sheets(Main_Sheet).Range(Paste_Cell).End(xlDown).Row
Last_Column = Sheets(Main_Sheet).Range(Paste_Cell).End(xlToRight).Column
Set Used_Range = Sheets(Main_Sheet).Range(Cells(Range(Paste_Cell).Row, Range(Paste_Cell).Column), Cells(Last_Row, Last_Column))
Used_Range.ClearContents
Used_Range.ClearFormats
Value1 = Sheets(Main_Sheet).Range(Search_Cell).Value
Count = -1
If Sheets(Main_Sheet).Range(SearchType_Cell).Value = "Case-Sensitive" Then
    Case_Sensitive = True
ElseIf Sheets(Main_Sheet).Range(SearchType_Cell).Value = "Case-Insensitive" Then
    Case_Sensitive = False
Else
    MsgBox ("Choose a Search Type.")
    Exit Sub
End If
For S = LBound(Searched_Sheets) To UBound(Searched_Sheets)
    Set Rng = Sheets(Searched_Sheets(S)).Range(Searched_Ranges(S))
    For i = 1 To Rng.Rows.Count
        For j = 1 To Rng.Columns.Count
            Value2 = Rng.Cells(i, j).Value
            If PartialMatch(Value1, Value2, Case_Sensitive) = True Then
                Count = Count + 1
                Rng.Rows(i).Copy
                Set Paste_Range = Sheets(Main_Sheet).Cells(Range(Paste_Cell).Row + Count, Range(Paste_Cell).Column)
                If Copy_Format = True Then
                    Paste_Range.PasteSpecial Paste:=xlPasteAll
                Else
                    Paste_Range.PasteSpecial Paste:=xlPasteValues
                End If
            End If
        Next j
    Next i
Next S
Application.CutCopyMode = False
End Sub

Function PartialMatch(Value1, Value2, Case_Sensitive)
Matched = False
For i = 1 To Len(Value2)
    If Case_Sensitive = True Then
        If Mid(Value2, i, Len(Value1)) = Value1 Then
            Matched = True
            Exit For
        End If
    Else
        If Mid(LCase(Value2), i, Len(Value1)) = LCase(Value1) Then
            Matched = True
            Exit For
        End If
    End If
Next i
PartialMatch = Matched
End Function

• • Return to the VBA worksheet.

• Create the Search button:
  • Insert a rectangle by selecting Insert > Illustrations > Shapes > Rectangle.
  • Change the Fill color and type Search to make it look like a button.

• Assign the Macro:
  • Right-click on the rectangular box, and from the context menu, select Assign Macro.

• • Choose the macro name and click OK.

• Run the Search:
• • In cell A4, type the search keyword.
  • Select Case-insensitive as the search type, and then click Search.

• • You will see all the relevant results that were highlighted earlier listed here. All the rows associated with the name Smith are now visible in the VBA worksheet.

Read More: Create a Search Box in Excel with VBA

Things to Remember

• Apply conditional formatting to the data in all sheets.
• Type the keyword in the exact cell referred to in the conditional formatting.
• Use wildcards when searching to get all possible matching results if you don’t need exact matches.
• Make a copy of your dataset before applying the code to avoid data loss.
• You can drag the Search button as needed.

Download Practice Workbook

You can download the practice workbook from here:

Related Articles

• How to Create a Search Box in Excel
• How to Create a Filtering Search Box for Your Excel Data
• How to Create a Search Box in Excel Without VBA

<< Go Back to Search Box in Excel | Data Management in Excel | Learn Excel