How to Create Search Box in Excel (No VBA Code Required)

This blog post shows you to create search box in Excel without using VBA code.

The search box will find all the duplicate or same cell values in one search.

Suppose that we have a dataset and multiple cell values are the same within it.

Create Search Box in Excel
Create Search Box in Excel_Image01

See in the image above where we circled cell values are duplicated within the dataset.

Let’s say we want to find any cell value within the dataset when multiple values of the same cell exist in the dataset.

So we will use the ‘CTRL+F’ keyboard shortcut command to find it normally.

But Excel FIND option will find only one cell value at one time.

Create Search Box in Excel
Create Search Box in Excel_Image_2

We can find another cell value by pressing the ‘Find Next’ button at the bottom of the ‘Find and Replace’ box.

Then it will find another cell value if it exists.

Then again and again we have to use the same procedure to find the next same cell value or duplicate value.

Here in this post, we are going to create a search box that will find all the duplicate or same cell values in one go.

See in the below image what we are going to do.

Create Search Box in Excel
Create Search Box in Excel_Image_3

See in the image we have typed the same cell value here in our Excel search box and it finds all the duplicate cell values in one go.

Create Search Box in Excel

So we are going to create this kind of search box here.

First, know your data set for which we want to create a search box.

Create Search Box in Excel
Create Search Box in Excel_Image_4

You can see the search box above, where we will put the desired cell value.

The Search box here is a simple cell with background color only.

When we put the cell value into the search box, our cell value will highlight in a color within the dataset.

All the same cell values will be highlighted if more than one value is the same.

We are going to use Conditional Formatting here to create a search box.

Now follow the below procedure step by step.

Step1. – The very first step is to select the dataset for which we want to create a search box. You can see in the image below:-

Create Search Box in Excel
Create Search Box in Excel_Image_5

Step 2. – Go to the ‘Home’ tab and then go to the Conditional Formatting drop-down.

And click on the ‘New Rule’ option from the listed options.

Step 3 – When you click on the ‘New Rule’ option, you will get the ‘New Formatting Rule dialogue box. Check the image below.

Create Search Box in Excel
Create Search Box in Excel_Image_6

In this ‘New Formatting Rule’ dialogue box, go to the bottom option ‘Use a formula to determine which cells to format’ from the listed options.

Here you can see a formula bar circled, where you need to type the below Excel formula.

And type the below formula in the formula bar here.

=$H$1=A2

Create Search Box in Excel_Image_7

=$H$1 – this is the cell reference, where we want to create a search box, see the image below.

=A2 this is the cell reference of a cell (A2) within the dataset.

(If you find an “$” before or after this cell reference, delete it manually by hitting backspace or delete.)

Step 4 – Now click on the ‘Format’ button below to give a color to the searched cell value.

Go to the ‘Fill’ tab in the ‘Format Cells’ dialogue box.

Select any color from the color list below and see the sample preview at the bottom, when you select any color.

Create Search Box in Excel_Image_8

Once your color is selected, press the ‘Ok’ button below.

Step 5. – Then see in the ‘New Formatting Rule’ dialogue box, in the preview section, the selected color is applied.

Now, this is the color of your cell that you want to search in the dataset.

Create Search Box in Excel
Create Search Box in Excel_Image_9

Again press the ‘Ok’ button here to apply the above Conditional Formatting in the dataset.

And now it’s done.

Your search box is created now and ready to find any listed cell value within the dataset.

Now enter any cell value in the search box and see that this will find all the same cell values in the dataset.

See the below image and try it.

Create Search Box in Excel
Create Search Box in Excel_Image_10

This is the simple and easy search box that can create and find any duplicate cell values within the dataset.

Try more cell values to test this search box.

If you want to learn more about it, please visit here.

I hope you find this tutorial useful.

Please put your comment or suggestion in the comment box below.

Other Useful Post

VBA Macro to Dynamically Copy and Paste from One Sheet to Another

How to Lock Scroll Area in Excel with VBA (Step By Step Guide)

How to Hide Blank Rows in Excel Using VBA Macro

3 Quick Ways to Move or Copy a Worksheet into Another Workbook

3 Quick Ways to Change the Font Color in Excel VBA

How to Split Cells Diagonally in Excel (Step by Step Guide)

3 Easy Ways to Select the Visible Cells Only In Excel

How to Insert Blank Rows after Every (2nd or 3rd or nth) Row in Excel

5 Ways to Delete Blank Rows in Excel (With VBA Macro)

Leave a Comment

Your email address will not be published.