Create an Excel Drop-Down List Controlled by an IF Formula
Question details
Create a drop-down with selectable options when a control cell says Yes and display Not applicable when it says No.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A worksheet needs different output depending on a Yes or No value in a control cell.
- Observed behavior
- The desired result is a list of choices for Yes and a Not applicable result for No.
Choose a control cell, a selection cell, and a separate result cell. The example below uses A2, B2, and C2, and places the available choices in D2:D4.
Use Data Validation and an IF Formula for the Result
Use this method to offer a drop-down for Yes and automatically display Not applicable in a separate result cell for No.
An IF formula cannot create a Data Validation drop-down by itself. The drop-down and the automatic result need separate cells unless you use a macro.
In cell A2, enter Yes or No. Optionally, use Data > Data Validation > List to create a Yes/No drop-down in A2.
Enter the choices for Yes in cells D2:D4, one option per cell.
In E2, enter =IF($A$2="Yes",D2,IF(AND($A$2="No",ROW()=2),"Not applicable","")) and fill it down through E4. The helper range shows the choices for Yes or Not applicable for No.
Select B2, open Data > Data Validation, choose List under Allow, and enter =$E$2:$E$4 in the Source box. Make sure In-cell dropdown is selected, then click OK.
In C2, enter =IF(A2="No","Not applicable",IF(A2="Yes",B2,"")) and press Enter. For Yes, select an option in B2; for No, C2 displays Not applicable automatically.

Create a Conditional Drop-Down in WPS Spreadsheet
Use a helper range, Data Validation, and an IF formula in WPS Spreadsheet to offer choices for Yes and display Not applicable for No.
- 1. Set up the worksheet: Open the workbook in WPS Spreadsheet. Enter Yes or No in A2 and put the available choices in D2:D4.
- 2. Create the helper range: In E2, enter =IF($A$2="Yes",D2,IF(AND($A$2="No",ROW()=2),"Not applicable","")) and fill the formula down through E4.
- 3. Create the list: Select B2 and open Data > Data Validation. Choose a list type and set its source to =$E$2:$E$4, then confirm.
- 4. Show the result: In C2, enter =IF(A2="No","Not applicable",IF(A2="Yes",B2,"")) and press Enter. When A2 is Yes, choose an option from B2.

Frequently Asked Questions
Can an IF formula create a drop-down arrow in Excel?
No. Use Data > Data Validation to create the drop-down, and use an IF formula to control a helper range or display a result.
Why does my old selection remain after I change Yes to No?
Data Validation changes the available choices but does not automatically clear an existing value. Clear the selection cell manually; the separate result formula can still display Not applicable.
Can the same cell automatically show Not applicable and also contain a drop-down?
A formula in that cell would be replaced when you select a drop-down value. Use a separate result cell, or use a VBA macro to update the selection cell automatically.
Why are blank choices visible in the list?
The helper range contains formulas that return empty strings for unused entries. Those cells can appear as blank choices in the Data Validation list.




