MS Excel- Dynamic Drop-down list

Have you ever wondered that while filling a registration form when we select State, in the next field all the cities or districts pop-in related to that State only.

In this article, we will see how we can make dynamic drop down list. Let's understand this with the help of an example.

Let's say we have 5 states with corresponding cities:

Haryana - Karnal, Rohtak, Panipat, Yamunanagar

Punjab - Ludhiana, Amritsar, Jalandhar

HP - Shimla, Manali

UP - Lucknow

Maharashtra - Mumbai, Pune, Nagpur

Firstly, we need to convert this data into tabular form like this:

Please note, that while arranging the data into tabular form, keep all cities (related to one state) together as shown in the snapshot.

Copy State column and paste in another location (as shown here- column R), to remove duplicates from the list. The reason is when we apply drop down list, it should contain unique values of the States.

In order to apply the drop down list (shown here in column T), go to Data -> Data tools -> Data Validation.

?Given reference of the unique values in the Source: column R (from Haryana to Maharashtra). Click OK and you will see a drop down arrow in the Select State cell (cell in col. T in the above snapshot).

Now, we have to apply the main formula in order to get cities related to the State selected. This might be tricky for some users but I will try to keep it simple.

The logic is whatever state I select, it should go in the database table to fetch all the cities related to that state only. The reason that we have kept all the cities related to one State together because we will use the offset function to calculate the height.

Confused! Don't worry, let's do this, one step at a time.

The overall formula used is:

=OFFSET(L8,MATCH(O9,L9:L22,0),1,COUNTIF(L9:L22,O9))

In general offset function contains:

Offset(reference, rows, columns, [height], [width])

Let's take each argument one by one:

Offset(reference,........) = is the cell reference from where the offset function counts the rows and columns. In our case , we have taken L8 as reference cell. You can use any other cell of your wish as reference.

Offset(refernce,rows,......) = is the number of rows (up or down) from the reference cell. Either you can give an absolute number here or use any other function. e.g. if you give 2 here, it will refer to a cell that is 2 down from the reference cell.

In our case, we can not use absolute value as different states have different row nos. e.g. Haryana starts at row no. 9 , which is 1 row down the reference cell. Punjab starts at row no. 13 which is 5 rows down the reference cell.

So, we have used 'Match' function to solve this issue.

MATCH(O9,L9:L22,0)

In general, Match formula contains:

match(lookup_value,lookup_array,[match_type])

In our example, cell O9 will be treated as lookup_value. Based on this value, we have to show the relevant cities.

Lookup_array is L9:L22, which is the total database list.

And '0' in third argument means exact match.

Offset(reference,rows,columns,......) = is the no. of columns (left or right) from the reference cell. We have used '1' in the argument, it means it will offset 1 column to the right of the reference cell.

Guess what, if you wish to go to the left of the reference cell, give '-1' in the argument.

Offset(reference,rows,columns,[height],......)= height is the total no. of rows from the reference cell which you wish to take into account. e.g. if we have selected Haryana, we need to select 4 rows to take into account all the respective cities of Haryana. While if we have selected Punjab,we have to select 3 rows which show respective cities.

We can not give absolute value here as we have different no. of Cities for each State. Given the case, that you have same no. of cities for all the States, then we would have used that absolute value.

In our case we have used Countif function to tackle this.

COUNTIF(L9:L22,O9)

L9:L22 = is the database list of all the States

O9 = is the reference State for which it will count the value in the database. e.g. if O9 = Haryana, then it appears in the database for 4 times. If you select Punjab, countif function will give result as 3, because Punjab is listed 3 times in the database.

Offset(reference,rows,columns,[height],[width]) = for width we have not put any argument as in our case it is not required.

So here are we, Let's select Maharashtra in cell O9, and use the main formula in P9.

Oops! the result shows #Value!

Don't worry, in a cell you can only show one value, in our case Maharashtra has 3 cities. That is why, excel is showing error.

Copy the formula, press Esc, select cell P9, go to Data -> Data validation and paste the formula there.

There you go, select any State, and the respective Cities will be available in the drop-down.

Key takeaways:

1. Keep the data segregated with the cities of one State together. This will not work in case you add another City of Haryana at the bottom of the database. (I am yet to find solution for that. In case, you know, feel free to put in comments section)
2. Apply drop down list for States only on the unique values, to avoid duplicates in the list.
3. Arguments which are in [ ], are optional. You can keep them blank if not required. e.g. we have not used any value for [width] argument in the Offset function.

So, next time whenever someone asks you questions about dynamic drop down list, do not forget to share this article. :)

Feel free to comment in case you have any doubts.

#HappyLearning!