0:00
hello and welcome to tutorial's point in
0:02
this video we are going to learn how to
0:04
create a search bar in Excel as you can
0:08
see here we have the sample data set of
0:10
employees of a company we have first
0:12
name last name country age and CTC of
0:16
all these employees as you can see we
0:18
have hundreds of these employees and it
0:20
is really difficult to look for any one
0:22
employee so we are going to create a
0:25
search bar to search for any one
0:28
employee also the practice sheet is
0:31
available in the description to download
0:34
so without wasting time let's get
0:36
started to create our search bar the
0:39
first thing we need to do is to click on
0:41
this developer option but if you do not
0:43
have this developer option do not worry
0:45
you can come here and right click and
0:48
you have the customize the ribbon option
0:50
click on it and you will have this menu
0:53
as you can see here we have developer
0:56
and make sure that you have a take on
0:58
this developer once you just click on
1:01
okay and then you will have the
1:02
developer option here next I'm going to
1:05
click on this developer option and then
1:07
I'll click insert and in the insert I'll
1:09
select this icon which is a text box so
1:13
let me just click on it and I'm going to
1:16
drag it here something like
1:19
this so this is where I'm going to paste
1:22
it now the next thing is to right click
1:24
on this and go to properties and we have
1:27
this properties box now do not worry
1:29
about all all of these properties here
1:31
we just need this linked cell property
1:34
now as you can see I have pasted this
1:36
text box onto this H cell and as you can
1:39
see this is the H2 cell right so we will
1:42
link this cell with this text box so
1:45
here in the link cell I'll write
1:48
H2 and then I'll hit enter and then I'll
1:51
just close it as you can see this text
1:54
box is still selected so here we need to
1:57
click Once on This design mode so I'll
2:00
click here all right now the next thing
2:03
is I'll just copy these
2:06
headers so I'll press contrl C to copy
2:09
them and then let me just paste them
2:11
here contrl V to paste them we can also
2:14
adjust their widths a little bit
2:17
something like this all
2:19
right now our goal here is to write a
2:23
name of the employ here for example I
2:25
will search for an employee called David
2:27
and I should be able to get all the
2:29
details of David here to do this let me
2:32
just remove this for now all right now I
2:34
will select all of my data this is my
2:36
data so I'll just select the first cell
2:39
and then I'll press control shift right
2:41
arrow and then control shift down arrow
2:44
and all of my data is selected then I'll
2:46
press control T to convert it to a table
2:49
and it says create a table just click on
2:52
okay and our data is converted to a
2:56
table now we are going to use a formula
3:00
so here in the first name I'm going to
3:02
write a formula I'll write
3:04
equals filter and then parenthesis and
3:08
then we have the first option of an
3:10
array now here I'm going to select my
3:12
table so I'll just click here and then
3:15
press control shift right to select the
3:17
first one and then press control shift
3:20
downwards to select all of my table now
3:23
let me just scroll up quickly so after
3:26
this I'll write comma and then we need
3:29
to write another thing we need to write
3:32
what we are going to search for so again
3:34
I'm going to search through the first
3:36
name so I'll select this first name cell
3:40
and then I'll press control shift
3:41
downwards to select all the first names
3:44
let me just quickly scroll up again and
3:48
then I'll make sure that this first name
3:50
should be equal to what we have selected
3:53
here which is going to be our H2 cell so
3:57
H2 okay so if it is equal to it is going
4:00
to return as the result comma if it is
4:02
not equal to then we are going to write
4:06
not found all right and then I'll just
4:10
close the parenthesis and hit enter it
4:13
says not found because we have not given
4:14
any result so let's say I will look for
4:17
an employee called Olli okay there is no
4:20
employee named OE but is there an
4:22
employee named David so I'll write David
4:27
and there it is we have David last name
4:30
country age and CTC similarly let's look
4:33
for some other employee let's say Lisa
4:37
all right we have an employee named Lisa
4:39
with all the details available
4:42
here so that will be enough for this
4:44
video make sure to like this video share
4:47
it with your friends and I will see you
