Populate drop down list with filtered Excel Table values

Author: Oscar Cronquist Article last updated on October 01, 2019

This article demonstrates how to populate a drop down list with filtered values from an Excel defined Table.

The animated image below demonstrates how values (First Name) in a drop down list changes based on how the table is filtered (Country).

How to build this

First we need to insert a new worksheet, this new worksheet will contain a formula that extracts filtered values from the Excel defined Table.

The drop-down list contains a named range that will fetch the extracted values from the new worksheet.

Calculation worksheet

Press with right mouse button on on any worksheet tab at the very bottom of the Excel screen.
Press with mouse on "Insert" to open a dialog box.
Select "Worksheet" icon.
Press with left mouse button on OK button.

To rename the worksheet simply press with right mouse button on on the worksheet tab and then press with left mouse button on "Rename".

Type a new worksheet name and press Enter, I named it "Calculations".

Formula

I entered a header name in cell B2.

Formula in cell B3:

=IFERROR(INDEX(Table2[First Name], SMALL(IF(SUBTOTAL(3, OFFSET(Table2[First Name], MATCH(ROW(Table2[First Name]), ROW(Table2[First Name]))-1, 0, 1)), MATCH(ROW(Table2[First Name]), ROW(Table2[First Name])), ""), ROWS($A$1:A1))), "")

Double press with left mouse button on cell B3.
Paste above array formula, shortcut CTRL + v.
Press and hold CTRL and SHIFT keys simultaneously
Press Enter once

The formula in the formula bar now looks like this: {=formula}
Don't enter these curly parentheses yourself, they appear automatically if you did the above steps correctly.

Explaining formula in cell B3

Step 1 - Create an array

The OFFSET function allows you to identify filtered values if you combine it with the SUBTOTAL function.

OFFSET(Table2[First Name], MATCH(ROW(Table2[First Name]), ROW(Table2[First Name]))-1, 0, 1)

becomes

OFFSET(Table2[First Name], {0; 1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15; 16; 17; 18; 19}, 0, 1)

and returns

{"Kaya"; "Fraser"; "Jui"; "Cyril"; "Spencer"; "Horace"; "Emmit"; "Lynna"; "Charley"; "Fraser"; "Mckinley"; "Tiara"; "Damien"; "Linnie"; "Jui"; "Fraser"; "Siena"; "Lynna"; "Kelton"; "Kaya"}.

This step may seem weird when you can just use a cell reference to the values, however, that won't work. This step is necessary in order to force the SUBTOTAL function to evaluate each value in the array.

Note, the OFFSET function is volatile meaning it is recalculated each time the workbook is recalculated. Extensive use may slow down your workbook considerably.

Step 2 - Identify filtered values

The SUBTOTAL function returns 0 (zero) if value is not visible and 1 if the value is visible.

SUBTOTAL(3, OFFSET(Table2[First Name], MATCH(ROW(Table2[First Name]), ROW(Table2[First Name]))-1, 0, 1))

becomes

SUBTOTAL(3, {"Kaya"; "Fraser"; "Jui"; "Cyril"; "Spencer"; "Horace"; "Emmit"; "Lynna"; "Charley"; "Fraser"; "Mckinley"; "Tiara"; "Damien"; "Linnie"; "Jui"; "Fraser"; "Siena"; "Lynna"; "Kelton"; "Kaya"})

and returns

{0; 0; 0; 0; 0; 0; 0; 0; 0; 1; 0; 0; 0; 0; 1; 0; 0; 0; 0; 1}

Step 3 - Return row numbers of filtered values

The IF function returns the corresponding row number of each filtered (visible) value and a blank for not visible values.

IF(SUBTOTAL(3, OFFSET(Table2[First Name], MATCH(ROW(Table2[First Name]), ROW(Table2[First Name]))-1, 0, 1)), MATCH(ROW(Table2[First Name]), ROW(Table2[First Name])), "")

becomes

IF({0; 0; 0; 0; 0; 0; 0; 0; 0; 1; 0; 0; 0; 0; 1; 0; 0; 0; 0; 1}, MATCH(ROW(Table2[First Name]), ROW(Table2[First Name])), "")

becomes

IF({0; 0; 0; 0; 0; 0; 0; 0; 0; 1; 0; 0; 0; 0; 1; 0; 0; 0; 0; 1}, {1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15; 16; 17; 18; 19; 20}, "")

and returns

{""; ""; ""; ""; ""; ""; ""; ""; ""; 10; ""; ""; ""; ""; 15; ""; ""; ""; ""; 20}

Step 4 - Extract the k-th smallest row number

The SMALL function extracts the k-th smallest number in a cell range or array.

SMALL(array, k)

SMALL(IF(SUBTOTAL(3, OFFSET(Table2[First Name], MATCH(ROW(Table2[First Name]), ROW(Table2[First Name]))-1, 0, 1)), MATCH(ROW(Table2[First Name]), ROW(Table2[First Name])), ""), ROWS($A$1:A1))

becomes

SMALL({""; ""; ""; ""; ""; ""; ""; ""; ""; 10; ""; ""; ""; ""; 15; ""; ""; ""; ""; 20}, ROWS($A$1:A1))

becomes

SMALL({""; ""; ""; ""; ""; ""; ""; ""; ""; 10; ""; ""; ""; ""; 15; ""; ""; ""; ""; 20}, 1)

and returns 10.

Step 5 - Return value based on row number

The INDEX function returns a value based on a row number (and column number if needed).

INDEX(Table2[First Name], SMALL(IF(SUBTOTAL(3, OFFSET(Table2[First Name], MATCH(ROW(Table2[First Name]), ROW(Table2[First Name]))-1, 0, 1)), MATCH(ROW(Table2[First Name]), ROW(Table2[First Name])), ""), ROWS($A$1:A1)))

becomes

INDEX(Table2[First Name], 10)

becomes

INDEX({"Kaya"; "Fraser"; "Jui"; "Cyril"; "Spencer"; "Horace"; "Emmit"; "Lynna"; "Charley"; "Fraser"; "Mckinley"; "Tiara"; "Damien"; "Linnie"; "Jui"; "Fraser"; "Siena"; "Lynna"; "Kelton"; "Kaya"}, 10)

and returns "Fraser" in cell B4 which is the 10th value in the array.

The IFERROR function removes errors that will show when there are no more values to show.

Create named range

Go to tab "Formulas" on the ribbon.
Press with left mouse button on "Name Manager" button.
Press with left mouse button on "New.." button to open the Name Manager dialog box.
Name: Drop_down_list
Refers to: =INDIRECT("Calculations!$B$3:$B$"&SUMPRODUCT((Calculations!$B:$B<>"")*1)+1)
Press with left mouse button on OK!
Press with left mouse button on Close!

Explaining named range formula

The formula used in name range Drop_down_list is dynamic and changes based on the number of values in column B in worksheet "Calculations.

Step 1 - Identify non empty cells in column B

The less and greater than signs combined checks if cells are empty or not.

Calculations!$B:$B<>""

becomes

{""; "Filtered Table values"; "Fraser"; "Jui"; "Kaya"; "" ... ""}<>""

and returns

{"FALSE";"TRUE";"TRUE";"TRUE"; "TRUE"; "FALSE"... ; "FALSE"}

It is not neccessary to check every cell in column B, if this calculation is slow for you then use a smaller cell reference, for example: Calculations!$B$1:$B$1000<>""

Step 2 - Count non empty cells in column B

The SUMPRODUCT function returns a number representing the total number of non empty cells.

SUMPRODUCT((Calculations!$B:$B<>"")*1)

becomes

SUMPRODUCT({"FALSE";"TRUE";"TRUE";"TRUE"; "TRUE"; "FALSE"... ; "FALSE"}*1)

The SUMPRODUCT function can't sum boolean values (TRUE or FALSE) so we need to convert them to their numerical equivalents, this is done by multiplying with 1.

SUMPRODUCT({0; 1; 1; 1; 1; 0... ; 0})

and returns 4.

Step 3 - Create cell reference

The ampersand allows you to combine text with a calculation.

"Calculations!$B$3:$B$"&SUMPRODUCT((Calculations!$B:$B<>"")*1)+1

becomes

"Calculations!$B$3:$B$"&4+1

becomes

"Calculations!$B$3:$B$"&5

and returns

"Calculations!$B$3:$B$5"

Step 4 - Convert text to cell reference

The INDIRECT function lets you use the text as a cell reference.

INDIRECT("Calculations!$B$3:$B$"&SUMPRODUCT((Calculations!$B:$B<>"")*1)+1)

becomes

INDIRECT("Calculations!$B$3:$B$5")

and returns the values in cell range Calculations!$B$3:$B$5.

Create drop down list

Select cell D26
Go to tab "Data" on the riboon.
Press with left mouse button on "Data Validation" button.
Allow: List
Source: =Drop_down_list
Press with left mouse button on Ok

Get the Excel file

Add-filtered-table-values-to-drop-down-list2.xlsx

Drop down lists category

Create dependent drop down lists containing unique distinct values
This article explains how to build dependent drop down lists. Here is a list of order numbers and products. We […]

Populate drop down list with unique distinct values sorted from A to Z
Question: How do I create a drop-down list with unique distinct alphabetically sorted values? Table of contents Sort values using […]

Apply dependent combo box selections to a filter
Josh asks: now if i only knew how to apply these dependent dropdown selections to a filter, i'd be set. […]

See all articles in 'drop down lists' category

Table category

How to use an Excel Table name in Data Validation Lists and Conditional Formatting formulas
This article demonstrates different ways to reference an Excel defined Table in a drop-down list and Conditional Formatting. There are […]

Extract unique distinct values in a filtered list
This article demonstrates two formulas that extract distinct values from a filtered Excel Table, one formula for Excel 365 subscribers […]

Count unique distinct values in a filtered Excel defined Table
This article demonstrates a formula that counts unique distinct values filtered from an Excel defined Table. Debra Dalgleish described in […]

See all articles in 'table' category

Named range category

Create a dynamic named range
A dynamic named range grows automatically when new values are added and also shrinks if values are deleted. This saves […]

Macro creates links to all sheets, tables, pivot tables and named ranges
This article demonstrates a macro that automatically populates a worksheet with a Table of Contents, it contains hyperlinks to worksheets, […]

List all named ranges and their cell references
This article shows you a way to display all named ranges you have in a workbook. This is a powerful […]

See all articles in 'named range' category

Functions in this article

IFERROR INDEX SMALL IF SUBTOTAL OFFSET MATCH ROW ROWS

More than 1300 Excel formulas

How to use Excel Tables

Create an Excel Table

Excel categories

Home page

Latest updated articles.

Excel Functions

More than 300 Excel functions with detailed information including syntax, arguments, return values, and examples for most of the functions used in Excel formulas.

Excel Formulas

More than 1300 formulas organized in subcategories.

Excel Tables

Excel Tables simplifies your work with data, adding or removing data, filtering, totals, sorting, enhance readability using cell formatting, cell references, formulas, and more.

Advanced Filter

Allows you to filter data based on selected value , a given text, or other criteria. It also lets you filter existing data or move filtered values to a new location.

Data Validation

Lets you control what a user can type into a cell. It allows you to specifiy conditions and show a custom message if entered data is not valid.

Drop Down List

Lets the user work more efficiently by showing a list that the user can select a value from. This lets you control what is shown in the list and is faster than typing into a cell.

Named Ranges

Lets you name one or more cells, this makes it easier to find cells using the Name box, read and understand formulas containing names instead of cell references.

Excel Solver

The Excel Solver is a free add-in that uses objective cells, constraints based on formulas on a worksheet to perform what-if analysis and other decision problems like permutations and combinations.

Charts

An Excel feature that lets you visualize data in a graph.

Conditonal Formatting

Format cells or cell values based a condition or criteria, there a multiple built-in Conditional Formatting tools you can use or use a custom-made conditional formatting formula.

Pivot Tables

Lets you quickly summarize vast amounts of data in a very user-friendly way. This powerful Excel feature lets you then analyze, organize and categorize important data efficiently.

VBA

VBA stands for Visual Basic for Applications and is a computer programming language developed by Microsoft, it allows you to automate time-consuming tasks and create custom functions.

Macros

A program or subroutine built in VBA that anyone can create. Use the macro-recorder to quickly create your own VBA macros.

UDFs

UDF stands for User Defined Functions and is custom built functions anyone can create.

7 Responses to “Populate drop down list with filtered Excel Table values”

Fowmy says:

August 28, 2012 at 10:05 am

It's a nice concept but, when you select Germany in you example, it fails. I think if filtered rows are in consecutive order, it works.

Reply
- Oscar says:
  
  August 29, 2012 at 9:19 am
  
  Fowmy,
  
  You are right! It fails, my fault. It looks like it can´t be done without using a helper column. I don´t like "helper" columns. Data Validation lists can´t handle non contiguous ranges.
  
  I even tried creating a named range (and udf) returning a text string using a comma as a delimiter, but that failed too.
  
  I am not sure what to do with this post.
  
  Here is an example file with a "helper" column:
  Add-filtered-table-values-to-drop-down-list2.xlsm
  
  The helper column is in sheet2.
David Hager says:

September 1, 2012 at 11:48 pm

I believe that you can get your idea to work if you create a custom function that returns a delimited string from the filtered cell values. Just be sure to credit me :)

Reply
- Oscar says:
  
  September 3, 2012 at 9:06 am
  
  David Hager,
  
  I can´t get it to work unless I use a named range. It seems the "drop down list" only accepts a cell range, a named range or a text string.
  
  I used this function:
```
Function VisibleValues(Rng As Range) As String
Dim Cell As Range
Dim Result As String
For Each Cell In Rng
    If Cell.EntireRow.Hidden = False Then
        Result = Result & Cell.Value & ", "
    End If
Next

VisibleValues = Left(Result, Len(Result) - 2)

End Function
```
  I can only get the custom function to work if I use it in a named range. But then the comma (text delimiter) won´t work. The drop down list shows all values in the same row.
  
  What am I missing?
  
  All credit goes to you :-)
jack B. says:

September 28, 2016 at 9:28 pm

Id like to make one that is the opposite of what your posted,

ie. the the column gets filtered based on the value you select from a drop down, the drop down is populated with all the unique values of whats present in your table's column

Reply
Rüdiger says:

June 5, 2019 at 2:24 pm

Function VisibleValues(Rng As Range) As String

Dim Cell As Range
Dim Result As String
For Each Cell In Rng
If Cell.EntireRow.Hidden = False Then
Result = Result & Cell.Value & Application.International(xlListSeparator)
End If
Next

VisibleValues = Left(Result, Len(Result) - 2)

End Function

Reply
- Oscar says:
  
  June 18, 2019 at 4:11 pm
  
  Rüdiger,
  
  Thank you for your contribution.
  
  I had to remove only 1 character to make it work for my example:
```
Function VisibleValues(Rng As Range) As String

Dim Cell As Range
Dim Result As String
For Each Cell In Rng
If Cell.EntireRow.Hidden = False Then
Result = Result & Cell.Value & Application.International(xlListSeparator)
End If
Next Cell

VisibleValues = Left(Result, Len(Result) - 1)

End Function
```

How to comment

How to add a formula to your comment
<code>Insert your formula here.</code>

Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >

How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]

How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.

Contact Oscar

You can contact me through this contact form