Make Dynamic Drop Down Lists that Auto Sort & Update from Unsorted data with Duplicates - 3 Methods

To make a workable and practical drop down list in Excel we need to get a sorted unique data for from the data range that is unsorted and has duplicates. And No VBA or Plugin is required for these Excel tricks.

In this tutorial we learning three methods to make dynamic drop down lists in Excel by extracting unique list of items from the data range that is not alphabetical order and has duplicates repeating more than once.

With correct source data, making dynamic drop down lists is easy using data validation tool. But if you have crooked data with duplicates and also unsorted we have to resort to advanced Excel methods.

In this video, starting with the oldest tool available in Excel and then finishing with the newest feature, we are learning many things along the way while making alphabetically sort dynamic drop down lists.

Time tags for this video on Excel dynamic drop down lists:
==============================================

1:45 - Method 1: Using Pivot Tables to extract and sort unique list of items from unsorted data with duplicates and then using OFFSET function to plug the extracted list in data validation to get the dynamic drop down list

9:45 - Method 2: Using Power Query to remove duplicates and sort the data out of crooked data range. Just one tool i.e. Power Query is needed and once wrapped in named range, you get a fully working reliable dynamic drop down list in alphabetical order.

13:25 - Method 3: Use Dynamic arrays, the newest and coolest feature in Excel, to get the unique list of data using UNIQUE function and sort it using SORT function in a matter of seconds. And yes the way you use dynamic array output in data validation to get drop down list is different than usual but I have you covered here too.

#MSExcel #DynamicDropdownlists #DataValidation #PivotTables #PowerQuery #DynamicArrays

[HasaanFazal]

Contents:

Method 1: 1:45
Method 2: 9:45
Method 3: 13:25

[lindsayross]

Excellent presentation as always. Thank you for using jargon followed by plain English. Double’s the learning experience.

[cathyb]

Very nice explanations. Looking forward to more from you.

[elisabetdiaz]

@Hasaan Fazal YOU ARE THE MAN! Thank you for this video.

[ricardomantovaniassis]

Very nice explanation.
Thanks for sharing 😃

[gvinodnair]

Very clear explanation and extremely valuable information. Thanks for sharing it. 👍

[amybolin]

Thanks for the video. This is awesome. I was doing this using a variety of offset and index functions. This is so much easier.

[anillpatel]

This is excellent Hasaan. Very useful. Thank you.

[meghanadighe]

Very nice techniques & explanation

[yasirbakshi]

Beautifully explained..tysm for the video.