In this tutorial, you will learn how to make the advanced search bar in Excel that is capable to search for values in all columns. See below how the search bar works.
In the example, there are two sheets. One is “search_bar” and other is “source_data”. The data is being searched according to the value you type in the search bar. This is useful when you have to study only the data that is related with your search. From a big database, only related information displays in the sheet.
Follow these steps to do this.
- Prepare the data
- Make the search bar
- Write the formula to display searched items
Prepare the Data
Let’s assume we are making a search bar for sales data. Follow these steps in data preparation.
- Make two sheets, one is the source data for search (“source_data” in this example) and other for searched data (“search_bar” in this example).
- Change the source data to table format. Data in table will auto update the newly added values to formulas and search result. To convert data to table, select whole data, click on “Insert” tab, then “Table”. Select “my table has headers” option, then click “Ok”.
- Once table is created, click on the “table design” tab, then rename the table in “table name” box.
- Copy the column headers of source data to search_bar. While pasting the column headers, leave some blank rows at top to make the search bar.
Make the Search Bar
To Make the search bar, follow these steps.
- Click on “Insert” Tab. Click on Illustrations/Shapes/Rectangle: Rounded Corners
- Draw the shape, adjust the height and width by using the controls.
- Add an search icon from Insert/Illustrations/Icons then place it at the left corner of search bar.
- Add a Text Box (ActiveX Control) from Developer Tab’s Insert tool.
- Link cell A1 to the “Linked Cell” property of Text Box. (to open property, right click on the text box)
- Exit from the design mode by clicking on “Design Mode” tool in “Developer” tab.
for more clarity on making the search bar, see below image animation.
Write the Formula
In A8 cell (just below the first column header) writhe this formula to display the searched items.
=FILTER(my_data,NOT(ISERROR(SEARCH(A1,my_data[Date of Invoice]&”*“&my_data[Customer Name]&“*”&my_data[Product Name]&”*“&my_data[Qty Sold]&”*”&my_data[Sales Value]&”*“&my_data[Sales Representative]&”*”&my_data[Sales Manager]))),””)
Above formula does non-case sensitive search. If you need the case sensitive search, relpace the SEARCH function by FIND fucntion.
The * symbol is used to separate every single column values. If we do not use the separator, columns gets messed up. Hence, * symbol is required here.
Download the practice workbook fille from below.
