How to Build Search Box in Excel

Introduction

In this tutorial, you'll learn how to build a fully functional search box in Excel without any coding knowledge. This search box will allow you to find specific items in your data, like searching for chocolate chip cookies in a customer list. We will cover exact matches, partial matches, multiple column searches, and even highlight search terms using conditional formatting.

Step 1: Enable Developer Tab

To start, you need to access the Developer tab in Excel, which provides tools for creating forms and controls.

1. Open Excel.
2. Go to the File menu.
3. Select Options.
4. In the Excel Options dialog, select Customize Ribbon.
5. In the right panel, check the box for Developer.
6. Click OK to enable the Developer tab.

Step 2: Add Basic Exact Match Search Box

Next, you will create a basic search box that returns exact matches.

1. Go to the Developer tab.

2. Click on Insert in the Controls section.

3. Choose a Text Box from the ActiveX Controls.

4. Draw the Text Box on your worksheet.

5. Right-click on the Text Box and select Properties.

6. Change the Name property to "txtSearch" and the LinkedCell property to a cell where you want to display the search term.

7. Close the Properties window.

8. Next, create a button for executing the search.

   • Go to the Developer tab.
   • Click on Insert and choose a Button (ActiveX Control).
   • Draw the Button on your worksheet.
   • Right-click the Button, select Properties, change the Caption to "Search," and then close the window.

9. Double-click the Button to open the code editor and enter the following code:

   Private Sub CommandButton1_Click()
       Dim searchTerm As String
       Dim cell As Range
       Dim found As Range
   
       searchTerm = txtSearch.Text
       Set found = Nothing
   
       For Each cell In Range("A1:A100") ' Adjust the range as needed
           If cell.Value = searchTerm Then
               Set found = cell
               Exit For
           End If
       Next cell
   
       If Not found Is Nothing Then
           found.Select
       Else
           MsgBox "Item not found."
       End If
   End Sub
   

10. Close the code editor and return to the Excel worksheet.

Step 3: Add Partial Match Search Box

Now, let's modify the search box to allow for partial matches.

1. Open the code editor for the Button you created earlier.
2. Replace the existing code with the following:

   Private Sub CommandButton1_Click()
       Dim searchTerm As String
       Dim cell As Range
       Dim found As Range
       Dim firstAddress As String
   
       searchTerm = "*" & txtSearch.Text & "*"
       Set found = Range("A1:A100").Find(What:=searchTerm, LookIn:=xlValues, LookAt:=xlPart)
   
       If Not found Is Nothing Then
           firstAddress = found.Address
           Do
               found.Select
               Set found = Range("A1:A100").FindNext(found)
           Loop While Not found Is Nothing And found.Address <> firstAddress
       Else
           MsgBox "Item not found."
       End If
   End Sub
   

3. Close the code editor.

Step 4: Add Multiple Column Search Box

To enhance the search functionality further, you can set up a search that checks multiple columns.

1. Open the code editor for the Button again.
2. Modify the code to check multiple columns:

   Private Sub CommandButton1_Click()
       Dim searchTerm As String
       Dim cell As Range
       Dim found As Range
       Dim firstAddress As String
       Dim searchRange As Range
   
       searchTerm = "*" & txtSearch.Text & "*"
       Set searchRange = Range("A1:C100") ' Specify your range here
       Set found = searchRange.Find(What:=searchTerm, LookIn:=xlValues, LookAt:=xlPart)
   
       If Not found Is Nothing Then
           firstAddress = found.Address
           Do
               found.Select
               Set found = searchRange.FindNext(found)
           Loop While Not found Is Nothing And found.Address <> firstAddress
       Else
           MsgBox "Item not found."
       End If
   End Sub
   

3. Close the code editor.

Step 5: Use Conditional Formatting to Highlight Partial Matches

Finally, you can highlight the found items to make them stand out.

1. Select the range you wish to format (e.g., A1:C100).
2. Go to the Home tab, click on Conditional Formatting, then New Rule.
3. Choose "Use a formula to determine which cells to format."
4. Enter the following formula:

   =SEARCH($E$1, A1) ' Assuming E1 is where your search term is
   

5. Click Format and choose a highlight color.
6. Click OK to apply the formatting.

Conclusion

You have now successfully created an advanced search box in Excel that allows for exact and partial matches across multiple columns. Additionally, you've learned how to highlight search results using conditional formatting.

Next steps include experimenting with the search box on your own data and exploring more complex Excel functionalities to further enhance your data management skills.