![]() ![]() However, this technique won’t be dynamic unless the source data is in a Table object. First, let’s create the list shown in column H. Now, let’s use UNIQUE() to create a unique list of region values and then base a data validation dropdown on that list. Base a Data Validation dropdown on the Region values. Using UNIQUE() we can create an array result set that updates automatically.įigure A Image: Susan Harkins/TechRepublic. 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. The first thing we need is a unique list of values from the Region column. As you can see, the Region values repeat. By dynamic, I mean that everything updates automatically as you update the source data.įigure A shows a simple Table named Commission2 - that’s the source data. Microsoft Excel supports two different dropdowns: Data Validation and a combo box control.įor now, Data Validation is the only dropdown that is dynamic, so this tutorial won’t cover the combo box control. Now, let’s use this function to create a unique list. 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. Top Tech Conferences & Events to Add to Your Calendar in 2023 How to Create a Local Account in Windows 11 Pro Support for these Microsoft enterprise products ends in 2023 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 array argument is the Excel range you want to reduce to a unique list. As a stand-alone function, UNIQUE() returns a list of unique values using this syntax: If that’s the case, feel free to skip this section. The dynamic array function, UNIQUE() has been around for a while, so it’s possible that you already know how to use it. For your convenience, you can download the demonstration. 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. ![]() I’m using Microsoft 365 desktop on a Windows 10 64-bit system. SEE: Windows, Linux, and Mac commands everyone needs to know (free PDF) (TechRepublic) In this tutorial, I’ll show you how to put these two elements together to populate a dropdown with a unique list. Put them together and you’ve got an easy way to populate the dropdown with a unique list. 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. ![]() Microsoft Excel’s Data Validation feature provides an easy way to create a dropdown at the sheet level. For more info, visit our Terms of Use page. This may influence how and where their products appear on our site, but vendors cannot pay to influence the content of our reviews. We may be compensated by vendors who appear on this page through methods such as affiliate links or sponsored partnerships. Populating a dropdown with a dynamic list is easy thanks to Microsoft Excel’s UNIQUE() dynamic array function. How to use UNIQUE() to populate a dropdown in Microsoft Excel ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |