How to Create a Search Box in Excel Without VBA (2 Easy Ways)

Get FREE Advanced Excel Exercises with Solutions!

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.


Watch Video – Create a Search Box in Excel


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 highlight them.


Create a Search Box in Excel Without VBA: 2 Easy Ways

Suppose, we have a dataset (B4:F15) in Excel that contains the Dates, Fruit names, Zones, Amount,s 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.

2 Easy Ways to Create a Search Box in Excel Without VBA


1. Apply Excel Conditional Formatting to Create a Search Box Without VBA

In this method, we will apply Conditional Formatting 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.

Steps:

  • First of all, go to cell C17 or the cell that will contain the search box.

Apply Excel Conditional Formatting to Create a Search Box Without VBA

  • Secondly, go to the Home tab.

  • Thirdly, click on the Conditional Formatting drop-down 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:
=SEARCH($C$17, $B&$C5&$D5&$E5&$F5)
  • Now, click on Format to format the searched cell or row to highlight.

Apply Excel Conditional Formatting to Create a Search Box Without VBA

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.

Apply Excel Conditional Formatting to Create a Search Box Without VBA

  • In this way, only the rows (5, 9, 11, 13, 15) that contain Apple will remain highlighted with Red color.
  • See the screenshot below.

Read more: How to Create a Search Box in Excel


1.2 Search Box for Multiple Excel Worksheets

We can also create a Search Box in Excel for multiple 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.

Apply Excel Conditional Formatting to Create a Search Box Without VBA

Steps:

  • 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:
=SEARCH(MSheets1!$C$17, $B5&$C5&$D5&$E5&$F5)
  • Later, click on Format.

Apply Excel Conditional Formatting to Create a Search Box Without VBA

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).

Apply Excel Conditional Formatting to Create a Search Box Without VBA

  • Subsequently, go to MSheets1 > cell C17 > type Apple.

  • Thus, all the rows containing Apple will remain highlighted with Yellow color in the MSheets1 worksheet.

Apply Excel Conditional Formatting to Create a Search Box Without VBA

  • 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

2. Create a 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.

Steps:

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.

Create Dynamic Filter Search Box Without VBA Using Excel Functions

  • 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.

Create Dynamic Filter Search Box Without VBA Using Excel Functions

  • 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.

Create Dynamic Filter Search Box Without VBA Using Excel Functions

  • As a result, the New Name window will appear.
  • Thereupon, go to Name > type FruitList > OK.

Create Dynamic Filter Search Box Without VBA Using Excel Functions

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 will instantly appear in cell C17.

Create Dynamic Filter Search Box Without VBA Using Excel Functions

Lastly, it’s time to set the data. We will connect everything using helper columns. For example, we have used two helper columns H1 & H2. For this, we will use IF, ISNUMBER, SEARCH, and SUM functions 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:
=IF(ISNUMBER(SEARCH($C$17,C5)),G5,"")
  • After you type the formula, press the Enter key.
  • Drag the fill handle to copy the formula up to cell H15.

Create Dynamic Filter Search Box Without VBA Using Excel Functions

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; otherwise, 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.

Steps:

  • Firstly, go to cell C17.
  • Now, to get the number of matches type the following formula in cell C17:
=SUM(-(ISNUMBER(SEARCH("Apple",$C$5:$C$15))))
  • Finally, you must press Ctrl + Shift + Enter to close this array formula.

How to Find Number of Search Matches Using Array Formula in Excel

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

To create 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.

Download Practice Workbook

Download the practice workbook from here.


Conclusion

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.


Related Articles


<< Go Back to Search Box in Excel | Data Management in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Sagufta Tarannum
Sagufta Tarannum

Sagufta Tarannum, holding a BSc in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, contributes significantly as an Excel & VBA Content Developer at ExcelDemy. Fueled by a deep interest in research and innovation, she actively engages with Excel. In her role, Sagufta not only skillfully addresses challenging issues but also demonstrates enthusiasm and expertise in gracefully navigating intricate situations, underscoring her unwavering commitment to consistently delivering exceptional content. Her interests are Advanced... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo