Populating a drop-down list with a dynamic list is easy with Microsoft Excel’s UNIQUE() dynamic array function.
Microsoft Excel’s Data Validation feature provides an easy way to create a drop-down list at the sheet level. If you’re basing the list on data, you’ll probably want a list of unique items, which is easily achieved using Microsoft Excel’s UNIQUE() dynamic array function. Put them together and you have an easy way to populate the dropdown with a single list. In this tutorial, I will show you how to put these two elements together to populate a drop down list with a single list.
SEE: Windows, Linux, and Mac commands everyone should know (free PDF) (TechRepublic)
I am using Microsoft 365 office on a Windows 10 64 bit system. Excel’s UNIQUE() function is only available with a Microsoft 365 subscription, Excel for the web, Excel 2021, Excel for iPad, iPhone, and Android tablets and phones. For your convenience, you can download the demo .xlsx file.
What is Excel’s UNIQUE() function?
The dynamic array function, UNIQUE() has been around for a while, so you may already know how to use it. If so, feel free to skip this section. As a standalone function, UNIQUE() returns a list of unique values using this syntax:
SINGLE(array, [by_col], [exactly_once])
Array argument is the Excel range that you want to reduce to a single list. The by_col argument is a Boolean value: TRUE compares columns and returns unique columns; FALSE is the default and will compare rows to each other and return unique rows.
The exactly_once argument is also a Boolean value: TRUE returns all distinct rows or columns that occur exactly once in the range or table; FALSE, the default, returns all distinct rows or columns in the range.
Now let’s use this function to create a unique list.
How to Create a Single List in Excel
Microsoft Excel supports two different drop-down lists: data validation and a combo box control.
For now, data validation is the only dynamic combo box, so this tutorial won’t cover combo box control. By dynamic, I mean everything updates automatically when you update the source data.
Figure A displays a simple table named Commission2 — this is the data source. As you can see, the values in the region repeat. The first thing we need is a unique list of values from the Region column.
This is where UNIQUE() comes in. You can use Excel’s advanced filter feature to create a unique list, but you must update it when you change source data. By using UNIQUE() we can create an array result set that updates automatically.
Now let’s use UNIQUE() to create a unique list of region values, then base a data validation dropdown on that list. Let’s start by creating the list displayed in column H. Enter the following expression in H3:
=SORT(UNIQUE(Drop-down list !$E$3 :$E$13))
if you are not using a Table object. However, this technique will only be dynamic if the source data is in a Table object. Technically SORT() is not necessary, but a sorted list is easier to use.
Figure B displays the results — a dynamic array that includes a single sorted list of region values from the Commission2 table. You can tell the result is a table because H3 is the only cell that contains an expression and Excel shows a blue border around the list.
With the list in place, it’s time to base a data validation list on it.
How to base a data validation drop down list on an array in Excel
We are now ready to create the Data Validation drop-down list which will display the single list in column H. To start, select J2 and then do the following:
- Click on the Data tab.
- Click Data Validation in the Data Tools group.
- In the resulting dialog box, choose List from the Allow drop-down list.
- Highlight or type =$H$3:$H$7 as Source (Figure C).
- Click OK.
Click on the new drop-down menu to see the single list, displayed in Figure D. I’ve formatted the cell to make it easy to find the drop-down list.) You might think we’re done, but the drop-down list is dynamic. Let’s see what that means.
Go back to the Commission2 table and select the last cell, F13. Press Tab and Excel will display a new record. Tab to the Region column and enter Southcentral.
As you can see in Figure Enot only the list of dynamic tables in column H is updated, but also the Data Validation drop-down list!
Populating a dropdown with a unique list isn’t difficult, but it’s much easier when you use UNIQUE(). If you want to learn more about UNIQUE() techniques, read How to Use UNIQUE() Function to Return a Count of Unique Values in Excel.