Summary

Drop-down lists restrict cell input to a predefined set of options, reducing data entry errors and ensuring consistency. This article explains how to create drop-down lists using Data Validation — from a simple typed list to a dynamic range powered by a Table.

Before You Start

• Microsoft Excel (desktop or web).

Instructions

1. Create a Drop-Down from a Typed List

Manually define the list of options.

1. Select the cell or range where you want the drop-down.
2. Go to the Data tab.
3. Click Data Validation.
4. Under Allow, select List.
5. In the Source field, type your options separated by commas:

   Yes, No, Pending

6. Click OK.
7. A down-arrow appears on the cell — click it to see the options.

2. Create a Drop-Down from a Cell Range

Use values already typed in your spreadsheet as the source.

1. Type your list of options in a column (e.g., D1:D5).
2. Select the cell where you want the drop-down.
3. Go to Data > Data Validation.
4. Under Allow, select List.
5. In the Source field, click the range selector icon and select your list range (e.g., $D$1:$D$5).
6. Click OK.

3. Create a Dynamic Drop-Down Using a Table

Automatically expand the list when new items are added.

1. Type your list of options in a column.
2. Select the list and press Ctrl + T to convert it to a Table. Click OK.
3. Note the Table name (e.g., Table1) in the Table Design tab.
4. Select the cell where you want the drop-down.
5. Go to Data > Data Validation.
6. Under Allow, select List.
7. In the Source field, enter:

   =INDIRECT("Table1[Column1]")

   Replace Table1 and Column1 with your actual table and column names.
8. Click OK.
9. Whenever you add a new row to the Table, the drop-down updates automatically.

4. Add an Input Message and Error Alert

Guide users and prevent invalid entries.

1. Select the cell with the drop-down.
2. Go to Data > Data Validation.
3. Click the Input Message tab:
   • Enter a Title (e.g., "Select Status").
   • Enter an Input message (e.g., "Choose from the list").
4. Click the Error Alert tab:
   • Set Style to Stop (blocks invalid entries), Warning, or Information.
   • Enter a Title and Error message.
5. Click OK.

Note: A drop-down controls what goes into one cell. It does nothing to stop someone typing over the formulas, headings, or lookup list elsewhere on the sheet — and pasting into a validated cell bypasses the rule entirely. If you are building a sheet for other people to fill in, unlock only the input cells and protect the rest: How to Protect a Sheet or Workbook in Excel covers both halves.

5. Remove a Drop-Down List

Delete the Data Validation rule.

1. Select the cell or range with the drop-down.
2. Go to Data > Data Validation.
3. Click Clear All.
4. Click OK.

Troubleshooting

TIP

To apply the same drop-down to an entire column, select the full column (click the column header) before setting up Data Validation. This ensures new rows inherit the validation.

Symptom / Error	Potential Cause	Solution
Drop-down arrow not visible	Cell not selected, or validation was removed	Click the cell. If no arrow appears, re-apply Data Validation.
User can still type invalid values	Error Alert style set to Warning or Information	Change the Error Alert style to Stop in Data Validation settings.
New items not appearing in drop-down	Source range is fixed (not a Table)	Convert the source list to a Table or manually expand the source range.

Related Articles

• How to Protect a Sheet or Workbook in Excel
• How to Use Data Validation and Input Tips in Excel
• How to Use Conditional Formatting in Excel
• How to Fix Common Excel Formula Errors
• Understanding Excel Features — A Quick-Reference Guide to What Excel Can Do

Did this solve your problem?