How to Create Search Box in Excel with Conditional Formatting (5 Ways)

Get FREE Advanced Excel Exercises with Solutions!

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.


Create Search Box in Excel with Conditional Formatting: 5 Ways

Here, we have a dataset containing the 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.

Ways to Create Search Box in Excel with Conditional Formatting


1. Using SEARCH, IF, and ISBLANK Functions to Create a 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.

Using SEARCH, IF, and ISBLANK Functions to Create Search Box in Excel with Conditional Formatting

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.

Opening New formatting Rule box to Create Search Box in Excel with Conditional Formatting

  • 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.
=IF(ISBLANK($G$4),0,SEARCH($G$4,$B5&$C5&$D5))

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 columns B, C and D using SEARCH Function.

  • Next, click on Format.

Opening Format Cell Box to Create Search Box in Excel with Conditional Formatting

  • 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, and 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 ranges 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


2. Applying a Combination of Functions to Create a 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.

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

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.
=AND($G$4<>"",ISNUMBER(SEARCH($G$4,$B5&$C5&$D5)))

Here, at first, we checked if Cell G4 is a 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 columns 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


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

Using Name Box to Create Search Box in Excel with Conditional Formatting

  •  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.
=IF(ISBLANK(Search_box),0,SEARCH(Search_box,$B5&$C5&$D5))
  • 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 ranges 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.

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

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

Changing Properties of Text Box to Create Search Box in Excel with Conditional Formatting

  • 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.
=AND($G$4<>"",ISNUMBER(SEARCH($G$4,$B5&$C5&$D5)))

Here, at first, we checked if Cell G4 is a 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 columns B, C, and D using SEARCH Function.

  • Next, click on Format.

  • Finally, you will see that Cell ranges 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.

Using Data Validation with Conditional Formatting to Create Search Box in Excel with Conditional Formatting

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.

Opening Data Validation Box to Create Search Box in Excel with Conditional Formatting

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

Opening Text That Contains box Create Search Box in Excel with Conditional Formatting

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

Practice Section


Download Practice Workbook


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.


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.
Arin Islam
Arin Islam

Anowara Islam Arin, a graduate of Civil Engineering from Khulna University of Engineering & Technology, Bangladesh, serves as a technical content creator in the ExcelDemy project. Possessing a passion for innovation and critical thinking, she actively embraces challenges. Beyond her engineering background, Arin exhibits a keen interest in Excel, having authored numerous articles on Excel & VBA-related issues to simplify the experience for users facing obstacles in Excel. Besides, she is also interested in Advanced Excel,... Read Full Bio

2 Comments
  1. I have done this by using method number 3. however something has changed and now all my results are highlighted when the search box is empty. when i type a name in then i get the appropriate response, just highlighting what i want. once i clear the search box the entire column highlights again. Help!

    • Thanks JASON for your comment.

      The issue you are encountering is the result of two potential situations. The first scenario is that you have implemented an additional conditional formatting within your data range. The second scenario is that you have not adjusted the formula to align with your data.
      However, you can modify the formula for conditional formatting and replace it with the following one.
      =IF(ISBLANK(Search_box),"",SEARCH(Search_box,$B5&$C5&$D5))

      Remove all the conditional formatting except the dedicated one given in the method. Also, check whether there are any VBA codes running in your worksheet affecting the changes.

      Regards
      Md Junaed Ar Rahman

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo