Unique drop down list excel
A dynamic drop down (also known as a dependent drop down) is a term for drop downs with a dynamic list. The list items of a dynamic drop down get updated according to another input which is usually another drop down as well. A common example is to have lots of items under different categories. Instead of trying to finding an item in a huge list, you can first select a category and then find your desired item in a filtered list. In this article, we are going to show you how to create a dynamic drop down list Excel using the dynamic array functions UNIQUE and FILTER. Show Designing the dynamic drop downsOur sample data consists of a 2-column table containing names and types. As a result, we need a drop down for each column. However, there 2 problems we need to address first:
There are multiple ways to create a dynamic drop down list Excel. Here are a few examples:
However, mos of these methods require an extensive use of formulas. Fortunately, the new dynamic array functions make things a lot easier. Let's take a look at the basics of the dynamic array functions UNIQUE and FILTER. UNIQUE and FILTERIn September, 2018, Microsoft has introduced new concepts like the dynamic arrays and spilling, in addition to a set of new functions, like the UNIQUE and the FILTER functions. The common characteristic of these two functions is their ability to return an array of values. We will be using this feature to populate the lists of unique items in our dynamic Excel drop down list. UNIQUE FunctionThe UNIQUE function returns an array of unique values from a given array or range. This function is very helpful in eliminating items that occur multiple times. If you have a data set similar to our example that contains multiple values, like under the column Type, this formula will help simplify those lists. When using the UNIQUE function, add the reference for the column of values you need, and the function will return a list of unique items. =UNIQUE(Type) *Type is named range referring C4:C17 For more information and examples for the UNIQUE function, please see our related guide. FILTER FunctionThe FILTER function, on the other hand, can filter an array of values by a criteria. The FILTER function accepts three (required) arguments:
For example, the following formula returns an array of values in a named range Name, where the values of the range Type is equal to the value of the DropdownType. Here, DropdownType is a single value, while "Name" and "Type" are ranges. =FILTER(Name,Type=DropdownType,"No Name Found") In the screenshot below, DropdownType is equal to "FIRE". Thus, only two values, "Arcanine" and "Entei", are returned by the formula. Finalizing the structureAfter this brief introduction, we can now create our dynamic drop down list Excel. Create a higher level drop down containing the unique valuesFirst step is creating a unique list of types and bind them to a drop down. In our example, we used the UNIQUE function on cell E4. The UNIQUE function spills its return array through the rest of the column. =UNIQUE(Type) Next is binding the spilled array into a drop down. To do this,
The # is the spill operator which saves us from guessing the size of the outcome array. Using # you can get all items returned from the UNIQUE formula. Create dynamic drop down with a dynamic listThis is where the FILTER function comes in. In our example, we named the ranges and used them in the formulas.
Our formula in this example is in cell E15: =FILTER(Name,Type=DropdownType,"No Name Found") The drop down procedure is very similar to previous one. The only difference is the reference of the cell contains the FILTER, =E15#. That's all! |