UPDATED! Auto Expand Drop Down List Using Dynamic Array In Excel - Excel Tips and Tricks - video Dailymotion

RabiGurungXybernetics

Discover how you can auto expand drop down list in Excel. We will be answering how do I create an expandable drop-down list in Excel? And also how do I extend Data Validation list in Excel?. This video will also address question around how do I auto populate data in Excel based on a drop-down list, and how do I automate data validation in Excel?.   Here are the steps outlined in this video.   Auto Expand Drop Down List  1) Select cell E3  2) =UNIQUE(SORT(FILTER(D3:D20,NOT(ISBLANK(D3:D20)),"NA")))  3) Enter  4) Select cell B2  5) Data ~ Data Tools ~ Data Validation ~ Data Validation  6) Allow set to List  7) =$E$3#  8) OK   Let's breakdown the formula.  =UNIQUE(SORT(FILTER(D3:D20,NOT(ISBLANK(D3:D20)),"NA")))   'FILTER(D3:D20, NOT(ISBLANK(D3:D20))): This part filters the range D3:D20 by removing any blank cells. It only returns the non-blank cells from that range.   SORT(FILTER(D3:D20, NOT(ISBLANK(D3:D20)))): The SORT function sorts the non-blank cells obtained from the previous step in ascending order.   UNIQUE(SORT(FILTER(D3:D20, NOT(ISBLANK(D3:D20))))): The UNIQUE function then takes the sorted non-blank cells and removes any duplicates. It returns a list of unique values from the sorted list.   Finally, "NA" is used as the third argument for UNIQUE to specify how to handle error values. In this case, it means that if any error occurs during the calculation, the function will return "NA" as the result.   So, the overall purpose of this formula is to create a list of unique, sorted values from the range D3:D20, excluding any blank cells, and handling any potential errors with "NA" as a result.   🔗🔗 LINKS TO SIMILIAR VIDEOS 🔗🔗  Auto Expand Drop Down List In Excel - Excel Tips and Tricks  https://youtube.com/shorts/cvZB_9X1h3g?feature=share   Why is Drop Down List in Google Sheet better than in Excel - Excel Tips and Tricks  https://youtube.com/shorts/OI5YJUs7Sqc?feature=share     Auto expand drop down list in Excel, How do I create an expandable drop-down list in Excel?,  How do I extend Data Validation list in Excel?,How do I auto populate data in Excel based on a drop-down list?,How do I automate data vaRecently, I posted a video showcasing the process of creating an Auto Expand Drop Down List in Excel using OFFSET and COUNTA() functions,

Transcript

00:00Use this technique to auto expand or dynamically expand your drop-down list.

00:04Take a look at this. I'm going to use this column E here, select E3 here.

00:08In here, I'm going to use the following formula. Is blank not returns a true if the cell is not

00:13blank. Filter function filters the range of D2 to D20 based on is blank being not blank. In short,

00:20it returns a fruit name if the criteria are true, otherwise NA. Sort will put it in alphabetical

00:26order and unique will include distinct values in the drop-down list and hit enter here. Next,

00:32you're going to go to cell B2 here. Go to data, data tools, data validation, data validation here.

00:38Allow, you're going to change this to list. Under source, you're going to click this button and select

00:42this guy here. You're going to put a pound here because this is a dynamic list and you can expand

00:47and then hit enter and hit ok. Now you can see that you have a drop-down list and if you add more fruits

00:53here, this thing dynamically increases and hence your drop-down increases by itself.

UPDATED! Auto Expand Drop Down List Using Dynamic Array In Excel - Excel Tips and Tricks

Category

Transcript

Be the first to comment

Recommended