hi everyone kevin here today i want to show you how you can create a searchable drop-down list in microsoft excel i’ll show you how you can do it on both the web and on desktop and as a spoiler alert it’s a lot easier on the web with a traditional drop down list you have to click into it and then you have to browse through to find the value that you’re looking for with a searchable drop down list on the other hand you simply type in a few letters of what you’re looking for and then you’ll see all possible matches this way you can focus on high value activities for example baking cookies if you want to follow along i’ve included a spreadsheet in the description all right let’s check this out here i am in excel and we’re going to start on the web because this is only going to take about a minute like i said it’s a lot easier on the web hopefully we see this same functionality come to desktop soon usually microsoft rolls out features on the web first and then they follow with desktop you can easily access excel on the web by going to office.com and it’s completely free to use it i want to create an order form for cookies and our sales team misspells cookie names all the time i keep telling them there’s a big difference between double chocolate chip and triple chocolate chip to insert a drop down list simply click on the cell where you want the drop down list to be next up on the top tabs go over to the option that says data click on that and then all the way over on the right hand side click on data validation within data validation settings you can specify what values you want to allow and currently it’s set to any value but i want this to be a drop down list right here i’ll select list next i need to specify what the source is for my drop down list here i’ll move the dialog over and here i see all the different cookie types that we offer this is the source for my drop down list here i’ll select all of these different items and then click on ok over on the left hand side i now have a drop down list and here when i click on it i can see all of the different options now let’s say i start typing let’s say maybe i’m looking for oatmeal cookies here i’ll type in oat and look at that it’s a searchable drop-down list here i see the two different cookie types that have oat in it we have our oatmeal butterscotch toffee and then we also have the oatmeal raisin both excellent choices as you can see it is extremely easy on the web on desktop though it’s a different story we’re going to have to use a few different functions but don’t worry i’ll show you how you can pull it off step by step let’s jump over to desktop here i am in excel desktop and to set up a searchable drop-down list we’re going to use a few functions that require a microsoft 365 subscription first we’re going to create a basic drop down list select the cell where you want to place your drop down list and up on the top tabs let’s click on the one called data over here right near the middle there’s an option called data validation click on this this opens up the data validation prompt and this is pretty similar to what we saw on the web right in here the cell is currently set to allow any value but we want this to be a drop down list with all of our different cookie types here i’ll select this and then select list for the source once again i’ll select all of the different cookie types that we offer here at the kevin cookie company next i’ll click on ok and check that out i now have a drop down list over on the left hand side and i can see all of the different cookie types that we offer here now let’s say i just want to see our oatmeal cookies here i’ll type in oat and then let me click on this drop oh look at that there is a nasty error message unfortunately on desktop you can’t search for values like you can on the web instead we’re going to have to build out some functions that allow us to do this before we build out our searchable drop-down list let’s go back to this cell and remove the data validation here i’ll click on data validation again and i’ll set it to allow any value next click on ok to build this out we’re going to use three separate functions and you can see them right here we’re going to use search is number and filter and we’ll walk through how each one of these works and then we’ll pull it all together and to make this example really clear we’re going to search for oats basically our lineup of oatmeal cookies now some people think that these are the healthiest cookies that we offer here at the kevin cookie company and we sell a lot of them or at least people think they’re healthy they still have a ton of butter and sugar to get started we are going to use the search function we want to search for oat in all of our different cookie types here i’ll click into this cell and by far the easiest way to enter a function in microsoft excel is to click on the insert function icon this will open up a really nice helper that’ll help us craft our function here i’ll type in search and then click on go next click on the top match item then click on ok this opens up a prompt where i could define the text that i want to search for i want to search for oat so here i’ll click on oat right over here next there’s another prompt that asks me where i want to search for this text and i want to search for oat in all of our different cookie type names so here i’ll simply select all of our different cookie types lastly it also asks me for a start number basically the position where i want to start looking and i want to look from the beginning so i’m just going to leave this blank and anyway this is an optional argument i’m all done now filling this out so i’ll click on ok now that i entered this function in i get a number back where it finds out and here i get a one back because it found out in the first position of this cookie type name all the other ones show me an error because there is no oat in the name now that i’ve used the search function to find oat in all of our different cookie names now i want to get a list back of just those items that have oat in them so over here i’ll type in the filter function this will basically allow us to filter the list down to the options without once again let’s click on the function helper right up here within insert function let’s type in filter then click on go and click on the best match this opens up the function arguments for filter and first it asks me for the array or basically all the different items that i want to filter here i’ll select all the different cookie types that we offer here and next it asks me which cookie types do i want to include if i look down here at this helpful hint it tells me that it needs an array of booleans where true represents a row or column to retain now if i look over here at the search results these are not currently boolean values so before i can use the filter function i need to use one other function to find out if oat is in fact in these different items basically i need to get true or false does it contain oats or does it not contain oat let’s close out of this prompt right over here and if you notice this you probably saw that there was one more function we’re going to use is number to find out true or false whether it contains ode here once again let’s click into this cell and then go up to the insert function within the prompt let’s type in is number then click on go and select the best match this opens up the function arguments and this function is really simple all i need to do is provide one value and it’ll tell me true or false does it contain a number over here i’ll click in this cell and it obviously doesn’t contain a number so i’ll click on ok and here it tells me false i’ll copy this formula all the way down and here we can see that only the oatmeal cookie options are true these are the only two with oat in them we’re now ready to use the filter function i’ll go back up to this cell and once again let’s go to insert function here let’s type in filter and then click on the best match within the function arguments let’s select the array and that’s all of the different cookie types that we offer here next it wants to know what values we want to include and now we’ve gone through and we’ve defined whether or not it contains a number if it contains a number well this is one of the options that we want in our drop down list here i’ll highlight this entire column lastly i can also specify what happens if no results come back here i’ll insert quotes and i’ll simply type in no results and then i’ll close the quotes this all looks good now so i’ll click on ok and check that out the filter function returns the two oatmeal cookies that we’re looking for so we’re ready to bring these results now into our drop down list let’s go back to the cell where we want to insert a drop down list i’ll select this cell right here let’s go to the top tabs click into data and then go over to data validation within data validation just like we did before let’s set it to allow a list here i’ll select list and for the source of the list instead of highlighting all of the different cookie types we’re going to select the filter list right here let’s select that value at the end of the source we have to add one additional item we’re going to insert a hash and the reason why is this list can contain multiple items and if you want to get multiple items back in the drop-down list well we have to use a hash that’ll include all of the spill values there’s one more change that we need to make before clicking on ok let’s also go over to the error alert and turn this off and the reason why is let’s say you were to just type in oat that doesn’t match any of the cookie types so you would get an error but we want people to be able to type in partial matches and then they’ll see all the possible options so let’s turn this off and now we’re all done so click on ok and check that out i now have a drop down list right here and it has oat in it when i click on the drop down list i see the two cookie options with oat so here’s an example let’s type in birth for the birthday cake cookie when i click on the drop down i see the one option with birthday cake or let’s say cookies with butter we have lots of cookies with butter in fact all of our cookies have butter but some of the names just don’t let people know and here we can see all of the different options so this is a pretty nice searchable drop down list at this point if you just need one row with a drop down list you can take all of this area over here you can move it to a separate sheet or you can hide these columns and no one will ever know that you have all of this logic behind the scenes to make your drop down list work if you want multiple rows to be able to work unfortunately we’re going to have to do a little bit more work here for example if i click on the cell with butter and i drag this down well look at that i still have my drop down list and it looks like it works but does it here i’ll type in whoopi we have a whoopee cookie and let’s click on the drop down list here it looks like it’s still returning all the butter values and the reason why is here if i come over to this column right here you’ll notice that it’s pointing at this specific cell so it doesn’t update for the cell below so how do we get it to work with multiple rows i want searchable drop-down lists for this entire order form well to do that let’s jump to the next worksheet called order form multiple here i am on the next sheet and once again i want to find all cookie types with oat and don’t worry we’re simply going to use everything that we just learned to return a drop down list on multiple rows and we’re going to start with the same function that we started with on this previous sheet here i’m going to enter search and once again i want to search for oat in all of our different cookie types this time instead of putting all the cookie types on this sheet i’ve placed it on a separate sheet so first off i’m looking for oat and then i need to specify where i want to search for it so i’ll insert a comma then i’ll select my other sheet and here i’ll select all the cookie types so once again i’m searching for oat in this list this all looks good so i’m going to close the parentheses and then hit enter just like we did before here i see a list of all of the different cookie types that include oat and just two of them include oat so once again just like we did before here i’m going to select the top cell and now i want to know is there a number in this list so just like we did before here i’m going to type is number as another function and then i’ll open the parentheses and close the parentheses so basically the output of the search we’re going to look at that to see if there’s a number in that list then let’s press enter down below i can now see booleans telling me true or false whether one of those cookie types includes oat and here i see that two of them are true two of them contain oat so once again we’re doing exactly what we did on the previous sheet but now we’re combining everything into one formula getting true or false back on its own isn’t really that helpful so once again we want to filter our list of cookie types so right here after equals i’ll type in filter then i’ll open the parentheses and it asks me for the array or basically the cookie types that we want to pass back or the cookies that include oat so here i’ll click on the cookie type sheet and once again i’ll select all of the different cookie types next i need to specify which values i want to include and on the previous sheet we have a column with true or false and that’s this formula that we already entered in so here i’ll simply insert a comma and this will tell filter what values or what cookies to include here at the very end i want to specify what it should say if there are no cookie types that match and just like we did before here i’ll type in no results and then close my quotes next i’ll close the parentheses and then hit enter and here now you see that two options come back so just like we did before the only difference now is we’ve combined all of these different functions together into just one formula for this drop down to work on every single row i need these results here to appear horizontally instead of vertically and luckily there’s a function that will help us with that here i’ll click in the top cell and right after the equal sign i’ll type in transpose this will take a vertical list and it’ll turn it so it’s a horizontal list here i’ll open the parentheses at the beginning i need to pass in an array that’s basically this list right here at the very end i’ll close the parentheses and then hit enter so here now you see i have a horizontal list of all of the different options that match ode now that our formula is ready to go let’s insert a drop down list here i’ll click into the cell where i want to add a drop-down list next let’s go up and click on data validation within data validation let’s click on allow and then select list for the source we’ll simply click right here and once again let’s include a hash that way that’ll include all values that match and it could spread across multiple cells or what’s referred to as the spill when i copy down my drop down list i don’t want it to be locked to just one individual row so right here there’s a dollar sign in front of the six i’m going to remove that so when i copy the data validation down it’ll apply to the lower rows up on top let’s also click on error alert and make sure that this check box is unchecked just like we did before we’re all set now so let’s click on ok and check that out here i have my drop down list and when i click on it i see the two oatmeal cookies now here i can now pull the data validation down so here i’ll pull it down a few rows and over here i have to do the exact same thing so i’ll also pull this down a few rows now let’s say i’m looking for all of our cookies with butter here i’ll type in butter i’ll click on the drop-down list and here i see all the different cookies with butter pretty neat huh to make this really elegant you probably don’t want all of the different options over on the side here i’ll zoom out a little bit and we can just select a whole bunch of these columns and i’ll hide them now when i zoom in i have all of these nice drop down lists and no one will have any idea that we have all this logic right over on the side before we wrap up let’s jump over to the cookie types worksheet i have all of the different cookie types in a table here and to insert a table simply go up to insert and right here you have the option to insert a table if you don’t have a table yet you could also press ctrl t the nice thing about using a table is if i add any additional items down here it’ll automatically be included in my drop down list with a table as well you can simply sort your table here and that’ll automatically apply the sorting in your drop-down list you could also use a function to sort but this is just a little bit easier simply sort it here alright well let me know down below in the comments would you personally order cookies from the kevin cookie company to see more videos like this please consider subscribing and i’ll see you next time [Music] you