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

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 Tip and Tricks

Why is Drop Down List in Google Sheet better than in Excel - Excel Tip and Tricks

#tip #excel #microsoft #shorts #short #shortvideo #shortsvideo #howto #how #google

[bali]

Wow! Beautiful formula! Nice and concise yet fully explained! THANK YOU!

[zdzislawkes]

This is a cool idea. But tell me, why don't You use a table instead? This is less complicated (then you don't need any formulas) and works when you need to insert data below D20 cell. Regards.