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.

Excel OFFSET Function explained with examples:

!! Get FREE Excel CORE BOOK:

[] For more Excel lessons and tutorials visit:

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

drop down lists,sort drop down list alphabetically excel,extract unique items from a list,data validation,Ms Excel,pivot table,power query,dynamic arrays,dynamic drop down list excel,offset data validation,excel tutorial,unique function,sort function,excel drop down list offset,get unique items for drop down excel,hasaan fazal,Excel tips and tricks,data validation drop down list excel,auto update drop down list excel,excel data validation,excel,