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.