Creating a drop-down list in Excel: how it works

With a drop-down list you pick a cell's value from a list instead of typing it. That prevents typos. You create it under Data > Data Validation > List.

Try the first exercises for freeIn the lesson Drop-down lists with data validation

What is a drop-down list?

A drop-down list is an arrow next to a cell. If you click it, you choose the value from a fixed list, such as Leeds, Bristol or York, instead of typing it yourself. If someone types something else anyway, Excel shows an error message.

That way, every value is written in exactly the same way everywhere. That matters for your formulas: COUNTIF and SUMIF count Leeds and Leeds with a space after it as two different values, and VLOOKUP doesn't find a value with a typo.

When do you use a drop-down list?

A column with a limited number of values, such as regions, departments or statuses.
A form that other people fill in, such as an order form or a request.
Yes or no in a column, so that it doesn't also contain y, Y and yep.
A cell where you choose a product code that VLOOKUP then looks up.

Creating a drop-down list, step by step

This is how to go about it:

Select the cells that get a drop-down list.
Choose Data > Data Validation.
Under Allow, choose List.
Under Source, type the choices separated by commas, such as Leeds,Bristol,York. Or click in the box and select the cells with your choices.
Click OK. An arrow appears next to the cell, and you choose a value with a click.

Your own error message

On the Error Alert tab of the Data Validation window, type what Excel should show when someone types something else anyway, such as: Choose a region from the list.

Where it often goes wrong

A drop-down list only blocks typed entries. If someone pastes a value into the cell, the check disappears.
If a choice is added to your list of cells, make sure the range is big enough, or turn the list into an Excel table.

Want to try it yourself?
Practise this in the lesson Drop-down lists with data validation. The first exercises are free.

Try the first exercises for free

Frequently asked questions

How do I remove a drop-down list?

Select the cells, choose Data > Data Validation and click Clear All.

Can I keep the choices on another sheet?

Yes. Put the choices on a separate sheet, such as Lists, and refer to them under Source: =Lists!$A$2:$A$10.