We may think that creating a Search Box in Excel without VBA is impossible. But it’s not true. In this article, we will learn how to create a Search Box in Excel without VBA. This article will guide you with some easy ways to do so. Let’s see them below.
Download Practice Workbook
Download the practice workbook from here.
Introduction to Search Box in Excel
In Microsoft Excel, the Search Box is a crucial tool for finding specific data when we work with a large amount of data. Using the Search Box, we can find any particular data in single or multiple worksheets and also highlight them.
2 Easy Ways to Create a Search Box in Excel Without VBA
Suppose, we have a dataset (B4:F15) in Excel that contains the Dates, Fruit names, Zones, Amount and Sales of some orders. Now, if you want to search for the information of any specific fruit then you can create a Search Box and highlight the row of the specific fruit. In the cell C17 we will input that we need to find. In this article, we will demonstrate 2 methods with some excellent examples and explanations to do so. So, without further delay, let’s get started.
1. Apply Excel Conditional Formatting to Create a Search Box Without VBA
In this method, we will apply Conditional Formatting in Excel to create a Search Box. We can find the Conditional Formatting option in the Home tab of any Excel worksheet.
1.1 Search Box for Single Excel Worksheet
In this method, we will discuss the method to create a Search Box in Excel within a single worksheet. Here, we will use the Excel Conditional Formatting feature to create the Search Box in cell C17 of the dataset (B5:F15) below. Let’s see the steps below for inserting the Search Box.
- First of all, go to cell C17 or the cell that will contain the Search Box.
- Secondly, go to the Home tab.
- Thirdly, click on the Conditional Formatting dropdown menu.
- Next, select New Rule from the dropdown.
- In turn, the New Formatting Rule window will appear.
- Subsequently, click on Use a formula to determine which cells to format from the Select a Rule Type section.
- Then, go to the Format values where this formula is true box.
- After that, to insert the Search Box type the formula in the box:
- Now, click on Format to format the searched cell or row to highlight.
In this formula, $C$17 denotes the location of the SEARCH Box and the $ sign is used for locking the cell. However, the B5, C5, D5, E5 and F5 refer to the columns from where the Search Box will search for the data (text or number).
- Therefore, the Format Cells dialog box will appear.
- In our case, we wanted to highlight the entire row of the searched data from the dataset.
- In this case, go to the Fill tab > Background Color > select any Color from the list > OK.
- Afterward, you will see the New Formatting Rule window again.
- Go to the Preview section and see if it shows the same formatting as you selected.
- Click OK.
- At this time, you will see the whole dataset B5:F15 (that you entered in the SEARCH function) with the formatting.
- Thereupon, type Apple in cell C17.
- In this way, only the rows (5, 9, 11, 13, 15) that contain Apple will remain highlighted with Red color.
- See the screenshot below.
1.2 Search Box for Multiple Excel Worksheets
We can also create a Search Box for multiple excel worksheets. The process for doing it is almost the same as the single worksheet but this time the SEARCH function will be different and we need to apply the Conditional Formatting for each worksheet. Assuming, we have an Excel workbook that contains two worksheets named MSheets1 & MSheets2. Here, we need to create a SEARCH BOX using Conditional Formatting which will be applied to both of these worksheets. The steps to do so are below.
- In the first place, go to the Home tab.
- Now, click on Conditional Formatting > New Rule.
- Hence, the New Formatting Rule window will pop up.
- After that, go to the Select a Rule Type > Use a formula to determine which cells to format.
- Then, to create the Search Box, keep the cursor in the Format values where this is true box and type the formula:
- Later, click on Format.
In the formula, MSheets1! refers to the MSheets1 worksheet where we will assign the Search Box and look for the data.
- As a consequence, the Format Cells dialog box will open.
- Accordingly, go to the Fill tab.
- Go to the Background Color section and select any color as you wish.
- Finally, click on the OK button.
- At this time, check the Preview section if the formatting is okay or not.
- If everything is okay then click the OK button.
- Next, you will see the whole dataset (B5:F15) of the MSheets1 worksheet with the formatting.
- Now, apply the same Conditional Formatting and the same SEARCH function to the MSheets2 worksheet.
- Again, the MSheets2 will show the same formatting in the whole dataset (B5:F15).
- Subsequently, go to MSheets1 > cell C17 > type Apple.
- Thus, all the rows containing Apple will remain highlighted with Yellow color in the MSheets1 worksheet.
- If you go to the MSheets2 worksheet then you will see the same formatting here also.
- In this way, we can create a Search Box in Excel using Conditional Formatting
Read More: Create a Search Box in Excel with VBA (5 Suitable Examples)
2. Create Dynamic Filter Search Box Without VBA Using Excel Functions
In this method, we will learn how to create a Dynamic Filter Search Box in Excel that will filter the data depending on what you enter in the Search Box. Here, we will use the Data tab, Formulas tab, Developer tab and some Excel functions to create this kind of dynamic Search Box. The steps are below.
First of all, we will create a unique list of the Fruit column from where we want to search our desired data. We will use this to create a dropdown.
- To begin, select the range (C4:C15) from where you want to search the desired data.
- Subsequently, right-click on the selection and click on Copy.
- Now, paste it into a new worksheet.
- After that, select the range B5:B15 > go to the Data tab.
- Next, Data Tools group > Remove Duplicates.
- In turn, the Remove Duplicates window will pop up.
- Therefore, go to Columns.
- Afterward, put a tick mark on the desired column for which you want to create the unique list.
- Click OK.
- Consequently, a message box will appear showing the number of duplicates removed and the number of remaining unique values.
- Click OK.
- Hence, you will see the unique values only in the Fruit column.
- At this time, select the range (B5:B9) > Formulas tab > Defined Names group > Define Name.
- As a result, the New Name window will appear.
- Thereupon, go to Name > type FruitList > OK.
After creating the unique list, you need to return to the previous or main worksheet and create the dynamic filter Search Box. In order to apply this technique, at first, we need to create a Search Box and then link the Search Box to a cell.
- To do so, go to the Developer tab > Controls group > Insert dropdown > ActiveX Controls > Combo Box.
- if you click at any place in the worksheet then the Combo Box will be inserted.
- Eventually, select Properties with a right-click on the Combo Box.
- Properties window > LinkedCell > C17 > ListFillRange > FruitList > MatchEntry dropdown > 2 – fmMatchEntryNone.
- Keeping selection on the Combo Box, go to the Developer tab again > Controls group > click on Design Mode.
- By doing so, you can exit the Design Mode and are free to enter any text in the Combo Box.
- From this point, anything you type would instantly appear in cell C17.
Lastly, it’s time for setting the data. We will connect everything using helper columns. For example, we have used two helper columns H1 & H2. For this, we will use the IF function, the ISNUMBER function, the SEARCH function & the SUM function in Excel respectively.
- In the beginning, go to the H1 column and put the serial number (1 to 11) for each record in the dataset.
- Secondly, select cell H5 of the H2 column.
- Thirdly, type the following formula in the cell (H5) to search for the desired data:
- After you typed the formula, press the Enter key.
- Drag the fill handle to copy the formula up to cell H15.
This formula will look for the data in the Search Box (connected to cell C17) in the cell that contains the name of the fruit. If a match is found, this formula shows the row number of the dataset; else, it displays a blank.
- For example, if you type Apple in the Search Box then it shows the row numbers 1, 5, 7, 9 & 11 containing Apple.
- Thus, we can create a dynamic filtering Search Box in Excel.
How to Find Number of Search Matches Using Array Formula in Excel
Here, we will demonstrate the process to get the number of matches for any specific data in Excel using an Array formula. Let’s say, in the dataset below we want the number of matches in the C17 column. The steps to find it are below.
- Firstly, go to cell C17.
- Now, to get the number of matches type the following formula in cell C17:
- Finally, you must press Ctrl + Shift + Enter to close this Array formula.
In the formula, the Apple is the desired data which we will look for in the C5:C15 range.
- Consequently, we can see the number of matches for Apple is 5 in cell C17.
Things to Keep in Mind
For creating a Search Box in Excel properly, we must maintain the points below:
- In the Conditional Formatting window, double-check that you entered the formula correctly.
- To verify that there is not any deviation, use the $ symbol just like in the formulas above.
- More columns are added to the formula by using the & sign. So, do not finish the formula with the & sign.
I hope this article will be helpful for you to create a Search Box in Excel without VBA. Here, we discussed creating a Search Box for multiple sheets also. Download the practice workbook and give it a try. Let us know your feedback in the comment section. Follow our website ExcelDemy to get more articles like this.