All language subtitles for Tired of scrolling through long Excel data_Tao hop tim kiem trong excel

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?