Dropdown lists from validation in Excel - Step-by-Step Guide

Introduction

Dropdown lists let you pick a value from a list in a cell. This helps avoid typing mistakes and makes data entry faster and cleaner.

When you want to enter a product category from a fixed list in a sales sheet

When you need to select a status like Pending, Approved, or Rejected in a task tracker

When filling a form where only certain options are allowed, like Yes or No

When you want to limit choices for a survey question in Excel

When you want to standardize data entry for regions like North, South, East, West

Steps

Step 1: Select

- the cell or range where you want the dropdown list

The selected cells are highlighted and ready for validation

Step 2: Click

- Data tab on the ribbon

Data tools group is visible

Step 3: Click

- Data Validation button in the Data Tools group

Data Validation dialog box opens

Step 4: In the Settings tab, click

- the Allow dropdown menu

A list of validation types appears

Step 5: Select

- List from the Allow dropdown

The Source field becomes active for list input

Step 6: Type

- the Source box

The list of allowed values is set for the dropdown

💡 Type items separated by commas like Yes,No,Maybe or select a range with your list

Step 7: Click

- OK button in the Data Validation dialog

Dropdown arrows appear in the selected cells allowing list selection

Before vs After

Before

Cell A1 is empty and accepts any value typed

After

Cell A1 shows a dropdown arrow and only allows selection from the list Yes, No, Maybe

Settings Reference

Allow

📍 Data Validation dialog > Settings tab

Choose the type of data allowed in the cell

Default: Any Value

Source

📍 Data Validation dialog > Settings tab (visible when Allow is List)

Defines the list of allowed values for the dropdown

Ignore blank

📍 Data Validation dialog > Settings tab

Allows empty cells without validation error

Default: Checked

In-cell dropdown

📍 Data Validation dialog > Settings tab

Shows or hides the dropdown arrow in the cell

Default: Checked

Common Mistakes

Typing list items in Source without commas

Excel does not recognize the list items as separate values without commas

Separate each item with a comma, for example: Yes,No,Maybe

Selecting a range with blank cells in the list source

Blank cells can cause unexpected empty options in the dropdown

Ensure the source range has no blank cells or use a dynamic named range

Not checking In-cell dropdown option

The dropdown arrow will not appear, making it hard to know a list is available

Always keep In-cell dropdown checked to show the arrow

Summary

Dropdown lists from validation let you pick values from a fixed list in a cell.

They help reduce typing errors and speed up data entry.

Remember to separate list items with commas or use a clean cell range as source.