Original subtitles

How to create a search box in Excel like this? Where if you

type something in the search box, names or data from another

sheet will appear. Let's do it together. First, in the student

list sheet, convert your data into a table. Press control A

to select all the data then press control T to turn it into

a table. Make sure the my table has headers box is checked if

your data has headers. Click okay. Now your data is

converted into a table. If you want to change the table style,

go to table styles and choose the style you like.

Next, uncheck the filter button to make your table look

cleaner. Then, let's name the table for easy reference. I'll

name it student list.

Next, we'll create named ranges for the columns. Press control

shift F3, uncheck left column and make sure only top row is

checked. Click okay. Next, copy the headers and go to the

search sheet then paste them wherever you want. After that,

go to the insert tab, click shapes and choose the shape you

like. Draw the shape above your header and pick your preferred

theme style.

Then go back to the insert tab. Select icons and search for

search. This part is optional. Pick an icon you like and click

insert. Adjust the icon and choose a style if you want.

Next, go to the developer tab, click insert, and under active

X control, select text box. Draw the text box inside the

shape you created earlier and adjust its size if needed.

Then, open the properties window. In the linked cell

field, link it to cell B five. This will be our reference for

the formula later. To remove the text box border, set the

border style to zero. Close the properties window and click

design mode to finalize the text box now you have a text

box that you can use as a search box next in cell B8

enter this formula to pull data from the other sheet type

equals then filter for the array type student list which

is the table we created earlier Then type is number then

search. For the find text, type B 5 and press F 4 to make it

absolute. This is the cell reference for our text box. For

within text type name. This is the column we'll use for

matching the text. Type 2, closing parenthesis then add

not found to show this message if there's no match. Close the

formula and press enter. Now, whatever you type in the search

box will appear filtered based on what you want to see.

If the name or data is not in the list, it will return not

found. Next, let's make your data look better. If you

notice, when you search, there are no borders. We'll add

borders. To do this, go to the view tab and uncheck grid

lines. Click anywhere inside the data table. Press control A

to select all. Go to the home tab. Click conditional

formatting then new rule. Choose format only cells that

contain then in the drop down select no blanks. Click format.

Go to the border tab. Pick the border color you like. And

click the box so that a line appears on each side. Once

done, click okay then okay again. When you search for a

name, your data will automatically have borders.

That's how you make a dynamic search box. Easy, right?