Office Settings & Configuration

Create an Excel Drop-Down List Controlled by an IF Formula

Ayan MasoodAyan Masood Nov 18, 2026 868 views

Question details

Create a drop-down with selectable options when a control cell says Yes and display Not applicable when it says No.

Create an Excel Drop-Down List Controlled by an IF Formula
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.
Before you start

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.

Solution 1Recommended

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.

1
Enter the control value

In cell A2, enter Yes or No. Optionally, use Data > Data Validation > List to create a Yes/No drop-down in A2.

2
Add the available choices

Enter the choices for Yes in cells D2:D4, one option per cell.

3
Create a conditional helper list

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.

4
Add the drop-down

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.

5
Display the result

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.

Use Data Validation and an IF Formula for the Result
Clear an old selection: Changing A2 does not automatically clear a previous selection in B2. Clear B2 or choose a new value when the control changes. Blank entries may also appear in the drop-down when the helper cells are empty.
Work with spreadsheets in WPS Office

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. 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. 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. 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. 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.
Build a Yes/No-controlled drop-down using formulas and Data ValidationOpen and save Microsoft Excel XLSX filesUse a familiar spreadsheet interfaceWork with spreadsheets in a free Office suite
microsoft office alternative - wps office

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.