How to Create Dynamic Drop Down List in Excel Using 4 Different Methods

In this article, we will learn how to create dynamic drop down list in Microsoft Excel.

As we know Data Validation feature improves the efficiency of data entry in excel and reduces mistakes and typing errors. It is used to restrict the user for the type of data that can be entered in the range. In case of any invalid entry, it shows a message and allows user to enter the data based on specified condition.

But a dynamic drop down list in Excel is a more convenient way of selecting data, without making any changes to the source. In other words, say you are going to update the list frequently which you’ve taken in drop down list. And, you are thinking if you make any changes in the list, you need to modify the data validation every time in order to get the updated drop down list.

But, this is where dynamic drop down comes into the picture, and it is the best option to select data without making any changes in the data validation. It is very similar to the normal data validation. However, when you update the list, the dynamic drop down list changes to accommodate that action, whereas the normal drop down list does not.

So, let’s take an example and understand how we create dynamic drop down list:-

We have a list of products in column A, and, we are going to have the dynamic drop down list of Products in cell D9.

image 1

 

Table Name with Indirect function

First, we will create table; follow the steps given below:-

  • Select the range A8:A16
  • Go to Insert Tab and then click on Table

image 2

 

  • After clicking on “Table” option, a Table window pops up
  • Then select the range for which we want to insert table A8:A17
  • Click on OK

image 3

 

  • Now, we click on OK
  • You can see this range has been converted into table, and header of this table has filter drop down option as well

image 4

 

Note: - If we add any product or item to the bottom of the list, the table will expand automatically to incorporate the new products or items.

Now we create the dynamic drop down list in cell D9, follow the steps given below:-

  • Select the cell D9
  • Open Data Validation dialog box , by pressing the key ALT+D+L
  • In Allow drop down list, select List
  • And then enter this function =INDIRECT(“Table1”) in source tab

image 5

 

  • Click on OK

image 6

 

Note: - When we click on OK, in Excel, window pops up saying that there is something wrong with the input. That’s because Excel does not accept any self-expanding table directly in the Data Validation.

Now add new products, in the product list.

image 7

 

We can see in the above image that new added product is appearing in the drop down list.

2nd Example:-

In this example, we will learn how to give the table name as ranged name

image 8

 

We already have the table name but here we have to define the name of this table to get the dynamic drop list; follow the steps given below:-

  • Select the cell D10
  • Go to table range, and except header, we select the range from first product to last product
  • Go to the name box and type short name “tablerange”, Press Enter

image 9

 

  • After pressing enter, we see nothing has changed in the name box

image 10

 

  • Click on the drop down list option to see all the named ranges available
  • In drop down list, we can see the name, which we just now defined for this table, also appears

image 11

 

  • Now, we go to Data Validation, and in “Source”, we enter the “tablerange”

image 12

 

Note:- If you do not remember what name you have given to that range, you can press F3 key and a window will pop up to suggest you all the named ranges available.

  • Now go to “Input Message” tab, and in title, we type “Select Product”, and then in message body, we write, “Please select your product from the list”

image 13

 

  • Now, go to “Error Alert” tab, and there in title, we write “Invalid Product”, and in error message, we type “You have entered wrong product

image 14

 

  • Click on OK
  • Cell D10 containing Input Message along with drop down list

image 15

 

image 16

 

  • Now when we add any product in the list, it will appear in drop down list automatically

image 17

 

But what happens when we skip one cell after last cell and then add new product or item? You can see, this time the table range has not expanded, and in fact, the newly added product is in general format. So, will it be showing in the drop down list or not? To check that, when we go to cell D10 and check the drop down list, we can see the same old drop down list with no new product. It is because the table range did not find anything after the very last cell and hence the range did not expend.

image 18

 

3rd Example:-

In the next two methods, we’ll learn how we can make our drop down list more dynamic by using OFFSET and COUNTA function.

Follow the steps given below:-

  • Select cell D11, and press ALT + D + L
  • Data Validation dialog box will open
  • Now select list in “Allow” option
  • Then in Source option, enter below formula:-

=OFFSET($A$9,0,0,COUNTA($A:$A),1)

image 19

 

 

Formula Explanation:-  We have selected A9, which is the first product in the range, and then we type 0 on the 2nd argument as we don’t want to move row from the starting point; then again 0 in the 3rd argument as here we don’t want any changes in the number of column as well as from the starting point. And then we have entered COUNTA function and have selected entire column A. This argument will check the height in number of rows to return the non-blank count. It will expand the range when any changes are made in the range.

And, the last argument “Width” is an optional argument. It is the width in number of columns. We can either skip it or can type 1 here for now. If we skip, it will, by default, consider the width of the returned range which we supplied in the argument and then we close the parentheses.

  • After clicking on OK, we can see a drop down list in cell D11
  • It shows the list including blank and then the products which we added

image 20

 

4th Example:-

In this example, we will use the function to define the name.

To define the range name, follow the steps given below:-

  • Press CTRL + F3, Name Manager dialog box will appear
  • Click on New
  • Define the range name “ProdName”, and enter below formula:-

=OFFSET('Dynamic Drop Down List with DV'!$A$9,0,0,COUNTA('Dynamic Drop Down List with DV'!$A:$A))

image 21

 

  • Click on OK
  • Open Data Validation dialog by pressing the key Alt + D + L
  • Select List in Allow drop down list
  • Enter =ProdName in Source tab

image 22

 

  • Click on OK
  • Now, if we add anything in the list then same will appear in the list

image 23

 

So, this is how you can get the dynamic list for any product or item with different methods using data validation. That’s all for now. In the next video of this series, we will explain how to create the dependent drop down list with different methods in Excel.

 

image 48

 

Video: How to Create Dynamic Drop Down List in Excel Using 4 Different Methods

 
Click on the video link for quick reference to the use of it. Subscribe to our new channel and keep learning with us!
 

 

If you liked our blogs, share it with your friends on Facebook. And also you can follow us on Twitter and Facebook.
We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Write us at info@exceltip.com

Comments

  1. This is a great tool! Thank you. So, question: based on a list of task categories using dropdown list 1, can the adjacent cell in that row display different associated drop down lists. For example, if a time sheet had 'Event Support' as the task CATEGORY dependent on the list of task CATEGORY drop list, then in the next column, can you have ONLY a list of task 'ACTIVITIES' associated only with event support. so, only display dropdown list for event support, such as logistics, booth setup, lead acquisition, etc. This would be different for ADVERTISING tasks such as copywriting, layout/design, etc. in this way, the dropdown lists would be shorter and dynamically controlled based on these task categories and task activities.

Leave a Reply to Paul Schiller Cancel reply

Your email address will not be published. Required fields are marked *

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.