Dependable Drop-Down Function in Excel
Thursday, May 21
commerce curve online accounting course

What is Dependable Drop-Down Function in Excel?

Dependable drop-down function is an advanced function of data validation that allows us to create simple drop-down lists. With the help of dependable drop-down function, we can create drop-down lists for more than one cell, in such a way that items in dependent list change as we change the item in the drop-down list. This is useful when working with database with lots of categories & subcategories and makes data entry easier by just picking up the item from parent drop-down list and then dependent drop-down list.

Where to find Dependable Drop-Down Function in Excel?

Go to:

Data>Data tools>Data Validation>Settings

How to use Dependable Drop-Down Function?

Let us take an example to understand Dependable Drop-Down function. Suppose Zee Limited has different categories & subcategories of expenses. It wants to create a drop-down list for its main category of expenses and later on, on the basis of the main category of expenses, a drop-down list of sub category of expenses so that data can be entered easily, efficiently and without any error. We can use drop-down function to arrange data in the way Zee Limited wanted.

Step 1: Select cell where you want the parent drop-down list. Open Data validation and following dialog box will open:

Allow: refers to different sources for creating the drop-down list.

Data: allows different criteria for drop-down list depending upon your selection in Allow field.

Source: allows you to select the cell range to be included in drop-down list.

Step 2: In Allow field select List, in Source field specify the cell range that contains the main expenses. 

Click Ok and parent drop-down list will be created.

Step 3: Create name range for all the main expenses. (Formulas>Name Manager) or using short key ctrl+f3. After creating name range for all the main expenses:

Step 4: Select the cell where you want to create dependable drop-down list. Open Data Validation and repeat the steps as explained above, except now in Source field type =INDIRECT(D2). INDIRECT function of Excel gives name range of main expenses and their sub expenses a valid reference and enable us to use this reference in creating dependable drop-down list. Please note that D2 is the cell location of parent drop-down list. The window will look like:

Click Ok and check the outcome:

Now you have to select the main expense from parent drop-down list and dependable drop-down list will be automatically update.

You may notice that by using this function many data with different categories can be entered very efficiently and with any error.



Learn 10 great Excel techniques that will wow your boss and make your co-workers say, “how did you do that??”
Plus weekly updates to help you learn Excel.

Download the eBook

Contact Us

Please reach out to us with questions and comments using the form.
Commerce Curve.

5 + 11 =