title
🔎 How to Create Searchable Drop Down List in Excel

description
In this step-by-step tutorial, learn how to build a searchable drop down list in Microsoft Excel. First, we look at how to create a searchable drop down list on Excel for the web, which is very easy. All you need to do is to create a drop down list and it's searchable by default. Desktop Excel is a little more involved and requires three functions, including search, isnumber, and filter. First, we start by creating a drop down list that works on one cell. Then we progress and build a drop down list that works across multiple cells. By the end, you'll master creating searchable drop down lists on both Excel on the web and Excel desktop. 👋 Additional resources - Sample spreadsheet to follow along: https://1drv.ms/x/s!AmxrofZZlZ-whMg2wN93Q4gx5gycCw?e=JX6W3Z - Excel on the web: https://www.office.com - Learn the fundamentals of Excel in just 2 hours: https://kevinstratvert.thinkific.com ⌚ Timestamps 0:00 Introduction 0:45 Create searchable drop down list in Excel on the web 2:53 Create basic drop down list in Excel desktop - no search 4:27 Create searchable drop down list in Excel desktop - one cell 11:28 Create searchable drop down list in Excel desktop - multiple cells 17:30 Best practices 18:09 Wrap up 📃 Watch related playlists - Playlist with all my videos on Excel: https://youtube.com/playlist?list=PLlKpQrBME6xLYoubjOqowzcCCd0ivQVLY 🚩 Connect with me on social - LinkedIn: https://www.linkedin.com/in/kevinstratvert/ - Twitter: https://twitter.com/kevstrat - Facebook: https://www.facebook.com/Kevin-Stratvert-101912218227818 - TikTok: https://www.tiktok.com/@kevinstratvert 🔔 Subscribe to my YouTube channel https://www.youtube.com/user/kevlers?sub_confirmation=1 🎬 Want to watch again? Navigate back to my YouTube channel quickly http://www.kevinstratvert.com 🛍 Support me with your Amazon purchases: https://amzn.to/3kCP2yz ⚖ As full disclosure, I use affiliate links above. Purchasing through these links gives me a small commission to support videos on this channel -- the price to you is the same. #stratvert

detail
{'title': '🔎 How to Create Searchable Drop Down List in Excel', 'heatmap': [{'end': 380.539, 'start': 312.17, 'weight': 0.817}, {'end': 571.539, 'start': 521.69, 'weight': 0.808}, {'end': 817.468, 'start': 754.457, 'weight': 0.828}, {'end': 871.684, 'start': 857.697, 'weight': 0.72}, {'end': 907.816, 'start': 882.549, 'weight': 0.732}, {'end': 1008.602, 'start': 926.849, 'weight': 0.814}], 'summary': 'Learn to create a searchable dropdown list in excel, emphasizing increased efficiency in finding values, using microsoft 365 subscription and demonstrating search and filter functions to find and display specific data, resulting in a final list of options.', 'chapters': [{'end': 162.906, 'segs': [{'end': 33.243, 'src': 'embed', 'start': 0.069, 'weight': 0, 'content': [{'end': 1.43, 'text': 'Hi everyone, Kevin here.', 'start': 0.069, 'duration': 1.361}, {'end': 9.173, 'text': 'Today, I wanna show you how you can create a searchable dropdown list in Microsoft Excel.', 'start': 1.67, 'duration': 7.503}, {'end': 13.034, 'text': "I'll show you how you can do it on both the web and on desktop.", 'start': 9.753, 'duration': 3.281}, {'end': 17.016, 'text': "And as a spoiler alert, it's a lot easier on the web.", 'start': 13.414, 'duration': 3.602}, {'end': 24.799, 'text': "With a traditional dropdown list, you have to click into it and then you have to browse through to find the value that you're looking for.", 'start': 17.936, 'duration': 6.863}, {'end': 27.16, 'text': 'With a searchable dropdown list.', 'start': 25.38, 'duration': 1.78}, {'end': 33.243, 'text': "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.", 'start': 27.16, 'duration': 6.083}], 'summary': 'Kevin demonstrates creating a searchable dropdown list in excel for web and desktop, highlighting the easier process on the web.', 'duration': 33.174, 'max_score': 0.069, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/fsL57bvd7Pk/pics/fsL57bvd7Pk69.jpg'}, {'end': 92.311, 'src': 'embed', 'start': 45.589, 'weight': 1, 'content': [{'end': 52.356, 'text': "Here I am in Excel and we're going to start on the web because this is only going to take about a minute.", 'start': 45.589, 'duration': 6.767}, {'end': 55.64, 'text': "Like I said, it's a lot easier on the web.", 'start': 52.837, 'duration': 2.803}, {'end': 59.965, 'text': 'Hopefully we see this same functionality come to desktop soon.', 'start': 56.161, 'duration': 3.804}, {'end': 65.15, 'text': 'Usually Microsoft rolls out features on the web first and then they follow with desktop.', 'start': 60.565, 'duration': 4.585}, {'end': 72.679, 'text': "You can easily access Excel on the web by going to office.com and it's completely free to use it.", 'start': 66.095, 'duration': 6.584}, {'end': 80.203, 'text': 'I want to create an order form for cookies and our sales team misspells cookie names all the time.', 'start': 73.039, 'duration': 7.164}, {'end': 85.907, 'text': "I keep telling them there's a big difference between double chocolate chip and triple chocolate chip.", 'start': 80.624, 'duration': 5.283}, {'end': 92.311, 'text': 'To insert a drop-down list, simply click on the cell where you want the drop-down list to be.', 'start': 86.627, 'duration': 5.684}], 'summary': 'Excel on web is free, easier, and likely to get features first. creating an order form with drop-down list.', 'duration': 46.722, 'max_score': 45.589, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/fsL57bvd7Pk/pics/fsL57bvd7Pk45589.jpg'}], 'start': 0.069, 'title': 'Creating a searchable dropdown list in excel', 'summary': "Demonstrates the implementation of a searchable dropdown list in microsoft excel, emphasizing increased efficiency in finding values on the web, with a specific example of creating an order form for cookies and showcasing the feature's functionality.", 'chapters': [{'end': 162.906, 'start': 0.069, 'title': 'Searchable dropdown list in excel', 'summary': "Demonstrates how to create a searchable dropdown list in microsoft excel, highlighting the ease of implementation on the web and the increased efficiency in finding values, with a specific example of creating an order form for cookies and showcasing the feature's functionality.", 'duration': 162.837, 'highlights': ['Creating a searchable dropdown list in Microsoft Excel The chapter demonstrates the process of creating a searchable dropdown list in Excel, emphasizing its ease of implementation and efficiency.', 'Implementation ease on the web compared to desktop It is highlighted that creating a searchable dropdown list is a lot easier on the web compared to desktop, with the hope for the same functionality to be available on desktop soon.', 'Example of creating an order form for cookies The specific example of creating an order form for cookies is used to showcase the benefits of using a searchable dropdown list, addressing the issue of misspelled cookie names and the ease of finding specific values.']}], 'duration': 162.837, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/fsL57bvd7Pk/pics/fsL57bvd7Pk69.jpg', 'highlights': ['Creating a searchable dropdown list in Microsoft Excel', 'Example of creating an order form for cookies', 'Implementation ease on the web compared to desktop']}, {'end': 672.905, 'segs': [{'end': 207.856, 'src': 'embed', 'start': 163.446, 'weight': 0, 'content': [{'end': 165.748, 'text': "On desktop though, it's a different story.", 'start': 163.446, 'duration': 2.302}, {'end': 171.251, 'text': "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.", 'start': 165.808, 'duration': 5.443}, {'end': 173.013, 'text': "Let's jump over to desktop.", 'start': 171.632, 'duration': 1.381}, {'end': 185.021, 'text': "Here I am in Excel desktop, and to set up a searchable dropdown list, we're going to use a few functions that require a Microsoft 365 subscription.", 'start': 173.493, 'duration': 11.528}, {'end': 188.684, 'text': "First, we're going to create a basic dropdown list.", 'start': 185.641, 'duration': 3.043}, {'end': 196.229, 'text': "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.", 'start': 189.344, 'duration': 6.885}, {'end': 200.812, 'text': "Over here right near the middle there's an option called data validation.", 'start': 197.109, 'duration': 3.703}, {'end': 201.712, 'text': 'Click on this.', 'start': 201.112, 'duration': 0.6}, {'end': 207.856, 'text': 'This opens up the data validation prompt, and this is pretty similar to what we saw on the web.', 'start': 202.373, 'duration': 5.483}], 'summary': 'To set up a searchable dropdown list in excel desktop, use microsoft 365 functions and data validation.', 'duration': 44.41, 'max_score': 163.446, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/fsL57bvd7Pk/pics/fsL57bvd7Pk163446.jpg'}, {'end': 255.178, 'src': 'embed', 'start': 229.67, 'weight': 2, 'content': [{'end': 236.534, 'text': '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.', 'start': 229.67, 'duration': 6.864}, {'end': 239.998, 'text': "Now, let's say I just want to see our oatmeal cookies.", 'start': 237.094, 'duration': 2.904}, {'end': 242.882, 'text': "Here I'll type in oat and then let me click on this drop.", 'start': 240.118, 'duration': 2.764}, {'end': 243.883, 'text': 'Oh, look at that.', 'start': 243.122, 'duration': 0.761}, {'end': 245.845, 'text': 'There is a nasty error message.', 'start': 243.943, 'duration': 1.902}, {'end': 251.252, 'text': "Unfortunately, on desktop, you can't search for values like you can on the web.", 'start': 246.386, 'duration': 4.866}, {'end': 255.178, 'text': "Instead, we're going to have to build out some functions that allow us to do this.", 'start': 251.693, 'duration': 3.485}], 'summary': 'The desktop app lacks search functionality, requiring implementation of custom functions.', 'duration': 25.508, 'max_score': 229.67, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/fsL57bvd7Pk/pics/fsL57bvd7Pk229670.jpg'}, {'end': 380.539, 'src': 'heatmap', 'start': 312.17, 'weight': 0.817, 'content': [{'end': 321.016, 'text': "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.", 'start': 312.17, 'duration': 8.846}, {'end': 325.719, 'text': "This will open up a really nice helper that'll help us craft our function.", 'start': 321.436, 'duration': 4.283}, {'end': 328.661, 'text': "Here I'll type in search and then click on go.", 'start': 326.199, 'duration': 2.462}, {'end': 332.664, 'text': 'Next, click on the top match item, then click on OK.', 'start': 329.342, 'duration': 3.322}, {'end': 337.727, 'text': 'This opens up a prompt where I could define the text that I want to search for.', 'start': 333.104, 'duration': 4.623}, {'end': 341.59, 'text': "I want to search for oat, so here I'll click on oat right over here.", 'start': 337.747, 'duration': 3.843}, {'end': 350.436, 'text': "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.", 'start': 342.53, 'duration': 7.906}, {'end': 354.299, 'text': "So here I'll simply select all of our different cookie types.", 'start': 350.937, 'duration': 3.362}, {'end': 362.585, 'text': '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,', 'start': 354.84, 'duration': 7.745}, {'end': 364.066, 'text': "so I'm just going to leave this blank.", 'start': 362.585, 'duration': 1.481}, {'end': 366.608, 'text': 'And anyway, this is an optional argument.', 'start': 364.227, 'duration': 2.381}, {'end': 369.671, 'text': "I'm all done now filling this out, so I'll click on OK.", 'start': 366.929, 'duration': 2.742}, {'end': 375.275, 'text': 'Now that I entered this function in, I get a number back where it finds oat,', 'start': 370.251, 'duration': 5.024}, {'end': 380.539, 'text': 'and here I get a one back because it found oat in the first position of this cookie type name.', 'start': 375.275, 'duration': 5.264}], 'summary': "Demonstrates entering a search function in excel, finding 'oat' in cookie type names, and obtaining a result of '1' for the position.", 'duration': 68.369, 'max_score': 312.17, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/fsL57bvd7Pk/pics/fsL57bvd7Pk312170.jpg'}, {'end': 411.684, 'src': 'embed', 'start': 385.342, 'weight': 1, 'content': [{'end': 390.386, 'text': "Now that I've used the search function to find oat in all of our different cookie names,", 'start': 385.342, 'duration': 5.044}, {'end': 394.389, 'text': 'now I want to get a list back of just those items that have oat in them.', 'start': 390.386, 'duration': 4.003}, {'end': 397.731, 'text': "So over here I'll type in the filter function.", 'start': 394.969, 'duration': 2.762}, {'end': 402.235, 'text': 'This will basically allow us to filter the list down to the options with OAT.', 'start': 397.812, 'duration': 4.423}, {'end': 406.219, 'text': "Once again, let's click on the function helper right up here.", 'start': 402.956, 'duration': 3.263}, {'end': 411.684, 'text': "Within insert function, let's type in filter, then click on go, and click on the best match.", 'start': 406.719, 'duration': 4.965}], 'summary': 'Using the filter function to find and list items with oat in the cookie names.', 'duration': 26.342, 'max_score': 385.342, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/fsL57bvd7Pk/pics/fsL57bvd7Pk385342.jpg'}, {'end': 571.539, 'src': 'heatmap', 'start': 521.69, 'weight': 0.808, 'content': [{'end': 527.793, 'text': "Within the function arguments, let's select the array and that's all of the different cookie types that we offer here.", 'start': 521.69, 'duration': 6.103}, {'end': 531.995, 'text': 'Next, it wants to know what values we want to include.', 'start': 528.413, 'duration': 3.582}, {'end': 536.717, 'text': "And now we've gone through and we've defined whether or not it contains a number.", 'start': 532.395, 'duration': 4.322}, {'end': 541.34, 'text': 'If it contains a number, well, this is one of the options that we want in our dropdown list.', 'start': 537.158, 'duration': 4.182}, {'end': 543.981, 'text': "Here I'll highlight this entire column.", 'start': 541.86, 'duration': 2.121}, {'end': 548.725, 'text': 'Lastly, I can also specify what happens if no results come back.', 'start': 544.482, 'duration': 4.243}, {'end': 553.868, 'text': "Here I'll insert quotes and I'll simply type in no results and then I'll close the quotes.", 'start': 549.205, 'duration': 4.663}, {'end': 556.85, 'text': "This all looks good now, so I'll click on OK.", 'start': 554.569, 'duration': 2.281}, {'end': 563.115, 'text': "And check that out, the filter function returns the two oatmeal cookies that we're looking for.", 'start': 557.231, 'duration': 5.884}, {'end': 567.458, 'text': "So we're ready to bring these results now into our drop-down list.", 'start': 563.495, 'duration': 3.963}, {'end': 571.539, 'text': "Let's go back to the cell where we want to insert a drop-down list.", 'start': 568.098, 'duration': 3.441}], 'summary': 'Filter function selects two oatmeal cookies for dropdown list', 'duration': 49.849, 'max_score': 521.69, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/fsL57bvd7Pk/pics/fsL57bvd7Pk521690.jpg'}], 'start': 163.446, 'title': 'Excel dropdown and search functions', 'summary': 'Covers creating a searchable dropdown list in excel using microsoft 365 subscription and demonstrates the usage of search and filter functions to find and display specific data, resulting in a final list of options.', 'chapters': [{'end': 273.511, 'start': 163.446, 'title': 'Creating searchable dropdown list in excel', 'summary': 'Demonstrates how to create a searchable dropdown list in excel using microsoft 365 subscription, and explains the process step by step, including setting up a basic dropdown list and then transitioning to building a searchable dropdown list using three separate functions.', 'duration': 110.065, 'highlights': ['The chapter explains the process of setting up a basic dropdown list in Excel using the data validation option. This involves selecting the cell, accessing the data tab, choosing data validation, and specifying the source for the dropdown list.', 'It mentions the limitation of not being able to search for values like on the web when using Excel desktop. This is highlighted by the error message encountered while trying to search for specific values.', 'The chapter introduces the need to build out three separate functions to create a searchable dropdown list. It emphasizes the requirement of using additional functions to enable the search functionality in the dropdown list.']}, {'end': 672.905, 'start': 273.972, 'title': 'Excel search and filter functions', 'summary': "Covers the usage of search and filter functions in excel to find and display cookie types containing 'oat' and create a searchable dropdown list, resulting in a final list of two oatmeal cookie options.", 'duration': 398.933, 'highlights': ["The search function is used to find 'oat' in all cookie types, resulting in a number indicating the position where it finds 'oat', with only two types containing 'oat' out of all the options.", "The filter function is utilized to narrow down the list to only include options with 'oat', by using the ISNUMBER function to determine if a cell contains 'oat' and returning 'true' for oatmeal cookies and 'false' for others.", "A dropdown list is created using the filtered list of oatmeal cookies, allowing users to easily select from the two options with 'oat', providing a user-friendly way to view and select relevant cookie types."]}], 'duration': 509.459, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/fsL57bvd7Pk/pics/fsL57bvd7Pk163446.jpg', 'highlights': ['The chapter introduces the need to build out three separate functions to create a searchable dropdown list. It emphasizes the requirement of using additional functions to enable the search functionality in the dropdown list.', "The search function is used to find 'oat' in all cookie types, resulting in a number indicating the position where it finds 'oat', with only two types containing 'oat' out of all the options.", "A dropdown list is created using the filtered list of oatmeal cookies, allowing users to easily select from the two options with 'oat', providing a user-friendly way to view and select relevant cookie types.", 'It mentions the limitation of not being able to search for values like on the web when using Excel desktop. This is highlighted by the error message encountered while trying to search for specific values.', 'The chapter explains the process of setting up a basic dropdown list in Excel using the data validation option. This involves selecting the cell, accessing the data tab, choosing data validation, and specifying the source for the dropdown list.']}, {'end': 1103.144, 'segs': [{'end': 700.984, 'src': 'embed', 'start': 673.485, 'weight': 4, 'content': [{'end': 679.689, 'text': 'At this point, if you just need one row with a drop-down list, you can take all of this area over here,', 'start': 673.485, 'duration': 6.204}, {'end': 689.296, 'text': 'you can move it to a separate sheet or you could 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.', 'start': 679.689, 'duration': 9.607}, {'end': 695.14, 'text': "If you want multiple rows to be able to work, unfortunately, we're going to have to do a little bit more work.", 'start': 689.736, 'duration': 5.404}, {'end': 700.984, 'text': 'Here, for example, if I click on the cell with butter and I drag this down well, look at that.', 'start': 695.72, 'duration': 5.264}], 'summary': 'Options for managing drop-down lists in excel, including hiding columns and additional work for multiple rows.', 'duration': 27.499, 'max_score': 673.485, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/fsL57bvd7Pk/pics/fsL57bvd7Pk673485.jpg'}, {'end': 780.469, 'src': 'embed', 'start': 754.457, 'weight': 5, 'content': [{'end': 761.3, 'text': "Here, I'm going to enter search, and once again, I want to search for oat in all of our different cookie types.", 'start': 754.457, 'duration': 6.843}, {'end': 766.623, 'text': "This time, instead of putting all the cookie types on this sheet, I've placed it on a separate sheet.", 'start': 761.92, 'duration': 4.703}, {'end': 771.205, 'text': "So first off, I'm looking for oat, and then I need to specify where I want to search for it.", 'start': 767.163, 'duration': 4.042}, {'end': 777.147, 'text': "So I'll insert a comma, then I'll select my other sheet, and here I'll select all the cookie types.", 'start': 771.545, 'duration': 5.602}, {'end': 780.469, 'text': "So once again, I'm searching for oat in this list.", 'start': 777.587, 'duration': 2.882}], 'summary': "Searching for 'oat' in all cookie types on a separate sheet.", 'duration': 26.012, 'max_score': 754.457, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/fsL57bvd7Pk/pics/fsL57bvd7Pk754457.jpg'}, {'end': 817.468, 'src': 'heatmap', 'start': 754.457, 'weight': 0.828, 'content': [{'end': 761.3, 'text': "Here, I'm going to enter search, and once again, I want to search for oat in all of our different cookie types.", 'start': 754.457, 'duration': 6.843}, {'end': 766.623, 'text': "This time, instead of putting all the cookie types on this sheet, I've placed it on a separate sheet.", 'start': 761.92, 'duration': 4.703}, {'end': 771.205, 'text': "So first off, I'm looking for oat, and then I need to specify where I want to search for it.", 'start': 767.163, 'duration': 4.042}, {'end': 777.147, 'text': "So I'll insert a comma, then I'll select my other sheet, and here I'll select all the cookie types.", 'start': 771.545, 'duration': 5.602}, {'end': 780.469, 'text': "So once again, I'm searching for oat in this list.", 'start': 777.587, 'duration': 2.882}, {'end': 785.131, 'text': "This all looks good, so I'm going to close the parentheses and then hit enter.", 'start': 781.169, 'duration': 3.962}, {'end': 793.194, 'text': '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.', 'start': 785.771, 'duration': 7.423}, {'end': 795.836, 'text': 'So once again, just like we did before.', 'start': 793.295, 'duration': 2.541}, {'end': 804.06, 'text': "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.", 'start': 796.656, 'duration': 7.404}, {'end': 811.645, 'text': "Here I'm going to type isNumber as another function, and then I'll open the parentheses and close the parentheses.", 'start': 804.681, 'duration': 6.964}, {'end': 817.468, 'text': "So basically the output of the search, we're going to look at that to see if there's a number in that list.", 'start': 812.045, 'duration': 5.423}], 'summary': "Searching for 'oat' in cookie types, finding 2 with oat, then checking for numbers.", 'duration': 63.011, 'max_score': 754.457, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/fsL57bvd7Pk/pics/fsL57bvd7Pk754457.jpg'}, {'end': 874.825, 'src': 'heatmap', 'start': 841.609, 'weight': 2, 'content': [{'end': 845.071, 'text': 'So once again, we want to filter our list of cookie types.', 'start': 841.609, 'duration': 3.462}, {'end': 849.033, 'text': "So, right here, after equals, I'll type in filter,", 'start': 845.551, 'duration': 3.482}, {'end': 857.057, 'text': "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.", 'start': 849.033, 'duration': 8.024}, {'end': 863.3, 'text': "So here I'll click on the cookie type sheet and once again I'll select all of the different cookie types.", 'start': 857.697, 'duration': 5.603}, {'end': 871.684, 'text': 'Next I need to specify which values I want to include, and on the previous sheet we have a column with true or false,', 'start': 863.9, 'duration': 7.784}, {'end': 874.825, 'text': "and that's this formula that we already entered in.", 'start': 871.684, 'duration': 3.141}], 'summary': 'Filtering cookie types to include oat using specific formula', 'duration': 33.216, 'max_score': 841.609, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/fsL57bvd7Pk/pics/fsL57bvd7Pk841609.jpg'}, {'end': 907.816, 'src': 'heatmap', 'start': 882.549, 'weight': 0.732, 'content': [{'end': 887.81, 'text': 'Here at the very end, I want to specify what it should say if there are no cookie types that match.', 'start': 882.549, 'duration': 5.261}, {'end': 892.352, 'text': "And just like we did before, here I'll type in no results and then close my quotes.", 'start': 888.211, 'duration': 4.141}, {'end': 896.153, 'text': "Next, I'll close the parentheses and then hit enter.", 'start': 893.392, 'duration': 2.761}, {'end': 899.874, 'text': 'And here now you see that two options come back.', 'start': 896.793, 'duration': 3.081}, {'end': 907.816, 'text': "So just like we did before, the only difference now is we've combined all of these different functions together into just one formula.", 'start': 899.934, 'duration': 7.882}], 'summary': 'Demonstrating combining functions into one formula for handling no matching results.', 'duration': 25.267, 'max_score': 882.549, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/fsL57bvd7Pk/pics/fsL57bvd7Pk882549.jpg'}, {'end': 1008.602, 'src': 'heatmap', 'start': 908.536, 'weight': 3, 'content': [{'end': 916.822, 'text': 'For this dropdown to work on every single row, I need these results here to appear horizontally instead of vertically.', 'start': 908.536, 'duration': 8.286}, {'end': 920.224, 'text': "And luckily there's a function that will help us with that.", 'start': 917.362, 'duration': 2.862}, {'end': 926.369, 'text': "Here I'll click in the top cell and right after the equal sign, I'll type in transpose.", 'start': 920.825, 'duration': 5.544}, {'end': 930.912, 'text': "This will take a vertical list and it'll turn it so it's a horizontal list.", 'start': 926.849, 'duration': 4.063}, {'end': 933.434, 'text': "Here I'll open the parentheses at the beginning.", 'start': 931.393, 'duration': 2.041}, {'end': 935.035, 'text': 'I need to pass in an array.', 'start': 933.554, 'duration': 1.481}, {'end': 937.057, 'text': "That's basically this list right here.", 'start': 935.135, 'duration': 1.922}, {'end': 940.919, 'text': "At the very end, I'll close the parentheses and then hit enter.", 'start': 937.737, 'duration': 3.182}, {'end': 946.241, 'text': 'So here now you see I have a horizontal list of all of the different options that match OAT.', 'start': 941.399, 'duration': 4.842}, {'end': 951.224, 'text': "Now that our formula is ready to go, let's insert a drop-down list.", 'start': 947.062, 'duration': 4.162}, {'end': 955.206, 'text': "Here I'll click into the cell where I want to add a drop-down list.", 'start': 951.764, 'duration': 3.442}, {'end': 958.127, 'text': "Next, let's go up and click on data validation.", 'start': 955.706, 'duration': 2.421}, {'end': 962.91, 'text': "Within data validation, let's click on allow and then select list.", 'start': 958.628, 'duration': 4.282}, {'end': 968.671, 'text': "For the source, we'll simply click right here and once again let's include a hash.", 'start': 963.63, 'duration': 5.041}, {'end': 976.473, 'text': "That way that'll include all values that match and it could spread across multiple cells or what's referred to as the spill.", 'start': 969.171, 'duration': 7.302}, {'end': 982.654, 'text': "When I copy down my drop down list, I don't want it to be locked to just one individual row.", 'start': 976.913, 'duration': 5.741}, {'end': 985.895, 'text': "So right here there's a dollar sign in front of the six.", 'start': 983.054, 'duration': 2.841}, {'end': 991.576, 'text': "I'm going to remove that so when I copy the data validation down, it'll apply to the lower rows.", 'start': 986.355, 'duration': 5.221}, {'end': 998.638, 'text': "Up on top, let's also click on error alert and make sure that this checkbox is unchecked, just like we did before.", 'start': 992.236, 'duration': 6.402}, {'end': 1001.319, 'text': "We're all set now, so let's click on OK.", 'start': 999.038, 'duration': 2.281}, {'end': 1008.602, 'text': 'And check that out, here I have my drop down list and when I click on it, I see the two oatmeal cookies.', 'start': 1002.28, 'duration': 6.322}], 'summary': 'Using the transpose function, a horizontal drop-down list with multiple values was created for data validation in excel.', 'duration': 24.898, 'max_score': 908.536, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/fsL57bvd7Pk/pics/fsL57bvd7Pk908536.jpg'}, {'end': 985.895, 'src': 'embed', 'start': 955.706, 'weight': 0, 'content': [{'end': 958.127, 'text': "Next, let's go up and click on data validation.", 'start': 955.706, 'duration': 2.421}, {'end': 962.91, 'text': "Within data validation, let's click on allow and then select list.", 'start': 958.628, 'duration': 4.282}, {'end': 968.671, 'text': "For the source, we'll simply click right here and once again let's include a hash.", 'start': 963.63, 'duration': 5.041}, {'end': 976.473, 'text': "That way that'll include all values that match and it could spread across multiple cells or what's referred to as the spill.", 'start': 969.171, 'duration': 7.302}, {'end': 982.654, 'text': "When I copy down my drop down list, I don't want it to be locked to just one individual row.", 'start': 976.913, 'duration': 5.741}, {'end': 985.895, 'text': "So right here there's a dollar sign in front of the six.", 'start': 983.054, 'duration': 2.841}], 'summary': 'Demonstrating data validation settings for creating a dynamic dropdown list.', 'duration': 30.189, 'max_score': 955.706, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/fsL57bvd7Pk/pics/fsL57bvd7Pk955706.jpg'}, {'end': 1097.448, 'src': 'embed', 'start': 1065.519, 'weight': 1, 'content': [{'end': 1068.321, 'text': "If you don't have a table yet, you could also press Ctrl T.", 'start': 1065.519, 'duration': 2.802}, {'end': 1077.524, 'text': "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.", 'start': 1069.702, 'duration': 7.822}, {'end': 1085.345, 'text': "With a table as well, you can simply sort your table here and that'll automatically apply the sorting in your drop-down list.", 'start': 1078.044, 'duration': 7.301}, {'end': 1089.366, 'text': 'You could also use a function to sort, but this is just a little bit easier.', 'start': 1085.685, 'duration': 3.681}, {'end': 1090.686, 'text': 'Simply sort it here.', 'start': 1089.566, 'duration': 1.12}, {'end': 1091.627, 'text': 'All right.', 'start': 1091.127, 'duration': 0.5}, {'end': 1097.448, 'text': 'well, let me know down below in the comments would you personally order cookies from the Kevin Cookie Company?', 'start': 1091.627, 'duration': 5.821}], 'summary': 'Using a table allows for automatic inclusion and sorting of items in a drop-down list, simplifying the process.', 'duration': 31.929, 'max_score': 1065.519, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/fsL57bvd7Pk/pics/fsL57bvd7Pk1065519.jpg'}], 'start': 673.485, 'title': 'Creating excel dropdown lists', 'summary': 'Covers creating searchable and dynamic dropdown lists in excel, enabling efficient filtering and selection of data. it discusses moving logic to separate sheets, challenges of updating dropdown lists for multiple rows, and demonstrates using functions like filter and transpose.', 'chapters': [{'end': 817.468, 'start': 673.485, 'title': 'Creating searchable dropdown lists', 'summary': 'Discusses creating searchable dropdown lists using excel, including moving logic to separate sheets and the challenges of updating dropdown lists for multiple rows. it also demonstrates searching for specific items and applying functions to manipulate the output.', 'duration': 143.983, 'highlights': ['The chapter discusses the process of moving a drop-down list logic to a separate sheet or hiding columns to enable functionality without revealing the underlying logic.', 'Demonstrates the challenge of updating dropdown lists for multiple rows, highlighting the need for additional work to ensure functionality.', "Explains the process of searching for specific items, such as cookies with 'oat', and demonstrates how to use functions to manipulate the output, including identifying numbers within a list."]}, {'end': 1103.144, 'start': 817.908, 'title': 'Creating dynamic drop-down lists in excel', 'summary': 'Demonstrates how to create dynamic drop-down lists in excel by using functions like filter, transpose, and data validation, allowing for efficient filtering and selection of data, resulting in user-friendly spreadsheets and improved data management.', 'duration': 285.236, 'highlights': ['The chapter demonstrates how to use the filter function to filter a list of cookie types to include only those containing oats, resulting in two true values indicating the presence of oats. Use of filter function to identify and quantify cookie types containing oats.', 'The chapter explains the use of the transpose function to convert a vertical list of options into a horizontal list, enabling the creation of a horizontal drop-down list for improved user experience. Explanation of how the transpose function converts a vertical list into a horizontal drop-down list.', 'The chapter details the process of using data validation and the spill feature to create dynamic drop-down lists, allowing for efficient selection and management of data across multiple cells. Explanation of using data validation and the spill feature for creating dynamic drop-down lists.', 'The chapter showcases the advantage of using tables for managing data and creating drop-down lists, including the automatic inclusion of new items and the seamless synchronization of sorting within the table and drop-down list. Advantages of using tables for managing data and creating drop-down lists.']}], 'duration': 429.659, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/fsL57bvd7Pk/pics/fsL57bvd7Pk673485.jpg', 'highlights': ['The chapter details the process of using data validation and the spill feature to create dynamic drop-down lists, allowing for efficient selection and management of data across multiple cells.', 'The chapter showcases the advantage of using tables for managing data and creating drop-down lists, including the automatic inclusion of new items and the seamless synchronization of sorting within the table and drop-down list.', 'The chapter demonstrates how to use the filter function to filter a list of cookie types to include only those containing oats, resulting in two true values indicating the presence of oats. Use of filter function to identify and quantify cookie types containing oats.', 'The chapter explains the use of the transpose function to convert a vertical list of options into a horizontal list, enabling the creation of a horizontal drop-down list for improved user experience. Explanation of how the transpose function converts a vertical list into a horizontal drop-down list.', 'The chapter discusses the process of moving a drop-down list logic to a separate sheet or hiding columns to enable functionality without revealing the underlying logic.', "Explains the process of searching for specific items, such as cookies with 'oat', and demonstrates how to use functions to manipulate the output, including identifying numbers within a list."]}], 'highlights': ['The chapter details the process of using data validation and the spill feature to create dynamic drop-down lists, allowing for efficient selection and management of data across multiple cells.', 'The chapter showcases the advantage of using tables for managing data and creating drop-down lists, including the automatic inclusion of new items and the seamless synchronization of sorting within the table and drop-down list.', 'The chapter demonstrates how to use the filter function to filter a list of cookie types to include only those containing oats, resulting in two true values indicating the presence of oats. Use of filter function to identify and quantify cookie types containing oats.', 'The chapter explains the use of the transpose function to convert a vertical list of options into a horizontal list, enabling the creation of a horizontal drop-down list for improved user experience. Explanation of how the transpose function converts a vertical list into a horizontal drop-down list.', 'The chapter introduces the need to build out three separate functions to create a searchable dropdown list. It emphasizes the requirement of using additional functions to enable the search functionality in the dropdown list.', "A dropdown list is created using the filtered list of oatmeal cookies, allowing users to easily select from the two options with 'oat', providing a user-friendly way to view and select relevant cookie types.", "The search function is used to find 'oat' in all cookie types, resulting in a number indicating the position where it finds 'oat', with only two types containing 'oat' out of all the options.", 'The chapter explains the process of setting up a basic dropdown list in Excel using the data validation option. This involves selecting the cell, accessing the data tab, choosing data validation, and specifying the source for the dropdown list.', 'Implementation ease on the web compared to desktop', "Explains the process of searching for specific items, such as cookies with 'oat', and demonstrates how to use functions to manipulate the output, including identifying numbers within a list."]}