How to Create a Search Bar in Excel (in two minutes)

Introduction

In this tutorial, you'll learn how to create a functional search bar in Excel that allows you to quickly filter large datasets. This search bar helps you find specific values effortlessly, making it an impressive feature to showcase in your projects. Best of all, you won’t need any VBA coding skills to implement this!

Step 1: Add the Developer Tab

To start creating your search bar, you first need to enable the Developer tab in Excel.

• Open Excel and go to the File menu.
• Select Options.
• In the Excel Options window, click on Customize Ribbon.
• In the right pane, check the box for Developer to add the tab to your ribbon.
• Click OK to apply the changes.

Step 2: Insert a Text Box

Next, you will insert a text box where users can input their search queries.

• Go to the Developer tab on the ribbon.
• Click on Insert in the Controls group.
• Select the Text Box from the ActiveX Controls section.
• Click and drag on your worksheet to create the text box.

Step 3: Link the Text Box

Now, link the text box to a specific cell to capture the input value.

• Right-click on the text box you just created and select Properties.
• In the Properties window, find the LinkedCell option.
• Enter the reference of the cell you want to link, for example, A1.
• Close the Properties window.

Step 4: Use FILTER, SEARCH, and ISNUMBER Functions

With the text box linked, you can now use Excel functions to filter your data based on the input.

1. Assume your dataset is in the range B2:B100.
2. In another area of your worksheet, use the following formula to filter the data:

   =FILTER(B2:B100, ISNUMBER(SEARCH(A1, B2:B100)), "No matches found")
   

   • This formula searches for the value entered in the text box (linked to cell A1) within the range B2:B100.
   • It returns rows that contain the searched value, or "No matches found" if there are none.

Conclusion

You have successfully created a search bar in Excel that filters data based on user input. This functionality not only enhances usability but also serves as an impressive feature to demonstrate your Excel skills. As a next step, consider customizing the layout or adding additional features to improve user interaction. Happy Exceling!