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

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:

Sample dataset to show how to create a search box in excel for multiple sheets

  • Select the range B3:F21 in Sheet2.
  • Navigate to the Conditional Formatting option and choose New Rule from the Home tab.

Apply conditional formatting

  • 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:
=SEARCH(Sheet1!$B$4,$B3&$C3&$D3&$E3&$F3)
  • Click on Format and select a fill color to highlight the relevant cells.
  • Confirm your settings by clicking OK.

Conditional formatting rule to create the search box

  • Repeat the same procedure for other data ranges in different sheets.
  • Ensure consistency by applying the same formatting rules.
  • 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.

Entering Keyword in the Search Box

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

Relavant search results in Sheet2

  • Check Sheet3 for a similar result.

Relavant search results in Sheet3

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:

  • Make a copy of your dataset. This ensures that you won’t modify the original data during the process.
  • Press ALT+F11 to open the Visual Basic for Applications (VBA) window.
  • In the VBA window, go to Insert > Module. This creates a new module where you can Insert your VBA code.
  • Insert or paste the VBA code that will create the search box functionality. You can customize this code based on your specific requirements.

Inserting New Module in VBA

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

VBA Code to Search

  • Return to the VBA worksheet.
  • Insert a rectangle by selecting Insert, clicking on Illustrations, choosing Shapes and selecting Rectangle.
  • Change the Fill color and type Search to make it look like a button.

Create a Search Box in Excel for Multiple Sheets

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

Assigning macro to a button

  • Choose the macro name and click OK.

Selecting macro to run

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

Get Result from Search Box in Excel for Multiple Sheets

  • 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


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

Get FREE Advanced Excel Exercises with Solutions!
Md. Shamim Reza
Md. Shamim Reza

Md. Shamim Reza, a marine engineer with expertise in Excel and a fervent interest in VBA programming, sees programming as a time-saving tool for data manipulation, file handling, and internet interaction. His diverse skill set encompasses Rhino3D, Maxsurf C++, AutoCAD, Deep Neural Networks, and Machine Learning. He holds a B.Sc in Naval Architecture & Marine Engineering from BUET and has transitioned into a content developer role, generating technical content focused on Excel and VBA. Beyond his professional pursuits,... Read Full Bio

14 Comments
  1. Trying to do this…this statement isn’t clear to me. “Now, move the datasets to cell A1 and then save the file as a macro-enabled workbook.” If I have multiple sheets, how do I move datasets to Cell A1? On Which sheet?

    • Hello Brian,

      Thanks for reaching out to us. Well, you can ignore that step I think. Have you tried to run the code on your dataset?

      Actually, I’d added the step because VBA was acting a little weird if there was a blank column at the beginning. Maybe because of the .EntireRow statement.

      So, why don’t you run the code ignoring that step and see if it works? Thanks.

      Regards
      Md. Shamim Reza (ExcelDemy Team)

  2. My results don’t show up on the first sheet when following these directions. I copy and pasted all the code. Could it be my variables are off?

    • Hello Robert,

      I’ve checked the code again and it is working fine. Perhaps you haven’t used any wildcards and there was no exact match to the search value. Otherwise, you haven’t used the wildcards properly.

      And can you please clarify what you mean by “variables are off”? Thanks.

      Regards
      Md. Shamim Reza (ExcelDemy Team)

  3. Great code!! Just have a question: My cells have multiple words and it seems the code is only looking in the last word of each cell. Is there a workaround for this? so that is searches every word on the cells?

    Thans in advance

  4. Dear BZOIRO,

    Yes, you can modify the SearchAll function in the given VBA code to search for each word in the cells separately. I have added the modified code in the Excel file below. This modification splits the search value into words using the Split function and loops through each word to find it in the range. If all words are found, the function merges the found ranges into a single range and thoroughly returns it. If any of the words are not found, the function exits and returns Nothing. Here’s how to use this for your case:

    1. Initially, assign Macro “SearchMultipleSheetsIndividually” in the Search command box and click Run.
    Assigning Macro
    2. Afterward, type the word you wish to look for. Also, don’t forget to use the Wildcards (*, ? etc) while searching. For instance, if you wish to look for a cell of 3 words and has “Emily” in the middle, type *Emily* and then perform searching. Alternatively, if it happens to be the first word, try Emily*.
    Search Results

    Download the Excel file to get the modified VBA code and practice by yourself. I hope it works for you.
    https://www.exceldemy.com/wp-content/uploads/2023/01/Search-Box-for-Multiple-Sheets.xlsm
    Best Regards,
    Yousuf Khan (ExcelDemy Team)

  5. Hi Sir,

    we need a help to change the (all.EntireRow.Copy dst) to only copy selected Column (A,C,D,P, etc).

    Basically i want show data only specific columns (A,C,D,P, etc). after search.

    thank you

    • Reply Mahfuza Anika Era
      Mahfuza Anika Era Feb 25, 2024 at 4:23 PM

      Hello YOGESH UTEKAR,
      Thank you for your comment. To show the data for specific columns (For example: A,C,D,I,P) instead of the entire row you can use the following code.

      
      Option Explicit
      
      Sub SearchMultipleSheets()
          Dim wsh As Worksheet, ash As Worksheet
          Dim all As Range, dst As Range
          Dim What As Variant
          Dim columnRange As Range
          
          Set ash = ActiveSheet
          Set dst = ash.Range("A6")
          What = ash.Range("A4").Value
          
          Application.ScreenUpdating = False
          dst.CurrentRegion.Clear
          
          For Each wsh In Worksheets
              If wsh.Name = ash.Name Then GoTo NextSheet
              Set all = SearchAll(wsh.Columns("A"), What, LookAt:=xlWhole)
              If Not all Is Nothing Then
                  Set columnRange = Union(wsh.Range("A" & all.Row), wsh.Range("C" & all.Row), wsh.Range("D" & all.Row), wsh.Range("I" & all.Row), wsh.Range("P" & all.Row)) ' Add more columns as needed
                  columnRange.Copy dst.Resize(1, columnRange.Columns.Count)
                  Set dst = dst.Offset(1, 0)
              End If
      NextSheet:
          Next
          
          Application.ScreenUpdating = True
      End Sub
      

      I have highlighted the portions where I have made changes.

      vba code

      Best Regards,
      Mahfuza Anika Era (ExcelDemy Team)

  6. Hi! I watched your video and read through your article in regards to how to create a search box utilizing VBA code.
    This video was very informative and helped me understand the code well enough to apply it to my own conditions, outside of one area. The line item under PartialMatch “For i = 1 To Len(Value2)” I am getting an Error 13 of mixed match type. I’ve read through some forums and tried to understand the error code, but can’t figure out where inside the code this would trigger an issue with my spreadsheet.

    • Reply Lutfor Rahman Shimanto
      Lutfor Rahman Shimanto Jun 6, 2024 at 11:45 AM

      Hello Brandy

      Thanks for your wonderful compliment! Your appreciation means a lot to us.

      The Error 13 Type Mismatch in VBA typically occurs when you try to perform an operation on incompatible data types. I have reviewed the code and found that Value2 is used to contain cell values when looping through and comparing with Value1. It seems like some of your values contain errors, which is why it is not possible to use the Len function with this value. So, to avoid this type of situation, you can use IsError to check whether the cells contain any errors or not. If not, perform an operation; otherwise, do nothing.

      You can use the following structure:

      If Not IsError(Rng.Cells(i, j).Value) Then
          'Perform operations
      End If

      Hopefully, you have found the ideas you were looking for. Good luck.

      Regards
      Lutfor Rahman Shimanto
      Excel & VBA Developer
      ExcelDemy

  7. Hello,

    Hoping you can assist. I get an error message of “Run-time error ‘9’: Subscript out of range”. When go to debug it comes up with this line as error:

    Set Rng = Sheets(Searched_Sheets(S)).Range(Searched_Ranges(S))

    Can you please assist? I have multiple data pages to search.

    • Hello Michelle,

      The “Run-time error ‘9’: Subscript out of range” occurs when a specified sheet or range does not exist. Please check the sheet names and ranges in your Searched_Sheets and Searched_Ranges arrays are correctly spelled and match exactly with your Excel workbook.

      I made some changes in the code to check whether sheets and ranges exist in your Excel workbook.
      Make sure to update this according to your Excel workbook:
      Searched_Sheets = Array(“Dataset 1”, “Dataset 2”) # Update it with your sheet name.
      Searched_Ranges = Array(“B5:F23”, “B5:F23”) # Update it with your ranges.

      Added SheetExists function it will check if a sheet exists before accessing it.
      Error Handling will check for the existence of sheets to prevent “Subscript out of range” errors.

      Sub SearchMultipleSheets()
          Dim Main_Sheet As String
          Dim Search_Cell As String
          Dim SearchType_Cell As String
          Dim Paste_Cell As String
          Dim Searched_Sheets As Variant
          Dim Searched_Ranges As Variant
          Dim Copy_Format As Boolean
          Dim Last_Row As Long
          Dim Last_Column As Long
          Dim Used_Range As Range
          Dim Value1 As String
          Dim Case_Sensitive As Boolean
          Dim Count As Long
          Dim S As Integer
          Dim i As Long
          Dim j As Long
          Dim Rng As Range
          Dim Value2 As String
          Dim Paste_Range As Range
      
          ' Initialize variables
          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
      
          ' Clear the previous results
          Last_Row = Sheets(Main_Sheet).Cells(Range(Paste_Cell).Row, Range(Paste_Cell).Column).End(xlDown).Row
          Last_Column = Sheets(Main_Sheet).Cells(Range(Paste_Cell).Row, Range(Paste_Cell).Column).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
      
          ' Get the search value and type
          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
      
          ' Search through the sheets and ranges
          For S = LBound(Searched_Sheets) To UBound(Searched_Sheets)
              If SheetExists(Searched_Sheets(S)) Then
                  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
              Else
                  MsgBox "Sheet " & Searched_Sheets(S) & " not found."
              End If
          Next S
          Application.CutCopyMode = False
      End Sub
      
      Function PartialMatch(Value1 As String, Value2 As String, Case_Sensitive As Boolean) As Boolean
          Dim Matched As Boolean
          Dim i As Long
      
          Matched = False
          For i = 1 To Len(Value2)
              If Case_Sensitive 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
      
      Function SheetExists(ByVal SheetName As String) As Boolean
          On Error Resume Next
          SheetExists = Not Sheets(SheetName) Is Nothing
          On Error GoTo 0
      End Function

      Regards
      ExcelDemy

  8. Hello,
    I have successfully used your code. Is there a way in which if you entered no text into the search box that no results populate? If I do it now it brings up everyone which causes a long lag as I have a lot of data across multiple sheets. Thanks

    • Hello Michelle,

      Yes, you can add a check in the VBA script to see if the search box is empty before proceeding with the search. If the search box is empty, the subroutine can exit early without performing any operations, preventing the script from unnecessarily processing all the data and causing lag.

      Here, added IsEmpty() and a simple comparison to an empty string (“”) to check if the Search_Cell is empty before running the rest of the code.
      If the search cell is empty, it displays a message box alerting the user and exits the subroutine early with Exit Sub.

      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
      
          ' Check if the search box is empty
          If IsEmpty(Sheets(Main_Sheet).Range(Search_Cell).Value) Or Sheets(Main_Sheet).Range(Search_Cell).Value = "" Then
              MsgBox "Search box is empty. Please enter a value to search.", vbExclamation
              Exit Sub
          End If
      
          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
      

      Regards
      ExcelDemy

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo