How to create conditional Excel drop down lists

by | Jul 12, 2018 | Excel Tips & Tricks

How to populate dropdown items

It’s often a good idea to use drop down lists or combo boxes to limit user errors make the data more standardized. However, sometimes we want our drop downs to populate under some conditions. Here, you can find out how to create conditional Excel drop down using Data Validation.

Syntax

=IF( condition, range dropdown list items, "")

Steps

  1. Begin by selecting the dropdown range C3:C5
  2. Open the Data Validation window by clicking DATA > Data Validation
  3. Select List in Allow dropdown
  4. Type your formula in Source input =COUNTIF($I$3:$I$7,B2)
  5. Click OK to continue and apply your settings

 How

Excel's Data Validation feature is a great tool to ensure that users enter correct data. It also allows you to create your own lists by using formulas. Selecting List option in Allow drop down shows the Source input that you can enter your list reference or formula.

By using formula support, you can determine the range of cells that you want to use as a list. Alternatively; you can send an empty string, which represents an empty list as well, to make drop down items hidden.

Simple IF function is enough to to make this a conditional Excel drop down. All you need to do is to set a condition and the list range that is populated if the condition is TRUE. The last argument of the IF function is the two double quotes ("") that represents empty string.

=IF(B3,$C$8:$C$11,"")

An important note:

An important point about the formula is absolute/relative states of references. As you know Excel populates and updates the relative references (e.g. B3) as you copy the formula and leave absolute references (e.g. $C$8:$C$11) as they are. This logic is the same with Data Validation as well which can be used to our advantage. Selecting C3:C5 cell first is to tell Excel to copy the formula along the range. We want B3 to change to B4 and B5 to match C4 and C5 while drop down list $C$8:$C$11 remains the same. You can do this by making B3 relative and leaving $C$8:$C$11 absolute.

Also see related articles how to create a dynamic drop down list in Excel, how to create dependent drop downs, and how to create Excel data validation for unique values.