Extract unique items for dynamic data validation drop down list

Excel Formula for dynamic drop-down lists

Excel extract unique items for drop-downHow can you extract unique items from a list that contains multiple instances of each item?

There are more “manual” ways you can do this – for example using the Remove duplicates feature or a Pivot Table;

But let’s say you’d like to use this list in a data validation drop-down and you’d like to have it automatic. So that anytime a new item is added to the list or the list expands, your data validation list is updated. The following video takes you through the steps.

In the first part of the video, I use formulas to extract the list of unique items to individual cells. My method includes a combination of the INDEX function together with MATCH and the COUNTIF function (this part will probably surprise you!). I’ll show you a way to avoid CSE (control shift enter) as well.

In the second part of the video, I use the OFFSET formula to create a dynamic list that expands depending on the number of items that should be included in the list.

The solution provided is dynamic so if you add new data or change data, everything updates automatically (we use Excel’s Table feature here).

Watch the video and follow along – you can download the Workbook below.

Feel free to Download the Workbook HERE.

Free Excel Download

The new Excel Dashboards course is here!

Now available on Udemy

Visually Effective Excel Dashboards for your reports.
Redeem your coupon for 75% off below.

Free Ebook

Leave A Comment

Share This