How to Add a Drop Down List in Excel: Step-by-Step Guide

To add a drop-down list in Excel, select the cells where you want it, go to Data > Data Validation, set Allow to List, and enter your choices in the Source box, either typed with commas (Yes,No,Maybe) or as a reference to a range of cells. Click OK and each selected cell gets an arrow that lets people pick from your list instead of typing.

That’s the whole core feature. The rest of this guide covers which source to choose, how to make the list maintain itself, how to control what happens when someone types something else, and how to edit or remove the list later. Steps are based on Microsoft’s own documentation for Excel for Microsoft 365, Excel 2024, 2021, 2019 and 2016, as of October 2026. Menu names can shift between versions, so expect minor differences.

Step-by-step: create a drop-down list

Microsoft’s guide recommends keeping your valid entries on a sheet in a single column or row with no blank cells. That makes the list easy to see and update.

1. Type your options in a column, for example in A2:A4. Many people put them on a separate sheet (say, “Lists”) to keep them out of the way.
2. Select the cells that should get the drop-down. You can select a single cell, a whole column range, or several cells at once.
3. Go to the Data tab and click Data Validation.
4. On the Settings tab, in the Allow box, choose List.
5. Click in the Source box and select your list range (for example =$A$2:$A$4). Leave out the header row so the header doesn’t show up as a choice.
6. Make sure In-cell dropdown is ticked. Without it, Microsoft notes you won’t see the arrow next to the cell.
7. Decide on Ignore blank (more on this below), then click OK.

Click one of the cells and you’ll see the arrow. Pick a value and it fills the cell.

Choosing a source for your list

The Source box accepts a few different kinds of input. Microsoft gives examples like Yes,No,Maybe, =$A$2:$A$4 and =PriceRange.

Typed items

Fine for something like Yes/No or Open/Closed. Separate items with commas.

A range or named range

Pointing at cells is the most common setup. A named range (created in Formulas > Name Manager or the Name Box) makes the formula more readable and easier to reuse.

An Excel Table (the low-maintenance option)

If you convert your source list to a table (select it and use Insert > Table), the drop-down can keep up with changes. According to Microsoft’s Add or remove items from a drop-down list article, when the source is a table, Excel automatically updates any associated drop-downs as you add or delete items. For a list you’ll be maintaining for months, this is the setup that saves the most hassle.

Add an input message and an error alert

The Data Validation dialog has two more tabs that are worth a minute of your time.

Input Message

On the Input Message tab you can enter a title and short text that appears when someone selects the cell, for example “Pick the project status.” It’s a gentle hint and doesn’t block anything.

Error Alert

The Error Alert tab controls what happens when someone types a value that isn’t in the list. There are three styles:

• Stop blocks the entry. Excel University explains that with Stop, a value that doesn’t meet the rule is rejected and not stored in the cell. It is also the default style.
• Warning shows an alert but still gives the person a way to keep the value, so it’s a softer check than Stop.
• Information shows an informational message and lets the entry through if the person clicks OK.

Use Stop when the data feeds reports or formulas that break on unexpected values. Use Warning or Information when the list is a suggestion and occasional exceptions are fine.

What “Ignore blank” actually does

This checkbox confuses a lot of people. Contextures points out that it doesn’t remove blanks from the list. It means “if this cell has no entered value, don’t apply the validation,” so an empty cell is allowed. Microsoft’s guidance is to leave it ticked only if empty cells are acceptable.

If your drop-down shows an empty option, the cause is usually blank cells inside the source range. Remove them, or use a table so the range matches your data exactly.

Edit an existing drop-down list

How you change the options depends on the source you used. Microsoft’s add or remove items article covers each case:

1. Table source: Type a new item at the bottom of the table to add it. To remove one, select it and press Delete, or right-click the cell, choose Delete, and click OK to shift the cells up. The drop-downs update on their own.
2. Plain range: Add or delete the items, then select a drop-down cell, open Data > Data Validation, and adjust the Source range so it covers the new list.
3. Named range: Edit the items, then go to Formulas > Name Manager, select the name, and update the range it refers to.
4. Typed items: Open Data > Data Validation on the drop-down cells and edit the comma-separated text in Source.

Editing the source doesn’t change values people already chose. If you rename an option, existing cells keep the old text until someone picks again.

Remove a drop-down list

Microsoft’s Remove a drop-down list article gives these steps:

1. Select the cell or cells with the drop-down. Hold Ctrl and click to pick several separate cells.
2. Go to Data > Data Validation.
3. On the Settings tab, click Clear All.
4. Click OK.

The arrow and the rule are removed; values already in the cells stay. If you don’t know which cells have drop-downs, press Ctrl+G, click Special, choose Data Validation, and Excel will select all validated cells so you can clear them in one go.

Searching and AutoComplete in drop-downs

Long lists used to mean a lot of scrolling. Microsoft has since added AutoComplete for data validation drop-downs: as you type in the cell, the list filters to matching items, and the match can be at the start, middle or end of an item. The Microsoft 365 Insider blog says it came to Excel for Windows first and was then rolled out to Insiders on Mac, iOS and Android, and that blank items are left out of the filtered list.

Availability depends on your Excel version and update channel, and Microsoft’s announcement for Mac and mobile did not give a general-availability date. If typing in a drop-down cell doesn’t filter the list, check for Office updates before assuming something’s wrong with your setup.

Common problems and quick fixes

• No arrow appears: Tick In-cell dropdown in the Data Validation settings.
• Blank option in the list: Remove empty cells from the source range, or convert the source to a table.
• New items don’t show up: Your Source range is fixed. Extend it, or switch to a table.
• People still type wrong values: Check the Error Alert tab. If the style is Warning or Information, invalid entries can get through; switch to Stop.
• The header shows as an option: Start the Source range one row below the header.

Bottom line

For a quick Yes/No field, typed items are fine. For anything your team will maintain, put the options in an Excel Table, point the drop-down at it, and set the Error Alert to Stop. That combination keeps the list current without anyone reopening the Data Validation dialog, and it stops stray spellings from creeping into your data.