Populating a dropdown list with a dynamic list is easy thanks to Microsoft Excel’s UNIQUE() dynamic array function.
Microsoft Excel’s Data Validation feature provides an easy way to create a sheet-level drop-down list. If you’re basing the list on data, you probably want a list of unique items, which is easily done by Microsoft Excel’s UNIQUE() dynamic array function. Put them together and you have an easy way to populate the drop-down list with a unique list. In this tutorial, I’ll show you how to merge these two elements to populate a drop-down list with a unique list.
SEE: Windows, Linux, and Mac Commands Everyone Should Know (Free PDF) (TechRepublic)
I’m using Microsoft 365 desktop 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 .xlsx demonstration 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:
UNIQUE(matrix, [by_col]† [exactly_once]†
The array argument is the Excel range you want to reduce to a unique list. The by_col argument is a Boolean value: TRUE compares columns and returns unique columns; FALSE is the default and compares rows to rows and returns unique rows.
The exact_once argument is also a Boolean value: TRUE returns all individual rows or columns that occur exactly once in the range or array; FALSE, the default, returns all individual rows or columns from the range.
Now let’s use this function to create a unique list.
Create a unique list in Excel
Microsoft Excel supports two different drop-down menus: data validation and a combo box.
For now, the only dropdown list that is dynamic is Data Validation, so this tutorial does not cover the combo box. By dynamic I mean everything is automatically updated as you update the source data.
Image A shows a simple table called Commission2 – that’s the source data. As you can see, the Region values are repeated. The first thing we need is a unique list of values from the Region column.
That’s where UNIQUE() comes in. You can use Excel’s advanced filtering feature to create a unique list, but you need to update it when you change the source data. With UNIQUE() we can create an array result set that is automatically updated.
Now let’s use UNIQUE() to create a unique list of region values and then base a data validation dropdown on that list. First, let’s create the list shown in column H. Enter the following expression in H3:
if you are not using a Table object. However, this technique is not dynamic unless 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 containing a sorted unique list of region values from the Commission2 table. You can see that the result is an array because H3 is the only cell that contains an expression and Excel displays a blue border around the list.
Now that the list is in place, it’s time to build a data validation list on it.
Basing a data validation dropdown on an array in Excel
Now we are ready to create the data validation drop-down list that displays the unique list in column H. To get started, select J2 and do the following:
- Click 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 enter =$H$3:$H$7 as the source (Figure C†
- Click OK.
Click on the new drop-down list to see the unique list, displayed in Figure D† I’ve formatted the cell so that you can easily find the drop-down list.) You’d think we’re done, but the drop-down list is dynamic. Let’s see what that means.
Return to the Commission2 table and select the last cell, F13. Press Tab and Excel displays a new record. Go to the Region column and enter Southcentral.
As you can see in Figure ENot only does the dynamic array list in column H update, but also the Data Validation dropdown!
Filling a drop-down list with a unique list isn’t difficult, but it’s much easier if you use UNIQUE(). If you want to learn more UNIQUE() techniques, read Using the UNIQUE() function to return a count of unique values in Excel†