Helper Column #2 – Use this simple IF Function function: =IF(D4=$H$2,E4,””).

Helper Column #1 – Enter the serial number for all the records (20 in this case, you can use ROWS() function to do this).

Here are the steps to create helper columns: This can be done using three helper columns. Create Helper Columns to Extract the Records for the Selected ItemĪs soon as you make the selection from the drop down, you need Excel to automatically identify the records that belong to that selected item.

To do this, we would need to use helper columns and formulas. The goal now is to select any country from the drop-down list, and that should give us the list of records for the country.

In Settings tab, select “List” in the drop down, and in ‘Source’ field, select the unique list of countries that we generated.

In Data Validation dialogue box, select the Settings tab.

Here are the steps to create a drop down list in a cell: See Also : The Ultimate Guide to Find and Remove Duplicates in Excel. Now we will use this unique list to create the drop-down list. This will give you a unique list as shown below.

In the Remove Duplicates dialogue box, select the column where you have the list of countries.

Select all the Countries and paste it at some other part of the worksheet.

In the above example, the first step is to get the unique list of all the countries. While there could be repetitions of an item in your dataset, we need unique item names so that we can create a drop down filter using it. Let’s deep dive and see what needs to be done in each of these steps.

Use helper columns to extract the records for the selected item.

Add a drop-down filter to display these unique items.

Here are the steps to create a drop-down filter that will extract data for the selected item: Note that as soon as I select India from the drop-down filter, all the records for India are extracted.ĭownload the Example File Extract Data from Drop Down List Selection in Excel As soon as I select any country from the drop-down, the data for that country gets extracted to the right. In this tutorial, I will show you how to create a drop-down filter in Excel so that you can extract data based on the selection from the drop-down.Īs shown in the pic below, I have a created a drop-down list with country names. Watch Video – Extract Data Using a Drop Down List in Excel

Add a drop down list in excel