Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or
0
0
0
s2smodern

How to create dependent cascading drop down lists in Excel?

Supposing you have three lists as below screenshot shown, and you need to create a cascading drop down list to limit the choices in the second drop down list based on the value in the parent one. For example, parent drop down lists locate in column E with values of Coffee, Tea and Wine. When selecting Coffee from the parent drop down list, only the coffee items display in the second drop down list. This article will show you a method to achieve this operation.

Create dependent cascading drop down list with indirect function


Create dependent cascading drop down list with indirect function


Please do as follows to create a dependent cascading drop down list in Excel.

1. Select the cells which the values you will display in the parent drop down lists, and then name the range in the Name Box (here I name the three column headers as drinkstuff in my case). See screenshot:

2. Next, give names to the corresponding Coffee, Tea and Wine items as Coffee, Tea and Wine separately in the Name Box as below screenshot:

3. Select the cells you want to create parent drop down lists, and then click Data > Data Validation > Data Validation. See screenshot:

4. In the Data Validation dialog box and under the Settings tab, select List from the Allow drop down list, enter =drinksuff into the Source text box, and then click the OK button.

Note: drinkstuff is the range name specified in step 1. Please change it based on your needs.

5. Select the cells you will create the second drop down lists, and then click Data > Data Validation > Data Validation to open the Data Validation dialog box.

6. In the Data Validation drop down list, select List from the Allow drop down list, enter =INDIRECT(E2) into the Source text box, and then click the OK button.

Note: In this case, E2 is the first cell contains the parent drop down lists.

Now the cascading drop down list is created. When selecting Coffee from the parent drop down list, only the coffee items display in the second drop down list. When Tea is selected in the parent drop down list, you can only select the tea items from the second drop down list. See screenshot:


Related articles:


Recommended Productivity Tools

Office Tab

gold star1 Bring handy tabs to Excel and other Office software, just like Chrome, Firefox and new Internet Explorer.

Kutools for Excel

gold star1 Amazing! Increase your productivity in 5 minutes. Don't need any special skills, save two hours every day!

gold star1 300 New Features for Excel, Make Excel Much Easy and Powerful:

  • Merge Cell/Rows/Columns without Losing Data.
  • Combine and Consolidate Multiple Sheets and Workbooks.
  • Compare Ranges, Copy Multiple Ranges, Convert Text to Date, Unit and Currency Conversion.
  • Count by Colors, Paging Subtotals, Advanced Sort and Super Filter,
  • More Select/Insert/Delete/Text/Format/Link/Comment/Workbooks/Worksheets Tools...

Screen shot of Kutools for Excel

btn read more      btn download     btn purchase

Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.

Be the first to comment.