Excel Guides

Contingent Validation Lists in Excel

A contingent validation list is a drop-down list in Excel that is only populated with values based on another cell's value. For example, if you have a list of countries in one column, and a list of cities in another column, you can create a contingent validation list of cities that only shows the cities from the country selected in the first column. This can be done using a simple formula, or by using Excel's built-in feature for creating dependent drop-down lists.

To create a contingent validation list using a formula, you first need to create two named ranges - one for the countries and one for the cities. To do this, select the cells containing the countries and give them a name (e.g. "countries"). Then select the cells containing the cities and give them a name (e.g. "cities").

Next, create a drop-down list in the cell where you want the contingent list to appear. To do this, click on the cell and then click on the Data tab in the ribbon. In the Data Tools section, click on Data Validation and then choose List from the Allow drop-down.

In the Source field, enter the following formula: =IF(A1="",cities,FILTER(cities,countries=A1))

This formula will show all of the cities in the named range "cities" if no country is selected in cell A1 (the cell where the country drop-down is located). If a country is selected in cell A1, then only the cities from that country will be shown.

You can also use Excel's built-in feature for creating dependent drop-down lists to achieve the same result. To do this, select the cells containing the countries and give them a name (e.g. "countries"). Then select the cells containing the cities and give them a name (e.g. "cities").

Next, create a drop-down list in the cell where you want the contingent list to appear. To do this, click on the cell and then click on Data > Data Tools > Data Validation > List.

In the Source field, enter =indirect(A1) . This formula will show all of the values in whatever named range is selected in cell A1.

Move beyond 

Excel

Get started with Causal today.
Build models effortlessly, connect them directly to your data, and share them with interactive dashboards and beautiful visuals.