Excel Search Box (Table of Contents)
Search Box in Excel
Search Box in Excel is a customized function which is used to search anything in a worksheet and we can even highlight the search results as well. To create a search box in Excel, go Conditional Formatting which is under the Home menu ribbon and from the drop-down menu list create a new rule to use a formula for formatting a cell from there. In the rule box, use the SEARCH function in such a way that if put anything we want to search in Find Text option, it will highlight those value cells with the selected color within the selected text range.
How to Create Search Box in Excel
Creating a Search Box in Excel is very easy and convenient when it comes to searching for rows or filtering data with a specific criterion. By using Conditional Formatting and Formulas we can easily create Search Box in Excel.
Let’s understand how to create your own Search Box in Excel.
Usage of Search Box in Excel
Consider that you have the data of a company that deals in the sale and purchase of used vehicles. The amount of data can be enormous and it can be difficult to search for a particular car model name from the entire data. A search box can prove to be a useful tool in such a case and help you in highlighting the values that you might be looking for.
To begin with, let’s consider data as shown in the screenshot below:
- In Excel, Go to the cell in which you wish to create the search box. In this case, we have selected G1 as the search box cell. You can highlight the selected cell in order to easily distinguish it from other cells.
- Now select the entire data range from where you want the data or the value to be searched. The selected range in our case is A2: D27 (as shown in the image below)
- Once you have selected the entire data range, go to Conditional Formatting, select New Rule.
- On the next window, click on the option that says, Use a formula to determine which cells to format.
After this, enter the following formula in the text box just below it.
=SEARCH($G$1,$A2&$B2&$C2&$D2)
- After writing the above formula in the space provided, click on the Format button just below it to select the color in which the searched (and matched) values will be highlighted.
This can be done by selecting the Fill button on the next window and selecting the color.
4.9 (4,804 ratings)
View Course
- After this, click on OK and you will see that all the fields in your sheet are now highlighted with the color that you selected. A screenshot of the same is also given below.
- Now you can try and search for the desired keyword and you will see that all the rows with the word that you have searched for are now highlighted with the selected color.
For example, let’s try searching the item named Scooter. You’ll see that all the rows with the word scooter have been highlighted.
This simplifies the process of looking for any value. For example, now after the fields for Scooter have been highlighted, we can simply apply a filter on the color and refine our search results even further. Now let us try to understand the meaning of the parameters that we used in the Search formula and how it worked for us in Excel.
Explanation of Search Box in Excel
Let us now try to understand what each parameter within the formula of Search means in Excel. Consider the formula that we used.
=SEARCH($G$1, $A2&$B2&$C2&$D2)
Let’s have look at each parameter individually.
- $G$1
=SEARCH($G$1, $A2&$B2&$C2&$D2)
This parameter defines the location where we wish to apply the search rule. We can either type in the cell number or we can simply click on the cell where we want the search box after we have written =SEARCH in the conditional formatting rule window.
2.$A2&
=SEARCH($G$9,$A2&$B2&$C2&$D2)
This parameter, ‘A’ specifies the column from which data is to be searched and ‘2’ specifies the initial position. So ‘A2’ means that we wish to search for the data in column ‘A’ starting from the 2nd row. Adding $ sign before ‘A’ ensures that the search location remains fixed even if we change the location of the search box.
Affixing the ‘&’ sign after ‘A2’ means that we wish to add more locations from where data is to be searched. We can then specify all the search locations in the same manner as we specified for ‘A2’.
This can be seen in the formula:
=SEARCH($G$9,$A2&$B2&$C2&$D2)
Example of Search Box in Excel Usage
By now we have been able to develop a clear understanding of how to implement a Search Box in Excel. Now let us see some examples where we can use a search box and get refined results. Consider the previous example where we had searched for the word ‘Scooter’ in our Excel sheet and all the fields having the word ‘Scooter’ were highlighted. Now, what will we do if we wish to see the brand names of the Scooters that were sold in the year 2008?
Well, the answer to this question is quite simple. We can simply apply a filer now in the Year of Sale field. Let us have a look at the screenshot attached below.
We get the following result once we apply the filter and click OK. There are 2 brands whose scooter were sold in the year 2008 as shown below.
This is how a Search Box in Excel can help us in simplifying the search process.
We can even use filters to perform a search as we did above but then we would need to apply multiple filters in order to look for multiple things. Moreover, the example that we saw here had limited data. There may be cases when the amount of data present in a sheet is huge. A Search Box can help us in all such situation as it creates a search criterion for the entire sheet.
Things to Remember
- Make sure that you enter the formula correctly in the conditional formatting window.
- Use the $ sign as shown in the formula above to ensure that there is no deviation.
- The & sign is used to add more columns in the formula. Make sure that you do not put the & sign at the end of the formula.
- We can use a filter to solve the purpose of a Search Box in Excel but we may need to apply filters to several columns in order to solve that purpose.
- Though both, Search Box and Filters are used to fetch outputs based on various conditions, we should not use them interchangeably as both solve unique purposes and in different manners. A Search Box in Excel can be used to enhance the function of a filter.
Recommended Articles
This has been a guide to Search Box in excel. Here we discuss How to Create Search Box in excel and Usage of Search Box in Excel along with practical examples and downloadable excel template. You can also go through our other suggested articles –
