How to Create Search Box in Excel with Conditional Formatting

Looking for ways to know how to create a search box in Excel with Conditional Formatting? We can create a search box in Excel in different ways. Here, you will find 5 ways to create a search box in Excel with Conditional Formatting.

Download Practice Workbook

5 Ways to Create Search Box in Excel with Conditional Formatting

Here, we have a dataset containing Customer Name, Product, and State of a company. Now, we will use this dataset to show you how to create a search box in Excel with Conditional Formatting.

1. Using SEARCH, IF, and ISBLANK Functions to Create Search Box in Excel

In the first method, we will show you how to create a search box using the SEARCH, IF and ISBLANK functions with Conditional Formatting in Excel. The SEARCH function returns a particular string from the given range and the ISBLANK function is used to check if a cell is empty or not and returns in True or False.

Go through the steps given below to do it on your own.

Steps:

• Firstly, select Cell range B5:D13.
• Then, go to the Home tab >> click on Conditional Formatting.

• Next, select the New Rule.

• Now, the New formatting Rule box will open.
• After that, select Use a formula to determine which cells to format.
• Then, insert the following formula in the box.

Here, at first, we checked if Cell G4 is Blank or not using the ISBLANK Function and if it is TRUE it will return 0 or else it will search for the value and adjacent data for Cell G4 in Cell column B, C and D using SEARCH Function.

• Next, click on Format.

• Now, the Format Cells box will open.
• Afterward, go to the Fill option.
• Then, select any Background Color of your choice. Here, we will select Green, Accent 6, Lighter 80%.
• Next, click on OK.

• Again, the New Formatting Rule box will appear.
• After that, click on OK.

• Now, if you put any value that contains the data of the selected Cell range in Cell G4, that Cell containing the data and its corresponding Cells will be formatted.
• Then, we will insert Virginia in Cell G4.
• Finally, Cell range B5:D5, B8:D8, B13:D13 will be formatted according to the formula used in Conditional Formatting.

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

2. Applying Combination of Functions to Create Search Box in Excel with Conditional Formatting

We can also create a search box using the AND, ISNUMBER and SEARCH functions in Excel. Here, the ISNUMBER function will check if a cell contains a number or not then the AND function will check multiple arguments and if all the arguments are True then it will return True otherwise it will return False.

Follow the Steps given below to do it on your own.

Steps:

• In the beginning, follow the steps shown in Method 1 to open the New Formatting Rule box.
• After that, select Use a formula to determine which cells to format.
• Then, insert the following formula in the box.

Here, at first, we checked if Cell G4 is Number or not using the ISNUMBER Function and if it is TRUE it will return 0 or else it will search for the value and adjacent data for Cell G4 in Cell column B, C and D using SEARCH Function.

• Next, click on Format.

• Now, change the Format where the formula is True going through the steps shown in Method 1.
• Then, we will insert Florida in Cell G4.
• Finally, you will see that Cell range B6:D6 and B11:D11 is formatted according to the formula we used in Conditional Formatting.

Read More: How to Create a Search Box in Excel for Multiple Sheets (2 Ways)

3. Using Name Box to Create Search Box with Conditional Formatting

In the third method, we will show you how to create a search box using Name Box with Conditional Formatting in Excel. Go through the steps given below to do it on your own.

Steps:

• Firstly, select Cell G4.
• Then, type Search_box in the Name Box.
• Next, press ENTER.

•  Now, follow the steps shown in Method 1 to open the New Formatting Rule box.
• After that, select Use a formula to determine which cells to format.
• Then, insert the following formula in the box.

• Next, click on Format.

• Afterward, change the Format where the formula is True going through the steps shown in Method 1.
• Then, we will insert Florida in Cell G4.
• Finally, you will see that Cell range B6:D6 and B11:D11 are formatted according to the formula we used in Conditional Formatting using Name Box.

4. Use of Text Box from ActiveX Control to Create Search Box with Conditional Formatting in Excel

Next, we will show you how to use Text Box from ActiveX Control to create a search box in Excel with Conditional Formatting.

Follow the steps given below to do it on your own.

Steps:

• In the beginning, go to the Developer tab >> click on Insert >> select Text Box from ActiveX Control.

• Then, insert a Text Box and Right-click on it.

• After that, click on Properties.

• Now, the Properties box will open.
• Then, go to the Alphabetic option >> insert Cell G4 as the Linked Cell.

• Next, insert Florida in Cell G4.
• After that, this data will automatically be inserted in the Text Box.

• Now, follow the steps shown in Method 1 to open the New Formatting Rule box.
• Afterward, select Use a formula to determine which cells to format.
• Then, insert the following formula in the box.

Here, at first, we checked if Cell G4 is number or not using the ISNUMBER Function and if it is TRUE it will return 0 or else it will search for the value and adjacent data for Cell G4 in Cell column B, C and D using SEARCH Function.

• Next, click on Format.

• Finally, you will see that Cell range B6:D6 and B11:D11 are formatted according to the formula we used in Conditional Formatting using Text Box.

5. Using Data Validation with Conditional Formatting in Excel

In the final method, we will show you how to create a search box using Data Validation with Conditional Formatting in Excel. Here, we will search a particular data in a particular column using Data Validation and there is no way to search for any data that does not exist in the dataset.

Follow the steps given below to do it on your own.

Steps:

• Firstly, select Cell C15.
• Then, go to the Data tab >> click on Data Validation.

• Now, the Data Validation box will appear.
• Next, select List as Allow.
• After that, select Cell range D5:D13 as Source.
• Then, click on OK.

• Afterward, select Cell Range D5:D13.
• Then, go to the Home tab >> click on Conditional Formatting.

• Now, click on Highlight Cells Rules >> select Text that Contains.

• Now, the Text That Contains box will open.
• Afterward, select Cell C15 as in the Format cells that contain the text box..
• Then, select Light Red Fill with Dark Rsd Text as Format.
• Next, click on OK.

• Now, we will select New Jersey in Cell C15 and the Format will change automatically in Cell D7 and Cell D9.

• Similarly, you can create search boxes for Column B and Column C and format the Cells according to your preference using Data Validation with Conditional Formatting.

Practice Section

In this section, we are giving you the dataset to practice on your own and learn to use these methods.

Conclusion

So, in this article, you will find 5 ways to create a search box in Excel with Conditional Formatting. Use any of these ways to accomplish the result in this regard. Hope you find this article helpful and informative. Feel free to comment if something seems difficult to understand. Let us know any other approaches which we might have missed here. And, visit ExcelDemy for many more articles like this. Thank you!

Related Articles

• Create a Search Box in Excel with VBA (5 Suitable Examples)
• How to Create a Filtering Search Box for Your Excel Data (5 Ways)