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.


Download Practice Workbook

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


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

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.

📌 Steps:

  • 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.
=SEARCH(Sheet1!$B$4,$B3&$C3&$D3&$E3&$F3)
  • 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


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.

📌 Steps:

  • 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.
Option Explicit
Sub SearchMultipleSheets()
  Dim wsh As Worksheet, ash As Worksheet
  Dim all, dst As Range
  Dim Wht As Variant
  Set ash = ActiveSheet
  Set dst = ash.Range("A6")
  Wht = 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"), Wht, LookAt:=xlWhole)
    If all Is Nothing Then GoTo NextSheet
    all.EntireRow.Copy dst
    Set dst = ash.Range("A" & Rows.Count).End(xlUp).Offset(1)
NextSheet:
  Next
  Application.ScreenUpdating = True
End Sub
Function SearchAll(ByVal Where As Range, ByVal What, _
    Optional ByVal After As Variant, _
    Optional ByVal LookIn As XlFindLookIn = xlValues, _
    Optional ByVal LookAt As XlLookAt = xlWhole, _
    Optional ByVal SearchOrder As XlSearchOrder = xlByRows, _
    Optional ByVal SearchDirection As XlSearchDirection = xlNext, _
    Optional ByVal MatchCase As Boolean = False, _
    Optional ByVal SearchFormat As Boolean = False) As Range
  Dim First_Address As String
  Dim R As Range
  Dim Stck As New Collection
  Dim Tmp() As Range, Item
  Dim m, n As Long
  If Where Is Nothing Then Exit Function
  If SearchDirection = xlNext And IsMissing(After) Then
    Set R = Where.Areas(Where.Areas.Count)
    Set After = R.Cells(R.Rows.Count * CDec(R.Columns.Count))
  End If
  Set R = Where.Find(What, After, LookIn, LookAt, SearchOrder, _
    SearchDirection, MatchCase, SearchFormat:=SearchFormat)
  If R Is Nothing Then Exit Function
  First_Address = R.Address
  Do
    Stck.Add R
    If SearchFormat Then
      Set R = Where.Find(What, R, LookIn, LookAt, SearchOrder, _
        SearchDirection, MatchCase, SearchFormat:=SearchFormat)
    Else
      If SearchDirection = xlNext Then
        Set R = Where.FindNext(R)
      Else
        Set R = Where.FindPrevious(R)
      End If
    End If
    If R Is Nothing Then Exit Do
  Loop Until First_Address = R.Address
  ReDim Tmp(0 To Stck.Count - 1)
  m = 0
  For Each Item In Stck
    Set Tmp(m) = Item
    m = m + 1
  Next
  n = 1
  Do
    For m = 0 To UBound(Tmp) - n Step n * 2
      Set Tmp(m) = Union(Tmp(m), Tmp(m + n))
    Next
    n = n * 2
  Loop Until n > UBound(Tmp)
  Set SearchAll = Tmp(0)
End Function
  • After that, paste the code onto the module.

Code Module to create a Search Box

  • Now, move the data in each sheet to cell A1 if possible and then save the file as a macro-enabled workbook. This is because VBA may act a little weird if there is a blank column at the beginning of the dataset. You may skip this step and see if it works.
  • Next, go back to Sheet1 and then insert a rectangle by selecting Insert >> Illustrations >> Shapes >> Rectangle. After that, change the Fill color and type Search to make it look as required.

Inserting a rectangle for the search button

  • Now, right-click on the rectangular box to select ‘Assign a macro’. Then, select the macro name and click OK.

Assigning macor to the search button

  • Next, type the search keyword in cell A4 and then click on the Search button. After that, you will see all the relevant results that were highlighted in the earlier method listed below.

Relavant results extracted from all sheets

VBA Code Explanation:

Option Explicit
It forces you to declare all variables.

Sub SearchMultipleSheets()
We will write the code inside this subject procedure.

Dim wsh As Worksheet, ash As Worksheet
Dim all, dst As Range
Dim What As Variant
Declaring necessary variables.

Set ash = ActiveSheet
Set dst = ash.Range(“A6”)
Wht = ash.Range(“A4”).Value
Defining variables.

Application.ScreenUpdating = False
This makes VBA run in the background to work faster.

dst.CurrentRegion.Clear
Clears anything remaining in the output region.

For Each wsh In Worksheets
Loop through each sheet.

If wsh.Name = ash.Name Then GoTo NextSheet
Skips the active sheet.

Set all = SearchAll(wsh.Columns(“A”), What, LookAt:=xlWhole)
Call the SearchAll function defined below.

If all Is Nothing Then GoTo NextSheet
Goes to the next sheet if nothing is found in the current sheet.

all.EntireRow.Copy dst
Copies the entire row of the matched cell.

Set dst = ash.Range(“A” & Rows.Count).End(xlUp).Offset(1)
Sets column A as the output range.

NextSheet:
The GoTo statement directs VBA to run from here.

Next
Goes to the next sheet to repeat the task.

Application.ScreenUpdating = True
Sets screen updating to its default.

End Sub
The procedure ends here.

Function SearchAll(ByVal Where As Range, ByVal What, _
Optional ByVal After As Variant, _
Optional ByVal LookIn As XlFindLookIn = xlValues, _
Optional ByVal LookAt As XlLookAt = xlWhole, _
Optional ByVal SearchOrder As XlSearchOrder = xlByRows, _
Optional ByVal SearchDirection As XlSearchDirection = xlNext, _
Optional ByVal MatchCase As Boolean = False, _
Optional ByVal SearchFormat As Boolean = False) As Range
This function is called by the subprocedure.

Dim First_Address As String
Dim R As Range
Dim Stck As New Collection
Dim Tmp() As Range, Item
Dim m, n As Long
Declaring variables for the function.

If Where Is Nothing Then Exit Function
The function ends without returning anything if the search box is empty.

Read More: How to Create a Search Box in Excel Without VBA (2 Easy Ways)


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.

Conclusion

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. You can also visit our ExcelDemy blog to explore more about excel. Stay with us and keep learning.


Related Articles

Md. Shamim Reza

Md. Shamim Reza

Hello there! This is Md. Shamim Reza. Working as an Excel & VBA Content Developer at ExcelDemy. We try to find simple & easy solutions to the problems that Excel users face every day. Our goal is to gather knowledge, find innovative solutions through them and make those solutions available for everybody. Stay with us & keep learning.

4 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)

Leave a reply

ExcelDemy
Logo