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

1 month ago 15

Populating a dropdown with a dynamic database is casual acknowledgment to Microsoft Excel’s UNIQUE() dynamic array function.

unique-dropdown-excel-tutorial-mayImage: prima91/Adobe Stock

Microsoft Excel’s Data Validation diagnostic provides an casual mode to make a dropdown astatine the expanse level. If you basal the database connected data, you’ll astir apt privation a database of unsocial items, which is easy accomplished acknowledgment to Microsoft Excel’s UNIQUE() dynamic array function. Put them unneurotic and you’ve got an casual mode to populate the dropdown with a unsocial list. In this tutorial, I’ll amusement you however to enactment these 2 elements unneurotic to populate a dropdown with a unsocial list.

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

I’m utilizing Microsoft 365 desktop connected a Windows 10 64-bit system. Excel’s UNIQUE() relation is disposable lone with a Microsoft 365 subscription, Excel for the web, Excel 2021, Excel for iPad, iPhone and Android tablets and phones. For your convenience, you tin download the objection .xlsx file.

What is Excel’s UNIQUE() function?

The dynamic array function, UNIQUE() has been astir for a while, truthful it’s imaginable that you already cognize however to usage it. If that’s the case, consciousness escaped to skip this section. As a stand-alone function, UNIQUE() returns a database of unsocial values utilizing this syntax:

UNIQUE(array, [by_col], [exactly_once])

The array statement is the Excel scope you privation to trim to a unsocial list. The by_col statement is simply a Boolean value: TRUE compares columns and returns unsocial columns; FALSE is the default and volition comparison rows against rows and returns unsocial rows.

The exactly_once statement is besides a Boolean value: TRUE returns each chiseled rows oregon columns that hap precisely erstwhile from the scope oregon array; FALSE, the default, returns each chiseled rows oregon columns from the range.

Now, let’s usage this relation to make a unsocial list.

How to make a unsocial database successful Excel

Microsoft Excel supports 2 antithetic dropdowns: Data Validation and a combo container control.

For now, Data Validation is the lone dropdown that is dynamic, truthful this tutorial won’t screen the combo container control. By dynamic, I mean that everything updates automatically arsenic you update the root data.

Figure A shows a elemental Table named Commission2 — that’s the root data. As you tin see, the Region values repeat. The archetypal happening we request is simply a unsocial database of values from the Region column.

That’s wherever UNIQUE() comes in. You tin usage Excel’s Advanced Filter diagnostic to make a unsocial list, but you person to update it erstwhile you modify the root data. Using UNIQUE() we tin make an array effect acceptable that updates automatically.

Figure A

 Susan Harkins/TechRepublic. Base a Data Validation dropdown connected  the Region values. Image: Susan Harkins/TechRepublic. Base a Data Validation dropdown connected the Region values.

Now, let’s usage UNIQUE() to make a unsocial database of portion values and past basal a information validation dropdown connected that list. First, let’s make the database shown successful file H. Enter the pursuing look successful H3:

=SORT(UNIQUE(Commission2[Region]))

or

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

if you’re not utilizing a Table object. However, this method won’t beryllium dynamic unless the root information is successful a Table object. Technically SORT() isn’t necessary, but a sorted database is easier to use.

Figure B shows the results — a dynamic array that comprises a sorted unsocial database of portion values from the Commission2 Table. You tin archer that the effect is an array due to the fact that H3 is the lone compartment that contains an look and Excel displays a bluish borderline astir the list.

With the database successful place, it’s clip to basal a information validation database connected it.

Figure B

Exceluniquedropdown_BImage: Susan Harkins/TechRepublic. The look returns a unsocial database arsenic an array.

How to basal a information validation dropdown connected an array successful Excel

Now we’re acceptable to make the information validation dropdown that volition show the unsocial database successful file H. To begin, prime J2 and past bash the following:

  1. Click the Data tab.
  2. Click Data Validation successful the Data Tools group.
  3. In the resulting dialog, take List from the Allow dropdown.
  4. Highlight oregon participate =$H$3:$H$7 arsenic the Source (Figure C).
  5. Click OK.

Figure C

Exceluniquedropdown_CImage: Susan Harkins/TechRepublic. Use the array database arsenic the source.

Figure D

Exceluniquedropdown_DImage: Susan Harkins/TechRepublic. The dropdown displays a unsocial database of regions.

Click the caller dropdown to spot the unsocial list, shown successful Figure D. I formatted the compartment truthful it’s casual to find the dropdown.)You mightiness deliberation we’re done, but the dropdown is dynamic. Let’s instrumentality a look astatine what that means.

Return to the Commission2 Table and prime the past cell, F13. Press Tab and Excel volition show a caller record. Tab implicit to the Region file and participate Southcentral.

As you tin spot successful Figure E, not lone does the dynamic array database successful file H update, but truthful does the Data Validation dropdown!

Figure E

Exceluniquedropdown_EImage: Susan Harkins/TechRepublic. This solution is dynamic acknowledgment to the root Table object.

Populating a dropdown with a unsocial database isn’t difficult, but it’s overmuch easier erstwhile you usage UNIQUE(). If you’d similar to larn much UNIQUE() techniques, work How to usage the UNIQUE() relation to instrumentality a number of unsocial values successful Excel.

Read Entire Article