Create a Drop-Down List Filter in Excel & Google Sheets

This tutorial demonstrates how to create a drop-down list filter in Excel and Google Sheets.

 

 

We can use a drop-down list to extract rows of data that match the entry in the drop-down list, and return these rows to a separate area in our worksheet.

There are a number of steps we need to take to perform this action.

1. Create a unique list of items to appear in our drop down list as our data might contain repeating items.
2. Create the drop down list to filter our data.
3. Create the filter using helper columns that contain the formulas that we need to extract the data.

Create a Unique List

First, we need to highlight the items that we wish to appear in our drop down list.

 

Then we need to copy and paste this list into a separate area of the worksheet.  Once we have done that, we need to remove the duplicates.

Select the pasted list and then, in the Ribbon, select Data > Data Tools > Remove Duplicates.

Click OK, and then click OK again to remove the duplicates and return to Excel.  We now have a unique list for our drop down.

 

Creating the Drop Down List

Now we can create the drop down list for our filter.

First (1), select the cell where you wish to place the drop down and then, in the Ribbon, select (2) Data and then (3) Data Tools > Data Validation.

 

In the Data Validation window, (1) choose List in the Allow drop-down, and (2) click on the arrow next to the Source box.

 

Select a range of cells that contain the unique items that we created above and press ENTER.

 

Click OK to confirm and exit the Data Validation window.

You will now be able to select from the drop-down list.

 

Note: You can add your own error message for data validation. You can also add an input message to the cells with data validation in order to provide information on which values are allowed.

Create Helper Columns and Formulas to Extract the Data

We now get to the filter part of this procedure.

First, we need to create “helper” columns in our table of data.

To the right of the data table, insert three columns and label them Helper 1, Helper 2, and Helper 3.

Select the first cell in the Helper 1  column, and then type in the following ROWS formula:

=ROWS($B$3:B3)

Then, (2) copy the formula down to the remaining rows in the helper column.

This formula will give each row in your data table a number which will start at the first data row of the data table. Notice that it does not correspond with the row number in Excel.

Select the first cell in the Helper 2 column, and then (1), type in the formula:

=IF(C3=$J$3,F3,"")

Then, (2) copy the formula down to the remaining rows in the helper column.

This formula will show the row number from the Helper 1 column IF  the value in the drop down list (J3) is equal to the value in the same row in column C (C3).

 

Now select the first cell in the Helper 3 column and (1), type the following formula using the IFERROR and SMALL Functions:

=IFERROR(SMALL($G$3:$G$21,F3),"")

Then, (2) copy the formula down to the remaining rows in the helper column.

 

This formula will return the 1st, 2nd, 3rd, etc. smallest number in the Helper 2 column based on the row number in the Helper 1 column.

Now that we have created our helper columns, we can create our formula in our filter result table.

Click in the first cell of the filter result table (eg L3), and then (1) type in the following formula using the IFERROR, INDEX, and COLUMNS Functions:

=IFERROR(@INDEX($B$3:$E$21,$H3,COLUMNS($L$3:L3)),"")

Then, copy this formula (2) down and then (3) across to fill up the remaining cells in the filter result table.

Depending on what value you have selected in the drop down list, the filter results should show all the results for that particular value.

In our example we have a list of product orders and we have filtered to show the orders where the product in the drop down list selected is “Headphones”.

We can now amend our selection in the drop down list to show a different list of orders.

Note: To make the worksheet more aesthetically pleasing, and to protect the formulas in the helper columns, I have hidden the helper columns.  To do this, right-click on the columns and selected Hide.

 

Create a Drop-Down Filter in Google Sheets

The drop-down filter works the same in Google Sheets as it does in Excel.  You have to follow the steps described above to create the conditional formatting drop down list, and then the three helper columns.  The formulas for these columns are identical to Excel.

When we create the final filter formula however, there is one slight difference:

=IF($H3<>"",INDEX($B$3:$E$21,$H3,COLUMNS($L$3:L3)),"")

We need create an IF statement to check if the value in the helper column 3 is there.  If the value is there, we can run the Index Formula, but if the value is not there, we will not run a formula.