How to create a computable drop-down list

Excel 365
Dynamic, dependable, conditional, or calculated drop-down list in Excel implies that the contents of the drop-down list for the current cell should depend on values in the other cell and change dynamically when the value of the controlling cell changes.

For example, you need to select gifts for the employee's children from the lists for different ages and have a spreadsheet with:

  • Name and birth date of the child
  • Age of the child calculated the following formula (see How to calculate age in Excel) for the 1st of August as the current date:

    = DATEDIF (<start date>, TODAY (), "y")

    where TODAY () returns the current date, "y" tells DATEDIF to count full years between <start date> and TODAY ():

Number of complete years in the period in Excel 2016
  • The next column should contain drop-down lists that show different gift options depending on the calculated age, for example:
Example of computed drop-down list in Excel 2016

To create the dynamic or computable drop-down list, do the following:

   1.   For every set of options for the dependent drop-down list, create a named range (see Creating and using named ranges), for example:

  • First – for children from 1 to 5 years
  • Second – for children from 5 to 9 years
  • Third – for children under 9 years:
Example of named ranges in Excel 2016

   2.   Add a drop-down list (see Creating a drop-down list in a cell):

   2.1.   On the Data tab, in the Data Tools group, click Data Validation:

Data Validation button in Excel 2016

   2.2.   In the Data Validation dialog box, on the Settings tab:

Data Validation in Excel 2016
  • In the Allow drop-down list, select List.
  • In the Source box, specify the range, using the nested IF formula:

    = IF ($C2<1, 0, IF ($C2<6, first, IF ($C2<10, second, IF ($C2<17, third,))))

    or the shorter formula using IFS added in Excel 2016:

    = IFS ($C2<1, 0, $C2<6, first, $C2<10, $C2<17, third).

  • Make sure to check the In-Cell Dropdown option.

Now, Excel will show different lists for different ages:

Example of a computable drop-list in Excel 2016

Note: If you need an automatically extended drop-down list in Excel that automatically adds new items to the list of choices, check the tip How to create an automatically extended or dynamic drop-down list.

See also this tip in French: Comment créer une liste déroulante calculable.

If you have any questions or suggestions, please feel free to ask OfficeToolTips team.

We use cookies to personalise content and ads, to provide social media features and to analyse our traffic. We also share information about your use of our site with our social media, advertising and analytics partners who may combine it with other information you’ve provided to them or they’ve collected from your use of their services.

Learn more