The list for the cell H2 drop-down is more complex, because we need to use two conditions in the FILTER function. Use the List in the Allow drop-down, click in the Source box, click cell L2, add the # and click OK. We can now create the drop-down for cell G2. =UNIQUE(FILTER(tblProd,tblProd=F2,РІР‚СњNoneРІР‚Сњ)) We can wrap the UNIQUE function around the FILTER function to remove duplicates. This list is not suitable for a drop-down list, as the entries are duplicated. The None in the formula specifies what to display if the condition is not met РІР‚вЂњ in this case, if cell F2 is empty. This formula lists all the Sizes (including duplicates) related to the product chosen in cell F2 (see Figure 5). The initial formula will use the FILTER function. I will create the list in cell L2 in two steps. The Size drop-down list (cell G2) is dependent on the Product chosen in cell F2. Click OK, and the first drop-down is ready.

This new symbol is part of the dynamic array functionality. Adding the # symbol to the end refers to the spill range that starts in cell K2. Click in the Source box and use the mouse to click on cell K2, then type # (see Figure 4). In the dialog that opens, select List from the Allow drop-down. Press Alt A V V in sequence, without holding down the keys. To create the drop-down for cell F2, we can use a keyboard shortcut to open the Data Validation dialog. This is part of the dynamic array feature included in the subscription version of Excel. Square brackets are used to define a column in the table.Р”Р‡igure 3 shows how the formula in cell K2 spills down (expands) to include the two products. TblProd is the name I have given the formatted table. We will create the list for the cell F2 drop-down in column K. We will create the drop-down list in the yellow cells in Figure 2, using the section on the right. This means the table range automatically expands as data is added to the table, and our solution will also be scalable and will adjust as new products are added to the table. The table in Figure 1 is a formatted table. (Note: Gadgets donРІР‚в„ўt have an extra-large size, and the colours vary between sizes.) We will use the table in Figure 1 as the basis for three separate drop-down selections. Unlike in previous versions of Excel, this solution is straightforward and scalable.

This reduces the amount of typing and errors.Р”Р„ dependent drop-down is one that populates its list based on a previous drop-down selection. A drop-down list is an input interface that lists options to choose from. The subscription version of Excel offers an easy way to create dependent drop-down lists.

How to create a drop down list in excel