How to Create a Drop-Down List in Excel

To create a drop-down list in Excel, select the cell, open the Data tab, click Data Validation, choose Allow: List, and either type your items in the Source box separated by commas or select a range that contains them. Click OK, and a small arrow lets users pick from the list instead of typing.

Drop-down lists keep data consistent: instead of one person typing “NSW”, another “N.S.W.” and a third “New South Wales”, everyone picks the same option. This guide shows you how to build one step by step, how to base it on a range so it’s easy to update, and how to edit or remove it.

What is a drop-down list in Excel?

A drop-down list restricts what can be entered in a cell to a set of options you define. It’s built with Excel’s Data Validation feature. The result is a cell with a small arrow; click it and choose from your list. It’s the simplest way to keep a spreadsheet clean, prevent typos, and make data easier to filter and analyse later.

How do you create a drop-down list in Excel?

The quickest method is to type the options directly:

1. Select the cell (or cells) where you want the drop-down.
2. Go to the Data tab on the ribbon.
3. In the Data Tools group, click Data Validation.
4. On the Settings tab, open the Allow box and choose List.
5. In the Source box, type your options separated by commas, for example NSW,VIC,QLD,WA,SA,TAS.
6. Make sure In-cell dropdown is ticked, then click OK.

How do you make a drop-down list from a range of cells?

Typing options works for short, fixed lists. For anything you’ll update, base the list on a range instead:

1. Put your options in a column (ideally on a separate sheet, e.g. a “Lists” tab), one per cell.
2. Select the cell for the drop-down, then Data > Data Validation > Allow: List.
3. Click in the Source box, then select the range of options (for example =Lists!$A$2:$A$10).
4. Click OK.

Tip: for a list that grows automatically, format your options as an Excel Table (Insert > Table) and reference it. New items you add to the table then appear in the drop-down without editing the validation.

How do you edit or add items to a drop-down list?

• If the list was typed in the Source box: select the cell, go to Data > Data Validation, and edit the items in the Source box.
• If the list is based on a range: just edit the cells in that range. Add a new option to the list and it appears in the drop-down (immediately if it’s inside the referenced range or an Excel Table).

To apply the same drop-down to more cells, copy the cell with the drop-down and paste it into the others (or select the whole range before you create the validation).

How do you remove a drop-down list?

Select the cell (or cells), go to Data > Data Validation, and on the Settings tab click Clear All, then OK. The drop-down is removed but any value already in the cell stays.

How do you add an input message or error alert?

Data Validation can also guide users:

• Input Message tab: show a small tooltip when the cell is selected (e.g. “Choose a state from the list”).
• Error Alert tab: control what happens if someone types something not on the list, Stop (blocks it), Warning, or Information.

Tips for better drop-down lists

• Base lists on a Table so they expand as you add options.
• Keep option lists on a separate sheet to keep your main sheet tidy.
• For dependent (cascading) drop-downs, where the second list depends on the first, use named ranges with the INDIRECT function. It’s an intermediate technique worth learning once you’re comfortable with the basics.

Practice on your own data

Drop-down lists are one of those small skills that make a spreadsheet feel professional, and they click into place fast with a bit of guidance.

Data validation and drop-down lists are covered in our instructor-led Excel training courses, in-class in Sydney and Melbourne or Online Live across Australia. For the bigger picture, see our Complete Guide to Microsoft Excel.

Frequently asked questions