How to use UNIQUE() to populate a dropdown in Microsoft Excel

Populating a dropdown 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 dropdown at the sheet level. If you base the list on data, you’ll probably want a list of unique items, which is easily accomplished thanks to Microsoft Excel’s UNIQUE() dynamic array function. Put them together and you’ve got an easy way to populate the dropdown with a unique list. In this tutorial, I’ll show you how to put these two elements together to populate a dropdown with a unique list.

SEE: Windows, Linux, and Mac commands everyone needs to know (free PDF) (TechRepublic)

I’m using Microsoft 365 desktop on a Windows 10 64-bit system. Excel’s UNIQUE() function is available only 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 demonstration .xlsx file.

About Excel’s UNIQUE() function

The dynamic array function, UNIQUE() has been around for a while, so it’s possible that you already know how to use it. If that’s the case, feel free to skip this section. As a stand-alone function, UNIQUE() returns a list of unique values using this syntax:

UNIQUE(array, [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 will compare rows against rows and returns unique rows.

The exactly_once argument is also a Boolean value: TRUE returns all distinct rows or columns that occur exactly once from the range or array; FALSE, the default, returns all distinct rows or columns from the range.

Now, let’s use this function to create a unique list.

How to create a unique list in Excel

Microsoft Excel supports two different dropdowns: Data Validation and a combo box control.

For now, Data Validation is the only dropdown that is dynamic, so this tutorial won’t cover the combo box control. By dynamic, I mean that everything updates automatically as you update the source data.

Figure A shows a simple Table named Commission2 — that’s the source data. As you can see, the Region values repeat. 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 Filter feature to create a unique list, but you have to update it when you modify the source data. Using UNIQUE() we can create an array result set that updates automatically.

Figure A

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:

=SORT(UNIQUE(Commission2[Region]))

or

=SORT(UNIQUE(Dropdown!$E$3:$E$13))

if you’re not using a Table object. However, this technique won’t be dynamic unless the source data is in a Table object. Technically SORT() isn’t necessary, but a sorted list is easier to use.

Figure B shows the results — a dynamic array that comprises a sorted unique list of region values from the Commission2 Table. You can tell 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.

With the list in place, it’s time to base a data validation list on it.

Figure B

How to base a data validation dropdown on an array in Excel

Now we’re ready to create the data validation dropdown that will display the unique list in column H. To begin, select J2 and then do the following:

1. Click the Data tab.
2. Click Data Validation in the Data Tools group.
3. In the resulting dialog, choose List from the Allow dropdown.
4. Highlight or enter =$H$3:$H$7 as the Source (Figure C).
5. Click OK.

Figure C

Figure D

Click the new dropdown to see the unique list, shown in Figure D. I formatted the cell so it’s easy to find the dropdown.)You might think we’re done, but the dropdown is dynamic. Let’s take a look at what that means.

Return to the Commission2 Table and select the last cell, F13. Press Tab and Excel will display a new record. Tab over to the Region column and enter Southcentral.

As you can see in Figure E, not only does the dynamic array list in column H update, but so does the Data Validation dropdown!

Figure E

Populating a dropdown with a unique list isn’t difficult, but it’s much easier when you use UNIQUE(). If you’d like to learn more UNIQUE() techniques, read How to use the UNIQUE() function to return a count of unique values in Excel.