How To Create Options In Excel Drop Down

This tutorial will demonstrate how to create a yes/no drop down in Excel and Google Sheets.

YesNo Intro

Restricting user input by means of a drop down list is very useful when we wish the user to select from a list of preset values. We can achieve this in Excel by means of Data Validation.

Data Validation to Add a Yes/No Drop Down List

1. In the Ribbon, select Data > Data Tools > Data Validation.

EmailDataValid Ribbon

2. In the Settings tab, select List under Allow, and ensure that Ignore blank and In-cell dropdown are checked. Type in Yes, No as the Source for the drop down list.

Note that unchecking Ignore blank would mean an empty cell is considered invalid. Unchecking In-cell dropdown would mean that "Yes" or "No" would have to be typed in (or copy-pasted), rather than selected from a drop down list in a cell.

YesNo ListSource

3. If we wish to set up a message to inform the users of the worksheet that they need to select from a drop down list, we can select the Input Message tab and check the Show input message when cell is selected check box. Type in the Title and Input message required.

YesNo ListMessage

4. We can then select the Error Alert tab and set up a message when the user does not select from the valid drop down list. Make sure that Show error alert after invalid data is entered is checked, and then select the Style in the drop down list. We can then type in a Title and Error message for our warning.

DDList Alert

7. Click OK to add the Data Validation to the selected cells.

When we click in the first cell that has the Data Validation applied to it, the Input Message will appear.

YesNo Message

Should we ignore the values in the drop down list, and type a different value into the cell, an Error Alert message will appear.

DDList Alert Msg

How to Create a Yes/No Drop Down List in Google Sheets

Creating a drop down list in Google Sheets using Data Validation is very similar to how the list is created in Excel.

1. We first highlight the cells where we want the list to appear and in the Menu, select Data > Data validation.

YesNo GS Menu

2. We can then (1) set the Criteria by selecting List of Items and typing in Yes and No separated by a comma.

YesNo GS SetCriteria

4. Make sure that (2) Show dropdown list in cell is ticked and then select either (3) Show warning or Reject Input. Then click Save to save the Data Validation.

The drop down list will now appear in the selected cells.

YesNo GS DropDown

Should we type an incorrect value into the cell, a message will appear rejecting the input.

YesNo GS Reject