Of course, we only need to keep unique values.

the cell containing the test value (here cell I3).

Note: On some language versions of Excel you will need to use a semicolon ( ) instead of a comma. The options in your drop-down list will appear in the exact same order that you have typed them.

Indicate the column that contains the data to return A longer list of options might look like this: Red, Blue, Green, Purple, Orange, Yellow, Brown.

The condition will be the category selected 😉 Now we're going to build a new formula based on the result of the previous selection. This time, we will use the FILTER function.īecause the FILTER function extract values in function of a condition. With this trick, whatever the number of values returned by the array formula UNIQUE, the drop-down list will always displayed all the values. Next, we look at how to create multiple dependent drop. The # sign corresponds to the extended reference of the array formula. In this step-by-step tutorial, first we learn how to create a basic dropdown list in Microsoft Excel.

From Excel Ribbon, click Data tab > Data Validation. all cells which have a drop-down box is applied, and you wanted to edit.

Next, we will create a drop-down list in the menu Data> Data validationĬhoose the List option and write the following function In your excel spreadsheet, select all the cells which have reference to your drop-down list (In this example, select all rows from column Answers), i.e.

Drop down list in excel