See all How-To Articles

Drop Down List with IF Statement – Excel

This tutorial will demonstrate you how to create a drop-down list with an IF statement in Excel and Google Sheets.

 

drop down list if statement Main

 

Create a Custom Drop-down List with Data Validation in Excel

To create a drop-down list which changes according to what the user selects, you can use an IF statement within the data validation feature.

  1. Click in C2
  2. In the Ribbon, select Data > Data Tools > Data Validation.

 

drop down list if statement 01

 

  1. Select List from the Allow drop-down box, and then type the following formula:

 

drop down list if statement 02

 

The formula uses two range namesShowFruit and ShowMeat. Depending on the option chosen, the corresponding list will show up in the drop-down list.

  1. Click OK.
  2. Click on the drop-down list to see the list of available fruit.

 

drop down list if statement 03

 

  1. Click on “Show Meat,” and then click on the drop-down list again. The list will have changed to show the meat list while the top value will have change to “Show Fruit.”

 

drop down list if statement 04

 

Create a Custom Drop-down List with a Nested IF Statement in Excel

Expand on the formula above by using nested If statements.

 

drop down list if statement 05

 

  1. Click in C2.
  2. In the Ribbon, select Data > Data Tools > Data Validation.
  3. Select List from the Allow drop-down list, and then select Range F2:I2.

 

drop down list if statement 06

 

  1. Click OK.
  2. Click in C3.
  3. In the Ribbon, , select Data > Data Tools > Data Validation.
  4. Select List from the Allow drop-down list, and then type the following formula.

 

drop down list if statement 07

 

Once again, we are using Range Names in the formula.

  1. Select the food type in the top drop-down list.

 

drop down list if statement 08

 

  1. The list in the second drop-down list will change accordingly.

 

drop down list if statement 09