Excel Conditional Drop-Down Lists Using Dynamic Arrays - Episode 2248
4,953 views · Published 18 October 2018 · 3:18 · Indexed 23 October 2026
Channel: MrExcel.com · 2018 · Education
Microsoft Excel Tutorial: How to set up Dependent Validation in Excel Using New Dynamic Arrays. This is also known as Conditional Drop-Down Lists or Cascading Data Validation. Welcome to another episode of Learn Excel From MrExcel! In today's podcast, we will be discussing how to use dynamic arrays for dependent validation. This topic has been addressed twice before on the podcast, but with the new dynamic arrays feature announced in September 2018, it has become much easier to implement. However, please note that this feature is currently only available for Office 365 users and is still being rolled out gradually. To begin, we have two formulas - the UNIQUE formula and the SORT formula. The UNIQUE formula returns all the classifications and the SORT formula sorts them in alphabetical order. This gives us one formula that returns five results, which we will place in cell D4. Next, we will use this formula as the source for our data validation in cell E4. The # symbol ensures that all the results from D4 are returned, even if we add a new category in the future. Now, let's move on to the more complex part - filtering the list in column A based on the chosen category in column B. We will use the FILTER formula for this, with the criteria being the chosen category in cell H3. However, we also need to account for the scenario where no category has been chosen yet. For this, we will use the third argument in the FILTER formula, which will display "Choose Class First" if no category is selected. This ensures that the user is prompted to choose a category before the list is filtered. If you're not familiar with dynamic arrays, I highly recommend checking out my book "Excel Dynamic Arrays", which is available for free download until the end of 2018. It includes this example and 29 others to help you understand and utilize this feature to its full potential. In conclusion, dynamic arrays have made dependent validation much easier to implement in Excel. If you're not on Office 365 and don't have access to this feature yet, you can refer to our previous podcast (episode 1606) for the old method. Thank you for tuning in, and don't forget to check out our other netcasts for more Excel tips and tricks. See you next time! Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/ You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/ #excel #microsoft #exceltricks #excelhacks #microsoft365 #excelnew #evergreen This video answers these common search terms: excel cascading dropdowns conditional drop-down list excel how to do conditional data validation in excel how to make conditional data validation excel how to create conditional data validations in excel dynamic validation lists in excel how to make dynamic data validation excel how to have a dynamic data validation list excel what is data validation in excel and how to use it how to create two data validation lists in excel how to find data validation on excel how to do an offset data validation in excel excel how do you do data validation how to create data validation drop down in excel excel dependent drop-down list dependent data validation excel linked dropdowns in excel excel dropdown based on another cell Table of Contents: (00:00) Explanation of Dependent Validation Using Arrays (00:24) Introduction to Dynamic Arrays (00:51) Two Formulas for Dependent Validation (01:04) Setting Up Data Validation (01:22) Explanation of First Validation Formula (01:32) Explanation of Second Validation Formula (01:43) Importance of Choosing a Category First (02:03) Use of "Choose Class First" Argument (02:27) Explanation of Data Validation (02:43) Clicking Like really helps the algorithm Can you set up Excel Data Validation so that the choices in a second drop-down menu are dependent on the choice in the first menu? For example, if you choose Bagels, the answers might be Raisin, Tomato, or Everything. If you choose Pancakes, then the answers would be Buckwheat, Blueberry, or Pumpkin. This has been solved twice before on my channel using Names or using OFFSET. Today, with the introduction of new Dynamic Array formulas, there could be an easier way. Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1154077/
More from this channel
-
2:13
Excel Calculate Decimal Hours from two Time Cells - Episode 902
-
3:57
Excel 2007 - Color of Selected Sheets is too much like unselected sheets - Episode 907
-
2:48
Excel - Adding Equals Icon to Excel QAT - Build Formula with Mouse - Episode 911
-
3:30
Excel Grouping Percentages into Buckets for a Pivot Table - Method 2 - Episode 913
-
2:04
Excel Change Color of Selected Cells - Episode 914
-
4:38
Excel - Auto Numbering a List with Frequent Inserts & Deletes - Episode 916
-
2:00
Excel Hiding Data in Plain Site - Episode 919
-
2:20
Excel - Bring Back The Full Excel 2003 Dialogs In Excel! - Episode 920