No Ticket Needed

Appearance

8 min fix 3 min read Recently reviewedBeginner

By , IT support technician

Quick Answer

Select a cell, go to the Data tab, click Data Validation, choose List, and type your options separated by commas.

How to Create a Drop-Down List in Excel ​

Applies to: Microsoft 365 (Excel desktop, Excel for the Web)
Article Type: How-To
Last Updated: 2026-03-09

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.

Prerequisites ​

  • 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.

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 / ErrorPotential CauseSolution
Drop-down arrow not visibleCell not selected, or validation was removedClick the cell. If no arrow appears, re-apply Data Validation.
User can still type invalid valuesError Alert style set to Warning or InformationChange the Error Alert style to Stop in Data Validation settings.
New items not appearing in drop-downSource range is fixed (not a Table)Convert the source list to a Table or manually expand the source range.