We’ve all used Control up at some point in our Excel to search a dataset. While in this Blog, we are going to show you a cool alternative that is guaranteed to impress you. We are going to learn how to insert a search bar in excel into a worksheet that searches data and returns all matches.
Why Use a Search Bar in Excel?
Before diving into the how-to, let’s first understand the importance of a search bar in Excel. With vast amounts of data stored in spreadsheets, finding specific information can be time-consuming and tedious. A search bar simplifies this process by allowing users to quickly search for keywords or phrases within the dataset, saving valuable time and effort.
Creating a Search Bar in Excel
In today’s fast-paced world, efficiency is key, especially when it comes to managing data in Excel. One powerful tool that can streamline your workflow is the search bar. In this blog post, we’ll explore how to create a search bar in Excel, empowering you to quickly locate and analyze data with ease.
Let’s get started. Here we have a sales dataset with thousands of rows and need to quickly search for representatives. Instead of using the Find tool to search for the rep and then cycle through all of the matches, we can insert a search bar into a worksheet that searches the data and returns any value containing the text.
Prepare Your Data
To insert a search bar, first we need to insert a text box from the Developer tab. If you don’t have the Developer tab in your workbook, right -click the ribbon, select Customize the ribbon, check the Developer tab in the main tabs list, and hit OK.
Now open the Insert drop -down, select Text Box, insert the text box into the worksheet by clicking and dragging, and then move it to where you want the search bar to be located. Next, we need to link the text box to a cell so that we can reference it in formulas.
To do this, right -click the text box, select Properties, and then enter the cell in the box next to Link Cell. I recommend linking the text box to a cell behind it to prevent confusion, so I’m going to enter J2 and then exit out of the Properties box.
Now that we’ve inserted and linked the text box, we can exit Design Mode by unselecting the Design Mode command in the Developer tab of the ribbon. The last step is to use the filter function to filter the dataset on any representative that contains the text entered in the search bar, so select where you want to list the matches, enter the filter function, and select the entire dataset as the array argument.
Next, enter this boolean statement as the include argument to return the rows that contain the text in the search bar, within the representative column. Let’s break this down. The search function searches each representative for the value entered in the search bar and then returns the number of the starting position of the value if it finds a match or an error if it can’t find a match.
The isNumber function converts the search function’s results into two or false values so that the filter function can read the results. It returns true if the search function resulted in a number indicating that there’s a match or false if it did not result in a number indicating no match.
Lastly, we can use the ifEmpty argument to return a default value of none if there are no matches. Now if we enter Mike’s name into the search box, all of the representatives that are named Mike are returned.
Test Your Search Bar
That’s it! Your search bar in Excel is now ready to use. Enter a search term into the text box and click the search button to locate matching data within your spreadsheet.
If we enter Smith, all the reps with the last name Smith are returned. If we enter a name, all of the data of the person from sales are returned and so on. To wrap up, we covered how to insert a search bar into our worksheet so that we never have to cycle through matching entries in the fine tool again.
In conclusion, creating a search bar in Excel is a simple yet powerful way to enhance productivity and efficiency when working with data. By following the steps outlined in this blog post, you can unlock the full potential of Excel’s search functionality and streamline your data analysis workflow. So why wait? Give it a try today and experience the benefits of a search bar in Excel for yourself!
You can watch this trick on YouTube: Click Here:- Search bar in Excel