Using this formula, you can return the 1st, 2nd, and 3rd, etc numbers given in the Helper 2 column on the basis of the row number in the Helper 1 column. Once again repeat the procedure and copy the formula to all the other rows available in the helper column. In the Helper 3 column, now choose the first cell and enter the following formula with the IFERROR and SMALL Functions: Using this formula, you can see the row number from the Helper 1 column in case the given value in the list is equal to the value in the same row in column C. Now, repeat the same procedure as above and copy the formula in all other rows of the helper column. In the Helper column 2, choose the first cell and add this formula: Also, make sure it does not match the row numbers. Using this formula, every row will be assigned a number that begins from the first data row of the data table. Now, copy the formula and put it in the other rows available in the helper column. In the Helper column 1, click on the first cell and add in the ROWS formula given below: For this, add three columns to the right-hand side of the data table and you can name them Helper 1, Helper 2, and Helper 3. Now, you have to make “helper” columns in the data. Make Helper Columns and Formulas to Extract the Data Now, you can select items from the drop-down list. Press OK and close the Data Validation window. When the Data Validation window opens, you need to select the List in the Allow drop-down and click on the Arrow next to the Source box option.Ĭhoose a cell collection having unique values and press ENTER.

Now, click on the Data option and choose the Data Tools, and then the Data Validation option. You can make the drop-down list with filters.įor this, choose the cell that needed to be placed in the drop-down. Now, you have a unique list of the drop-downs. Press OK and click OK once again to remove the duplicates. Choose the list to be pasted and click on the Data option from the Ribbon and then open the Data Tools option. Make sure to remove the duplicates when it is completed. Now, copy and paste the list into another section on the sheet.

Microsoft excel drop down lists