Creating a dynamic Excel drop down list is very useful If you have a list that is updated frequently. This article shows you how to create a dynamic drop down list with the help of OFFSET and COUNTA functions.

Syntax

=OFFSET(title of list, 1, 0, COUNTA(column that includes the list)-1)

Steps

1. Click Define Name under FORMULAS tab in ribbon
2. Enter a name and clear Refer to formula box
3. Start formula with the =OFFSET( function
4. Select title of your list 'Create dynamic lists'!$B$2,
5. Type 1, to make the list start at the next row, after the title
6. Type 0, to make the list start in the same column
7. Continue with COUNTA( function to count filled cells
8. Select the whole column that includes the list 'Create dynamic lists'!$B:$B
9. Type )-1 to close the COUNTA function
10. Type ) to close the OFFSET function and finish the formula
11. Click OK to save the name.
12. Select the cell that will contains the drop down
13. Click Data Validation under DATA tab in ribbon
14. Select List in Allow drop down
15. Type your name into Source box, with an equal sign =List
16. Click OK to save data validation

How

We are using the OFFSET function's ability to return arrays to generate dynamic lists. Using COUNTA function to return row count gives  the entire formula the dynamic behavior we are looking for

The OFFSET function returns an array that starts from a specified reference and has specified height and width numbers. The first argument of the OFFSET function is a base reference followed by Rows and Cols arguments which defines the array's top left reference. For example; If the first argument is C5 and the other two arguments are -1 and 2 respectively, it refers to cell E4 which is 1 row above, 2 columns right.

After determining where the array starts, next two arguments, which are 4^th and 5^th, defines the size of the array. If we continue with the same example;

=OFFSET(C5, -1, 2, 3, 4) formula refers E4:H6 range.

We can make this output range dynamic by making its size dynamic. Because we have a vertical list through column B, we should make 4^th parameter, which refers to its Height, dynamic. The COUNTA function can help us to find the size of the list. Counting all nonblank cells in column B gives the size of our list. But, If your list contains a title which you may not want to include, decrease the value from COUNTA by 1 to eliminate the title.

Let's analyze our formula;

=OFFSET('Create dynamic lists'!$B$2,1,0,COUNTA('Create dynamic lists'!$B:$B)-1)

To create a dynamic drop down list in Excel, you need to use this formula in Data Validation. You can write the formula directly, however saving it in a name first makes it re-usability.

Also see the related article Excel’s Dynamic Charts: A Tutorial On How To Make Life Easier.