Categories
Excel

Cascading (Dependant) Data Validation List

A cascading (dependent) data validation list in Excel is a way to create a drop-down list that changes based on the value selected in another cell. For example, you can have a list of categories in one cell and a list of types (or subcategories) that belong to that category in another cell. This allows you to limit the choices and avoid errors.

Create Validation Lists That Change Based on a Selection from Another List

Prepare the source data for the lists. You need to have a column (or row) with the main categories, and another column (or row) with the types that correspond to each category. Make sure the categories have unique names.

Video Tutorial: Creating a Dependent List in Excel

To create a cascading data validation list, you first need to create a drop-down list for the main categories:

  1. To create a drop-down list, you need to prepare a range of cells with the list items. The range can be either one row or one column. The drop-down list will display the items from the range you specify.
  2. Click on the cell where you want the list to appear.
  3. Go to the Data tab and click on Data Validation in the Data Tools group.
  4. Select List from the Allow option from the Settings tab in the Data Validation dialog box.
  5. In the Source box, specify the range that contains the list. The range can be in a different worksheet.
  6. Click OK.

Creating Dependent List

Now you need to create another drop-down list (subcategories) that changes based on the value selected in the (categories) previous drop-down list:

  1. Create named ranges for each category. You can use the Name Box to define names for each dependent list (subcategories).
  2. The names of the dependent lists should match exactly with the names of the categories.
    For example, suppose you have a list of fruit names in a certain range of cells. You can assign the name Fruits to this range by typing it in the Name Box as this match exactly with one of the name of the category.
  1. Select the cell where you want the second drop-down list to appear.
  2. Go to Data > Data Validation.
  3. Choose List as the Allow option
  4. In the Source box enter this formula: =INDIRECT($A$9), where A9 is the cell with the second drop-down list.
  5. Click OK.

Test your cascading data validation list. Select a value from the first drop-down list, and see how the second drop-down list changes accordingly.

Data Validation