How to Create Drop Down List with Filter in Excel (7 Methods)

In Microsoft Excel, data entry becomes faster when we use a drop-down list filter. Filters may be used to filter down and conceal sections of our worksheet’s data. In this article, we will demonstrate how to create excel drop down list filter.

You can download the workbook and practice with them.

7 Different Ways of  Creating Drop Down List with Filter in Excel

Filtering differs from grouping in that it allows us to qualify and show just the information that is relevant to us. Let’s have a look at some easy ways to create excel drop-down list filter.

1. Create a Drop Down List

In this method, we will see how we can create a drop-down list filter. For this, we are going to use the dataset below. The dataset contains some candidate names in column B. Now, we want to make a list of the candidates if they are selected or not in column C. We will create a drop-down list filter to make the work done easily. To do this, we need to follow the steps below.

• First, select the cells where we want to create the drop-down list filter.
• Second, click on the Data tab on the ribbon.
• Third, we need to go to the Data Validation drop-down menu.
• Fourth, select the Data Validation from the drop-down menu.

• This will open up the Data Validation dialog box.
• In the Settings option, we can see the Validation criteria.
• Now, click on the drop-down menu under Allow.
• By default, Any value is selected. We will change it to List.

• This will show a box named Source. We will write Yes, No, Not Decided Yet in the source box.
• Then, click on the OK button.

• Finally, we can see the result. Our selected cells are now drop-down list boxes.
• Now, we can easily make a list of who is selected.

• If we need to make changes to data, we can quickly do that.

Read more: How to Create Dependent Drop Down List in Excel

2. Excel Drop Down List Filter to Extract Data

In this method, we will see how to extract data or filter data based on a drop-down list selection in excel. So, here we have a dataset that contains some product id in column B, the name of the products in column C, and the county name in column D.

2.1. Make a List of Unique Items

We will make a unique list of the countries. To do so, let’s have a look at the steps below.

STEPS:

• Firstly, select the counties which are in column D.

• Secondly, paste the selected countries at any of the other cells in the worksheet.

• After that, go to the Data tab from the ribbon.
• Then, click on Remove Duplicates.

• This will appear in the Remove Duplicates dialog box.
• Now, check whether the column we want to make the unique list is selected or not.
• Then, click OK.

• A pop-up window will appear, confirming that the duplicate values were removed from the selected column.

• In the end, we can see that 2 duplicate values are removed and 4 unique values are remaining.

2.2. Put a Drop Down Filter to Show Unique Items

To show the unique values in a drop-down list filter we need to follow the same as shown.

STEPS:

• In the beginning, go to the Data tab.
• After that, click on the Data Validation drop-down menu.
• Now, select Data Validation.

• The Data Validation dialog box will appear.
• At this moment, select List from the drop-down.

• Next, click on the upper arrow in the Source section.

• Now, select the unique values that we have generated.
• Hit Enter.

• At this time, we can see that the selected unique values are in the source section.
• Click OK.

• By doing this, the drop-down list is now shown in I2.

2.3. Use Helper Columns to Extract the Records

We need excel to identify the records automatically that corresponds to the selected item as soon as we make a drop-down selection. For this, we need three helper columns. Let’s look at the steps below how we can do this.

STEPS:

• In the first helper column, we need the row number for each of these cells. So, E5 would be row number 1 in the dataset and E6 would be row number 2, and so on. To do this, we can manually hard code or use the ROWS formula.
• ROWS formula takes the input as an array and returns the number of rows between the two cell references. In our example, in cell E5, there is only one row.
• Lock the first cell by pressing F4 or putting ($) dollar sign.
• Now, write down the formula.

• Then, press Enter.

• Now, drag the fill handle to copy the formula to show all the rows.

• We can see that the cells are incremented by one because from D5 to D6 we have two rows and so on.

• Now, let’s create a helper column two which only shows those row numbers which match the country that we have selected in I2. We want those row numbers which have Bangladesh. So the helper column will show 1 and 4. To do that, we will use an IF condition.
• And, the condition is

• Now, drag the fill handle down to show the numbers.

• If we change the country, we can see the helper 2 columns will show the row number which contains the country.

• After that, we need another helper column in which all the numbers in helper column 2 will stack up together. Actually, we don’t want the gap in between. For this, we use the SMALL formula.
• Now, write down the formula below.

Here, we use ROWS($F$5:F5) to return the first smallest value.

• But, there is a problem. When we drag the fill handle down, it shows #NUM! errors.

• To avoid the error we will write the formula below.

This IFERROR function will remove the error.

• Finally, when we drag the fill handle, the row numbers will show properly.

• Now the final steps, the new three columns show the selected countries’ product IDs and product names. To do so, we will use a simple INDEX function that returns the product id as per the selected country.
• Now, in cell K5, write down the formula.

In the COLUMNS($H$5:H5), select the same column which is in the left parenthesis of the worksheet.

• Again, we can see that the #VALUE! error is showing up.

• To remove the error, we use the same as before, the IFERROR function.
• Instead of the previous formula now we will write.

• Finally, drag the fill handle over K5:M10.
• And, all the steps are done.

• If we change the country from the drop-down filter list, we can see that the right-sided table changes automatically.

Read more: How to Create Drop Down List in Excel with Multiple Selections

3. Excel Sort And Filtering Data from Drop Down List

In excel, there are lots of exciting tools which we can use in our daily work. Sort and Filter toolbar is one of the features we can easily make a drop-down list filter in our data. Likewise the above methods, We are going to use the same dataset, with product id, product name, and country.

3.1. Create Drop Down List Using Sort and Filter Feature

Let’s see how to use the sort and filter toolbar. For this, we need to follow the steps below.

STEPS:

• At first, select the headers of the dataset.
• Then, from the Data tab on the ribbon, click on Filter which is in the Sort & Filter section.

• This makes all the headers drop-down filter arrow.
• Now, click on any of the headers that we want to filter out.
• So, we click on the Product ID drop-down arrow to filter out the products.
• Now, uncheck the data we don’t want to view.
• Then, click on the OK button.

• Finally, we can see the result. All the unchecked products are now disappeared from the dataset. All the unchecked data are temporarily hidden now.

3.2. Add New Filter

To add new filters in the same dataset just follow these steps.

STEPS:

• In the first place, click the drop-down arrow where we want to add new filters. We will click on the country.
• In the second place, uncheck all the other countries we don’t want to view.
• Then, click OK.

• Now, we can see only the products with the country Bangladesh are now come out. Others are temporarily hidden.

3.3. Clear Existing Filter

If we need to clear the existing filter, we can simply clear those filters by following the steps.

STEPS:

• First, click on the header drop-down arrow which is filtered. We want to clear the filter from product identification.
• Now, click on Clear Filter From “Product ID”.

• And, that’s it. The drop-down list filters are removed now.

Read more: How to Create Dynamic Dependent Drop Down List in Excel

4. Filtering Data in Excel Using Search

By the same token, now we will see the drop-down data filtering using search. For this, we are using the same dataset as shown in the earlier methods.

STEPS:

• Primitively, select all the headers we want to make a drop-down box.
• After that, go to the Data tab > click on Filter.

• To filter a column, click the drop-down arrow in that column. We want to filter the product name column.
• Next, in the search box showing in the picture write down the product name we want to see. We want to see only the product name Shampoo.
• Then, click OK.

• And, now we can see that it will display only the data which contains the product name, Shampoo.

Read more: Excel Drop Down List Depending on Selection

Similar Readings

• Create Excel Drop Down List from Table (5 Examples)
• How to Create Excel Drop Down List with Color (2 Ways)
• Excel Drop Down List Not Working (8 Issues and Solutions)
• How to Create List from Range in Excel (3 Methods)

5. Text Filters in Excel Drop Down List Filter

To view the data more specifically, we can use text filters.

STEPS:

• To construct a drop-down box, choose all of the headings of the dataset.
• Then, go to the Data tab and select Filter.

• After that, click on the drop-down arrow, in the column of the text we want to filter. We click on the country column.
• Then, go to the Text Filters > Do Not Contain.

• At this point, a Custom AutoFilter dialog box will appear. Suppose we don’t want to contain any data with Canada. So, we select Canada.
• Then, OK.

• Now, we can see that all the data is now hidden which contains the country Canada.

Read more: How to Create Drop Down List in Multiple Columns in Excel

6. Number Filtering in Excel Drop Down List Filter

To manipulate numbers, we can use Number Filters. For this, we are going to use the dataset below.

STEPS:

• Correspondingly to the previous methods, select the headers.
• Go to Data tab > click on Filter.

• Click on the drop-down arrow of the column only which contains the numbers. We will click on product identification.
• Now, from Number Filters, select Between. Cause we want to see the product in between 105 -110.

• This will open up the Custom AutoFilter dialog box.
• Now, take the numbers we want to display.
• Then, click OK.

• Finally, the products id between 105-110 is displayed now, and others are hidden from the data.

7. Date Filters in Excel Drop Down List

To view the data in a certain time period, we can use the date filter. To do this, we are going to use the dataset below which is similar to the previous one but in addition, this dataset has a delivery date column. So, let’s look at the steps.

STEPS:

• Likewise, the other method, select the headers.
• From the Data tab, click on Filter.

• Click on the Delivery date drop-down arrow.
• Go to Date Filters. We want to display only the product which was delivered last month. So we select last month.

• Finally, we can see that all the products are displayed now which we delivered in the last month.

Conclusion

In this article, you learned about Excel Drop Down List Filter. Hope this will help you! If you have any questions, suggestions, or feedback please let us know in the comment section. Or you can have a glance at our other articles in the ExcelDemy.com blog!

Further Readings

• How to Create a Drop Down List From Another Sheet in Excel (2 Methods)
• Edit Drop-Down List in Excel (4 Basic Approaches)
• How to Make a Drop Down List in Excel (Independent and Dependent)
• VLOOKUP with Drop Down List in Excel