How to Add a Drop-Down List in Excel (Data Validation)

Typing the same five department names into a sheet over and over is how typos creep in. One wrong spelling and your totals quietly miss a row. A drop-down list fixes that: people pick from a list you control, and Excel only accepts what is on it.

This is called data validation, and it takes about two minutes to set up. Here’s how to do it.

Step 1: Put your list somewhere visible

Type your options into a column, one per cell. It can sit on the same sheet or on a separate sheet called something like Lists. Keeping it on its own sheet stops people changing it by accident.

Step 2: Select the cells you want the drop-down in

Click the first cell, or select the whole column you’ll be filling in, for example C2:C200.

Step 3: Open Data Validation

1. Go to the Data tab.
2. Click Data Validation (in some versions, Data Validation sits under the Data Tools group).
3. On the Settings tab, set Allow to List.
4. In Source, either type the options separated by commas (for example Sales,Finance,Operations), or select your list range, such as =Lists!$A$2:$A$6.
5. Tick In-cell dropdown and click OK.

A small arrow now appears when you click a cell in that range.

Step 4: Stop bad entries

On the Error Alert tab, leave Show error alert after invalid data is entered ticked, and write a short message such as Please pick a department from the list. Now Excel rejects anything typed that isn’t on the list.

Tips that save time

• Use a named range. Name your list Departments (Formulas tab, Define Name), then use =Departments as the source. Your list can grow without you editing the validation.
• Make the list grow automatically. Turn the list into a table (Ctrl+T) and point the name at the table column. New items show up in the drop-down straight away.
• Dependent drop-downs. Use INDIRECT with named ranges to show a second list that depends on the first, for example a region and then its branches.

When a drop-down isn’t enough

Data validation keeps the entries clean, but it won’t stop someone pasting over the cell. If the sheet is shared with a lot of people, combine drop-downs with sheet protection, so only the input cells can be changed.

If you’d like a ready-made starting point, browse my Excel templates, each one set up and ready to use.

Questions? Get in touch via the contact page.