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

This article illustrates how to create a search box in Excel for multiple sheets. Assume you have 20 sheets in your workbook. Now you need to search for data in all those sheets relevant to a keyword. It will be very time-consuming and tiresome to do this manually. Fortunately, you can create a search box for your workbook to search through all sheets. Follow the article to learn how to do that.

Watch Video – Create a Search Box for Multiple Sheets in Excel

How to Create a Search Box in Excel for Multiple Sheets: 2 Ways

Assume you have data as follows in multiple sheets. You need to search by names to quickly find all the relevant results in those sheets.

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

Follow the methods below to be able to do that.

1. Search Multiple Sheets in Excel with Conditional Formatting

Follow the steps below to apply conditional formatting to quickly search through multiple sheets in Excel.


  • First, select the range B3:F21 in Sheet2. Then, select Conditional Formatting >> New Rule from the Home tab.

Apply conditional formatting

  • Next, select ‘Use a formula to determine which cells to format’ as the rule type. After that, enter the following formula in the text box below for Rule Description.
  • Next, click on Format and choose a Fill color to highlight the cells. Then click OK.

Conditional formatting rule to create the search box

  • After that, apply the same formatting to the data ranges in other sheets by following the same procedure.
  • Now go to Sheet1 and enter a keyword in cell B4. It is better to use wildcards (*, ?, etc.) in the search box if you don’t need exact matches.

Entering Keyword in the Search Box

  • Then go to Sheet2 to see the following result. You can see the relevant results highlighted.

Relavant search results in Sheet2

  • Next, go to Sheet3 to see a similar result as follows.

Relavant search results in Sheet3

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

2. Create a Search Box in Excel for Multiple Sheets with VBA

Now you might be thinking about getting those highlighted data in the same sheet i.e. Sheet1 where you search for the keyword. Follow the steps below to be able to do that with VBA.


  • First, make a copy of your dataset, and then press ALT+F11 to open the VBA window. Then select Insert >> Module.

Inserting New Module in VBA

  • Next, copy the following code.
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))
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
    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
                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
                    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
        If Mid(LCase(Value2), i, Len(Value1)) = LCase(Value1) Then
            Matched = True
            Exit For
        End If
    End If
Next i
PartialMatch = Matched
End Function
  • After that, paste the code onto the module.

VBA Code to Search

  • Next, return to the VBA worksheet and insert a rectangle by selecting Insert > Illustrations > Shapes > Rectangle. After that, change the Fill color and type Search to make it look like a button.

Create a Search Box in Excel for Multiple Sheets

  • Now, right-click on the rectangular box, and from the context menu select ‘Assign Macro’.

Assigning macro to a button

  • Then, select the macro name and click OK.

Selecting macro to run

  • Next, type the search keyword in cell A4, select Case-insensitive as the Search type, and then click on the Search.
  • After that, you will see all the relevant results that were highlighted in the earlier method are listed here. All the rows associated with the name Smith are now visible in the VBA worksheet.

Get Result from Search Box in Excel for Multiple Sheets

Read More: Create a Search Box in Excel with VBA

Things to Remember

  • You must apply conditional formatting to the data in all sheets. And don’t forget to type the keyword in the exact cell referred to in the conditional formatting.
  • Don’t forget to search with wildcards to get all possible matching results if you don’t need exact matches.
  • You must make a copy of your dataset before applying the code. Otherwise, you may lose your data.
  • You can drag the Search button to move it as required.

Download Practice Workbook

You can download the practice workbook from the download button below.


Now you know how to create a search box for multiple sheets in Excel. Have you been able to apply the methods to your datasets? Do you have any further queries or suggestions? Please let us know in the comment section below.

Related Articles

<< Go Back to Search Box in Excel | Data Management 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

  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.

      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.

      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.
    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
          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
          Application.ScreenUpdating = True
      End Sub

      I have highlighted the portions where I have made changes.

      vba code

      Best Regards,
      Mahfuza Anika Era (ExcelDemy Team)

Leave a reply

Advanced Excel Exercises with Solutions PDF