Today I want to show you how you can add
a search box to your Excel spreadsheet. Imagine you have customer information in
Excel, you can use a search box to look for exact matches, but you can also use it for more
advanced scenarios. You can look for partial matches. You can even search across multiple
columns, and we can even highlight the results. This is a fully fledged search box.
If you want to follow along today, I've included a sample spreadsheet right
up above and down below in the description. This is the perfect thing to add to an Excel
dashboard, and if you want to create an Excel dashboard, I have a video right up there
that'll show you step-by-step how to do it. All right, let's check this
out. Here I am in Excel, and we're going to start with
just the basic search box first. We're going to do an exact match. Down below I have a data set with
customers and their favorite cookies. I know, how can you possibly choose just one
favorite cookie, but hey, we had to do it. Over on the right-hand side, I want to
add a search box so when you search for a customer's first name, the table down below
will show all of the matching customers. To insert a search box, first off, we need to add
the developer tab and if you don't yet have this, you can simply right click on the
ribbon and then click on customize. Within customize over on the right-hand
side you should see an option for developer. Make sure that this box is
checked and then click on OK. Now that we have the developer
tab, let's click on it and right in the center there is the option to insert. Click on this and under the ActiveX controls
category, here you'll see a text box. We're going to use this as our search box. Click on
this and then draw a search box over cell G2. We now have a search box on the sheet, but it
doesn't actually do anything yet. While in design mode, let's right click on the search box and
then go down to the option that says properties. This opens up properties and within this
list you'll see an option for linked cell. We want to link our search box
to a cell within our worksheet and I'm going to link it to the cell that's
directly behind the search box or cell G2. So right here I'll type in G2 and then I can close out properties. Now that my search
box is linked to the spreadsheet, here I can exit design mode, and let's
say I type in Kevin into the search field. I'll go back into design mode and
here I'll move the search box. So here you'll see that it's
now linked to this cell, and whatever value I type into this
search box will also show up in this cell. Here I'll move the search box back
down and I'll exit design mode. Now that we've linked the search box to a cell, we can now insert a function that
uses the input of this search box. Right over here, let's click into the first cell of this table and then let's
use the function helper. Right up here, let's click on this. This opens up the function
helper and I want to use the filter function. Here I'll select filter and I'll click on OK. This opens up the function arguments and the
first argument is the array or this is the data set that I want to filter and I want to filter
this table right over here, so this is my array. Here I'll select the entire table. Next it asks me what values I want to include,
and I only want to include results that include the first name that I entered in this box, so
I only want to include results with the name Kevin. Here I'll highlight this first column and
once again I only want it where it equals Kevin, so I'll enter equals and G2. G2 once again
is the search field. Right down below, there's yet one more argument that allows me
to define what happens if there are no matches. Here I'll simply type in "No Records Found."
This all looks good now, so I'll click on OK. And here we can see that I typed in Kevin in
the search field and here I get one result back. I could click in the search field and
let's say I type in Kerry instead. Here I see Kerry as one of the results. We now have the basic search box working, but
what if I also want partial matches, so what if I just type in Kev, and I want to see Kevin show
up as one of the results? To do a partial match, once again, we'll use the same filter function
that we used earlier, and in fact here I have the exact same formula entered, but we also need
to use two additional functions to help us out. We're going to use the Search
function and the IsNumber function. Search will tell us is there a partial match
and then IsNumber will tell us true or false, is there a match there and we can then
use that output in the filter function. Let's start with Search. Over here, I'll click in
this cell and in the formula bar here I'll enter =search. I'll select this function and first
it asks me what text are we looking for? Well, I want to look for the
text entered into the search box, so I'll enter G2. Remember that's
the cell behind the search box. Then it asks me where do I want to look for this value and I want to look for
it in this first name column. So here I'll select all of these different values
and then I'll close the parentheses, and that's my function. Over in column I, I can now see all of
the different rows where there is a match for KEV. So here I can see that, Kevin includes Kev. Here if I change the search box and let's
say I just type in Ke here I can see that Kerry starts with KE, so that's also a
match and it matches in position one. If I Scroll down here, I see a four for the
name Parker, and there's a KE in Parker, and the key is in position four,
which is why I get that four back. Next, we can now use the IsNumber function to tell
us true or false, is there a number over here? Here I'll type in = and I'll type in IsNumber. I'll open the parentheses and I simply
want to look across this set of values. So here I'll highlight all of these
values and then close my parentheses and here you'll see that the number one right
here, yes, that's true, that is a number, and if I scroll down here #4,
that's also true, that's a number.
We can now use the output of this
as part of our filter function. Here I'll click into the filter
function and now we just need to make one small tweak to have it show the partial match. Here I'll click into the function and for
the array we'll simply leave that as is. We don't have to make any changes there. Here it asks me what values I want to include and
over here we already did that work. Here we say true or false, is there a partial match?
Here I could remove this portion of the function that we answered previously and
now we're going to replace that with this. So here I'll click into that cell, and
I'll copy down this entire section. Here you'll see that it
enters it as J5 with a # sign. So why is there a hash? Well, this is an array function, so I
just need to click in the first cell and the # will include any other cells that spill
underneath, and that's the only tweak I need to make. Here I'll hit the enter key and you'll
see that I now have a partial match search, so here I could type in Kev and here it cuts
it down to Kevin, and if I go back to KE, here it shows all of the partial matches. Pretty
cool. Of course, if we want to clean up our sheet and we don't want these two helper columns on the
side, we can take these functions and incorporate them into our filter function. Here I'll remove
this reference and instead I'll add the IsNumber. Then we'll add Search and here
once again we want to look for G2 or the value entered in the search field,
and we want to find it within this list. So here I'll highlight that list and then I'll
close the parentheses and now I'll hit enter. And here you'll see it works exactly the same,
but now instead of pointing to these columns, everything is integrated into this one
cell. The search box is now starting to become very powerful, but right now
we're just searching on one column. We can now expand it so you could
search across an entire table. To search on multiple columns, it's just a really
small tweak to what we've already been doing. Here in the search field all once again type in KE and here you see that it filters down based
on the first name and here it also filters on the favorite cookie. Here you see Snickerdoodle
includes Ke as well as some of the first names. So how do we do this? Well, once again, it's just a really
small tweak to what we've been doing. I'll click into this cell, and here
you should recognize this formula. Here we have the filter function. Here I have IsNumber and Search. So just like what we did in the previous step, but here in the previous step, we
simply looked at the first name column. To include additional columns,
you simply enter in a plus sign. It's basically like including an or and here
now I say IsNumber, Search and we look at the last name column and then I add another plus
sign or basically an or and then we search this last column, the favorite cookie with
IsNumber, Search and the favorite cookie. And so you simply keep adding these for
whatever number of columns you have. Now I'll press enter and
that's all there is to it. The formula itself looks really complicated,
but it simply takes what we've already learned. As icing on the cake, I want to highlight any of
the matching results with the search query and for that we can use conditional formatting.
To apply conditional formatting, here I'll highlight all of my result cells and on the
home tab, let's click on conditional formatting. Here I'll select highlight cell rules and I'll
select text that contains. This opens up a prompt and here I'll type in =G2 and I'll set this to
an absolute reference by pressing the F4 key. Alternatively, you can also enter a $ sign in
front of the letter and the number for the cell and over here you can select how
you want to format those cells. Here I'll go with the yellow fill and click on OK. Here now when I type in Ke here,
I see all of the partial matches, but it highlights whichever value matches that,
so it makes it really easy to see all the matches. All right, you now know how to add a very powerful
search box to your Excel spreadsheets. To see more Excel tutorials like this one,
check out the playlist right up above. Please consider subscribing,
and I'll see you next time.
