See all How-To Articles

Create / Add a Drop-Down List in Excel & Google Sheets

This tutorial will demonstrate how to create a drop-down list in Excel and Google Sheets.

 

DDList intro

 

Restricting user input by means of a drop-down list is very useful when the user should select from a list of preset values. You can do this with data validation.

Data Validation to Add a Drop-Down List

  1. First, create a list of values in the workbook that contains the drop-down list items. This list can be in the same worksheet as the drop-down list, or “hidden” in another worksheet.

 

DDList valueList

 

  1. Then, highlight the range of cells where you want the drop-down list to appear.

 

DDList Highlight Range

 

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

 

EmailDataValid Ribbon

 

  1. In the Settings tab, select List from the Allow drop down list, and ensure that Ignore blank and In-cell dropdown are checked. Select the Source for the dropdown list by highlighting the values previously typed into the worksheet.

 

DDList Rule

 

  1. To set up a message to inform the users of the worksheet that they need to select from a drop-down list, 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.

 

DDList Message

 

  1. 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. Then type in a Title and Error message for the warning.

 

DDList Alert

 

  1. Click OK to add the Data Validation to the selected cells.
    When you click in the first cell that has the Data Validation applied to it, the input message will appear.

 

DDList Message DD

 

Should you 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

 

Create a Drop-Down List in Google Sheets

Creating a drop-down list in Google Sheets using data validation is very similar to creating one in Excel.

  1. First, create the list of values in a separate location on the worksheet.

 

DDList GS valueList

 

  1. Highlight the cells where you want the list to appear and in the Menu, select Data > Data validation.

 

DD List GS menu

 

  1. Set the (1) Criteria by selecting List from a range and selecting the values previously typed for the drop-down list.
    Make sure that (2) Show dropdown list in cell is ticked and then select either (3) Show warning or Reject Input. Tick (4) Show validation help text and type in the help text required. Then click Save to save the data validation rule.

 

DDList GS CriteriaRules

 

The validation help text will show in the sheet when you select a cell which has the data validation rule applied to it.

 

DDListGS Help text

 

Should you type an incorrect value into the cell, a warning message will appear.

 

DDList GS InvalidText

 

Other Applications for Drop-Down Lists

Drop Down 
Data Validationyes
10 Data Validation Rules and Techniques
Drop Downyes
Add a Drop-Down List With Color Formatting
Create a Cascading Drop-Down List
Create a Drop-Down List Filter
Create a Default Value for a Drop-Down List
Make Drop-Down Categories and Subcategories
Drop-Down List With If Statement
Create a Yes / No Drop Down
Update a Drop-Down List
Remove a Drop-Down List
Alphabetize a Drop-Down List
Formula Auditingyes
Get Rid of the Red Triangle in Google Sheets
Formulasyes
Count Cells Not Equal To in Excel & Google Sheets
Risk Score Bucket Using VLOOKUP
INDIRECT Fx Cell Reference from Text
UNIQUE Function Examples
Picturesyes
Insert a Picture into a Cell Automatically
Shortcutsyes
Excel Filter Shortcut
VBAyes
VBA Drop-Down List